Search

Categories

 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

Send mail to the author(s) E-mail

# Wednesday, 10 December 2014
( SQL | SQLServer )

https://efreversepoco.codeplex.com/

Reverse engineers an existing database and generates EntityFramework Code First POCO classes, DbContext and Configuration mappings. Works for SQL Server and SQL Server Compact 4.0

| | # 
# Monday, 06 October 2014
( SQL | SQLServer )

http://ssmstoolspack.com/

Needs a licence

image
Nice query history window.  Also connection colouring so know which server!

| | # 
# Wednesday, 16 July 2014

image

Going for a minimal install, but with client tools.  Also put on mixed mode authentication.  Rest are defaults.

| | # 
# Wednesday, 29 January 2014

http://blog.sqlauthority.com/2007/08/07/sql-server-2005-list-tables-in-database-without-primary-key/

SELECT SCHEMA_NAME(schema_id) AS SchemaName,name AS TableName
FROM sys.tables
WHERE OBJECTPROPERTY(OBJECT_ID,'TableHasPrimaryKey') = 0
ORDER BY SchemaName, TableName;

| | # 
# Tuesday, 20 August 2013

http://channel9.msdn.com/Events/TechEd/NorthAmerica/2013/EXM14#fbid=LpSfjF2hCoS    Michael Corkery

  • MTA – Microsoft Technology Associate
  • MCSA – Microsoft Certified Solutions Associate.. 3 exams.. doing.  Version specific.  DB Admins
    • 461 – Querying SQL
    • 462 – Admin
    • 463 – Data Warehouse
  • MCSE – Expert..  Recertification every 3 years
    • MCSA
    • 464 – Developing
    • 465 – Designing DB Solutions
  • MCSM – Master.. knowledge based exam and lab

Summary

http://www.microsoft.com/learning/en-us/exam-70-464.aspx

Implementing DB Objects (31%)

  • Table
  • Index

Programming Objects

  • UVF
  • Views
  • Triggers

Designing DB Objects

  • How design index?
  • Which field should I include

Optimize Queries

  • Execution plans

Strategy

Take practice exams from multi vendors

  • Measureup – 49/69USD.  Frequent discounts
  • Transcender

Study Group

http://borntolearn.mslearn.net/certification/database/default.aspx#fbid=UiasUz-5IIB db (he moderates)

http://social.msdn.microsoft.com/Forums/en-us/home?forum=CertGeneral – general cert stuff (he moderates)

Resources

http://technet.microsoft.com/en-us/video/sql-server-2012-virtual-labs.aspx – Virtual labs (IT pro)

http://msdn.microsoft.com/en-us/hh859579.aspx – Virtual labs (Dev)

http://www.microsoftvirtualacademy.com – live webcasts JumpStarts

http://andreikos.wordpress.com/2013/01/20/preparing-for-exam-70-464-developing-microsoft-sql-server-2012-databases/ – Studyking links and others at bottom

 

Create and alter tables

  • UI
  • SQL

Triggers

  • Types of triggers
  • Perf
  • Other ways
  • eg when delete on a trigger, it goes to a temp table
  • change tracking/auditing
    • change tracking is built in

Data Versioning

  • Sequences

@Table and #table

Sample Questions

Not multi choice

Permissions

Schemas

Build list

Encrypt

Locking granularity

isolation levels – read?, snapshot

Indexes

Data Types

Data types

CLR objects

Spacial

Column store and sparse columns

SPARSE

Constraints

XML

Automation scripts

Table value and scalar functions

UDFs

Optimizing and Tuning

| | # 
# Friday, 26 July 2013
# Wednesday, 17 July 2013
( SQL | SQLServer )

http://www.codeproject.com/Articles/543164/QueryplusOptimizationplusandplustheplusSQLplusServ

Very useful in debugging why sometimes a SP was running quickly and sometimes not.  This was an issue of calling a SP, and when calling with a NULL value in a parameter it didn’t perform well subsequently for non null values.  If call with a non null value to begin with, then both null and non null perform well.

DECLARE @dbId INTEGER
SELECT @dbId  = dbid FROM master.dbo.sysdatabases WHERE name = ‘myDatabase’
DBCC FLUSHPROCINDB (@dbId)
| | # 
# Friday, 12 July 2013

Ctrl Shift R – updates the schema to get rid of red underlines

| | # 
# Wednesday, 03 July 2013

http://blog.sqlauthority.com/2009/01/30/sql-server-2008-2005-rebuild-every-index-of-all-tables-of-database-rebuild-index-with-fillfactor/

DECLARE @TableName VARCHAR(255)
DECLARE @sql NVARCHAR(500)
DECLARE @fillfactor INT
SET @fillfactor = 80
DECLARE TableCursor CURSOR FOR
SELECT OBJECT_SCHEMA_NAME([object_id])+'.'+name AS TableName
FROM sys.tables
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'ALTER INDEX ALL ON ' + @TableName +' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) +')'
EXEC (@sql)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
GO

| | # 
# Wednesday, 29 May 2013

http://sqlfool.com/2011/06/index-defrag-script-v4-1/

then

     EXECUTE dbo.dba_indexDefrag_sp
              @executeSQL           = 1
            , @printCommands        = 1
            , @debugMode            = 1
            , @printFragmentation   = 1
            , @forceRescan          = 1
            , @maxDopRestriction    = 1
            , @minPageCount         = 8
            , @maxPageCount         = NULL
            , @minFragmentation     = 1
            , @rebuildThreshold     = 30
            , @defragDelay          = '00:00:05'
            , @defragOrderColumn    = 'page_count'
            , @defragSortOrder      = 'DESC'
            , @excludeMaxPartition  = 1
            , @timeLimit            = NULL
            , @database             = 'northwind';
| | # 
# Tuesday, 14 May 2013

SQL Server diagrams are simple and useful, but when backup and restore they don’t get coppied.

http://www.conceptdevelopment.net/Database/ScriptDiagram2008/

  • Add sproc to the DB you want to export diagram
  • Run sproc
  • Copy and paste generated SQL, and run it on new DB you want to import the diagram to
| | # 
# Wednesday, 08 May 2013
( Index | SQL | SQLServer )

Part of tuning I want to update the indexes of a production DB to one with better indexes that has been worked on in dev.

Drop all Non Clustered indexes on the database we want to update

select 'DROP index ' + i.name + ' on ' + so.name
from sys.indexes i
inner join sys.objects so ON i.object_id = so.object_id
where i.type_desc = 'nonclustered'
AND so.type = 'u'

However I can’t do this as:

An explicit DROP INDEX is not allowed on index 'xxx'.  It is being used for UNIQUE KEY constraint enforcement.

looks like I’ll need to drop the uniqueness eg

alter table tbl_Payroll_SubType drop constraint IX_tbl_Payroll_SubType
Can see UC’s here: (against the actual db – not master)

SELECT *
FROM sys.objects
WHERE type = 'UQ' ;

What Are Clustered Indexes?

http://stackoverflow.com/questions/1251636/what-do-clustered-and-non-clustered-index-actually-mean

..Telling the database to store close values actually close to one another on the disk.

image

If you wish to quickly retrieve all orders of one particular customer, you may wish to create a clustered index on the "CustomerID" column of the Order table. This way the records with the same CustomerID will be physically stored close to each other on disk (clustered) which speeds up their retrieval.

P.S. The index on CustomerID will obviously be not unique, so you either need to add a second field to "uniquify" the index or let the database handle that for you but that's another story.

Regarding multiple indexes. You can have only one clustered index per table because this defines how the data is physically arranged. If you wish an analogy, imagine a big room with many tables in it. You can either put these tables to form several rows or pull them all together to form a big conference table, but not both ways at the same time. A table can have other indexes, they will then point to the entries in the clustered index which in its turn will finally say where to find the actual data.

Non Clustered Index

With a non clustered index there is a second list that has pointers to the physical rows. You can have many non clustered indexes, although each new index will increase the time it takes to write new records.

It is generally faster to read from a clustered index if you want to get back all the columns. You do not have to go first to the index and then to the table.

Unique Non Clustered Index and Key

When want to enforce uniqueness eg:

CostCentreID

CostCentreCode ** on this column must be unique

we put on a nonclustered index which is unique called: UC_CostCentres_CostCentreCode

this appears in Keys too.

| | # 
# Tuesday, 07 May 2013
( Index | Performance | SQL | SQLServer )

http://www.brentozar.com/blitz/

Good at analysing indexes.

| | # 
# Monday, 14 January 2013

select @@version

| | # 
# Friday, 05 October 2012
( SQLServer | VS2012 )

image
Installing this to get DB Functionality in VS2012

| | # 
# Monday, 24 September 2012

Handy script to delete if you don’t want to drop the db.

--EXEC sp_MSforeachtable @command1 = "DROP TABLE ?"   --DELETE all tables!

--this script cleans all views, SPS, functions PKs, FKs and tables.

/* Drop all non-system stored procs */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 ORDER BY [name])

WHILE @name is not null
BEGIN
    SELECT @SQL = 'DROP PROCEDURE [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Dropped Procedure: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 AND [name] > @name ORDER BY [name])
END
GO

/* Drop all views */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 ORDER BY [name])

WHILE @name IS NOT NULL
BEGIN
    SELECT @SQL = 'DROP VIEW [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Dropped View: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 AND [name] > @name ORDER BY [name])
END
GO

/* Drop all functions */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0 ORDER BY [name])

WHILE @name IS NOT NULL
BEGIN
    SELECT @SQL = 'DROP FUNCTION [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Dropped Function: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0 AND [name] > @name ORDER BY [name])
END
GO

/* Drop all Foreign Key constraints */
DECLARE @name VARCHAR(128)
DECLARE @constraint VARCHAR(254)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME)

WHILE @name is not null
BEGIN
    SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
    WHILE @constraint IS NOT NULL
    BEGIN
        SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] DROP CONSTRAINT [' + RTRIM(@constraint) +']'
        EXEC (@SQL)
        PRINT 'Dropped FK Constraint: ' + @constraint + ' on ' + @name
        SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND CONSTRAINT_NAME <> @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
    END
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME)
END
GO

/* Drop all Primary Key constraints */
DECLARE @name VARCHAR(128)
DECLARE @constraint VARCHAR(254)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME)

WHILE @name IS NOT NULL
BEGIN
    SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
    WHILE @constraint is not null
    BEGIN
        SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] DROP CONSTRAINT [' + RTRIM(@constraint)+']'
        EXEC (@SQL)
        PRINT 'Dropped PK Constraint: ' + @constraint + ' on ' + @name
        SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND CONSTRAINT_NAME <> @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
    END
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME)
END
GO

/* Drop all tables */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 ORDER BY [name])

WHILE @name IS NOT NULL
BEGIN
    SELECT @SQL = 'DROP TABLE [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Dropped Table: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 AND [name] > @name ORDER BY [name])
END
GO

| | # 
# Thursday, 18 August 2011

SELECT
    [TableName] = so.name,
    [RowCount] = MAX(si.rows)
FROM
    sysobjects so,
    sysindexes si
WHERE
    so.xtype = 'U'
    AND
    si.id = OBJECT_ID(so.name)
GROUP BY
    so.name
ORDER BY
    2 DESC

From http://sqlserver2000.databases.aspfaq.com/how-do-i-get-a-list-of-sql-server-tables-and-their-row-counts.html

This is approximate

| | # 
# Wednesday, 18 May 2011
CREATE FUNCTION Guid_Empty()
RETURNS UniqueIdentifier
AS
BEGIN
RETURN cast(cast(0 as binary) as uniqueidentifier)
END
GO



SELECT MerchantUID, EmailToSendReport1, EmailToSendReport2, EmailToSendReport3, EmailToSendReport4
FROM aspnet_Profile2
WHERE UserType = 1 and
-- where MerchantUID is not a null Guid.. uses function Guid_Empty
ISNULL(MerchantUID, dbo.Guid_EMPTY()) != dbo.Guid_EMPTY()

| | # 
# Tuesday, 18 January 2011

From http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_26469215.html

DECLARE @dt datetime
SET @dt = '2007-01-01'
WHILE @dt <= '2017-01-01' BEGIN
INSERT INTO SomeTable (DateColumn) VALUES (@dt)
   SET @dt = DATEADD(day, 1, @dt)
END

| | # 
# Monday, 17 January 2011
( Azure | SQLServer )

http://code.msdn.microsoft.com/Project/Download/FileDownload.aspx?ProjectName=KB2006191&DownloadId=8227

Here are the scripts put together in 1 file:

Remember to point your web app to the database to populate it ie in Visual Studio, click on the icon at the top in solution explorer to launch the web app.

| | # 
# Thursday, 02 September 2010

http://www.databasejournal.com/features/mssql/article.php/3654176/SQL-Server-2005-Command-Line-Tool-SQLCMD--Part-I.htm

C:\Program Files\Microsoft SQL Server\90\Tools\Binn

sqlcmd –S.\SQLEXPRESS –E    -- E is windows auth

select @@version

seelct @@servername

sqlcmd –Smssql1.openhost.net.nz –Uuser –Psecret    -- carefuly copying this from here.. I had to retype

sqlcmd –Smssql1.openhost.net.nz –Uuser –Psecret –i c:\dbtestbackup.sql –oc:\sqloutput.txt

 

Had a huge 800MB .SQL file.  Notepad++ wouldn’t open after 10mins.

even gvim tried, but opened as junk text

Try SQL Server Import / Export now. This worked really well!

| | # 
# Monday, 09 August 2010

http://thedatafarm.com/blog/tools/asp-net-iis6-network-service-and-sql-server/

On the master database:

exec sp_grantlogin 'NT AUTHORITY\NETWORK SERVICE'

Then go to Management Studio, and add in this user.

| | # 
# Thursday, 27 May 2010
( SQLServer | VMWare )

 

using the Web Plaform Installer. installation of the Studio didn’t work

tried ccleaner which didn’t work.

http://goneale.com/2009/05/24/cant-install-microsoft-sql-server-2008-management-studio-express/

Trick is to get the DB Server and tools together: **No this is just SQL Server 2008, not R2**

https://www.microsoft.com/downloads/details.aspx?familyid=7522A683-4CB2-454E-B908-E805E9BD4E28&displaylang=en

But as I’d compressed my C:\ drive on WMWare Player it came up with an unusual error.

This is good:

image

image

Expand Boot Drive on VMWare Player3

Need VMWare Player 3.

http://kb.vmware.com/selfservice/microsites/search.do?language=en_US&cmd=displayKC&externalId=1004047

Can simply edit the drive information in the player GUI, then goto Windows7 click on C:\ drive in admin tools and management, and then do expand volume.

| | #