T-SQL

UNPIVOT table example

You can use the UNPIVOT relational operator rotate columns into rows values. For example, having the below table:

We'll have the below ouput:

First, let's create a table variable and fill it with some sample rows:

DECLARE @Orders AS TABLE
     ( OrderId INT IDENTITY (1,1), CustomerId INT, YearNo SMALLINT, Jan INT, Feb INT, Mar INT)
INSERT INTO @Orders (CustomerId,YearNo, Jan, Feb, Mar)
VALUES (1, 2013, 3, 33, 333), (1, 2012, 2, 22, 222), (2, 2009, 9, 99, 999)
SELECT * FROM @Orders

Here it is a very simple usage of UNPIVOT:

SELECT CustomerId, YearNo, MonthNo, Value FROM @Orders UNPIVOT (Value FOR MonthNo IN (Jan, Feb, Mar)) AS unpvt;

How to get SQL server version

Option 1

SELECT
        SERVERPROPERTY('productversion'),
        SERVERPROPERTY ('productlevel'),
        SERVERPROPERTY ('edition')

Option 2

EXEC master..xp_msver

Option 3

SELECT @@version

Option 4

-- Option 4
DECLARE @ver NVARCHAR(128) = CAST(serverproperty('ProductVersion') AS NVARCHAR)
SET @ver = SUBSTRING(@ver, 1, CHARINDEX('.', @ver) - 1)

SET @ver =
                CASE
                        WHEN @ver = '7' THEN 'SQL Server 7'
                        WHEN @ver = '8' THEN 'SQL Server 2000'
                        WHEN @ver = '9' THEN 'SQL Server 2005'
                        WHEN @ver = '10' THEN 'SQL Server 2008/2008 R2'
                        WHEN @ver = '11' THEN 'SQL Server 2012'
                        WHEN @ver = '12' THEN 'SQL Server 2014'
                        ELSE 'Unsupported SQL Server Version'
                END
SELECT @ver

Check column exists in table

IF EXISTS( SELECT 1
                    FROM sys.columns
                    WHERE Name = N'YourColumnName'
                         AND object_id = Object_ID('dbo.[YourTableName]'))
BEGIN
         PRINT 'Column exists'        
END

List of stored procedures and functions associated with an assembly

In order to get the list of stored procedures and functions associated with an assembly:

SELECT object_name(M.object_id) as SPName, M.assembly_class, M.assembly_method
FROM
    sys.assembly_modules M
    INNER JOIN sys.assemblies A ON A.assembly_id = M.assembly_id
WHERE A.name = 'YourAssemblyName'

Index usability - tune your index strategy

The view sys.dm_db_index_usage_stats returns statistical counts of the various index operations that have occurred to an index over time.

SELECT
     object_name(i.object_id) as TableName
     , i.name as [Index]
     , user_seeks + user_scans + user_lookups + user_updates as usability
     , user_seeks, user_scans, user_lookups, user_updates
     , i.index_id, i.type_desc, i.is_unique, i.is_primary_key, i.is_unique
FROM
     sys.indexes i    
     INNER JOIN sys.dm_db_index_usage_stats s ON s.index_id = i.index_id AND s.object_id = i.object_id
WHERE 1=1
     AND i.name is not NULL
     AND s.database_id = DB_ID() -- current DB
     --AND object_name(i.object_id) = '<TableName>'
ORDER BY
     usability, [Index], TableName;

List of long running queries in SQL Server

T-SQL script that returns the queries that take the longest time to run:

SELECT TOP 20
        R.session_id, R.status, R.start_time, R.command, Q.text
FROM
        sys.dm_exec_requests R
        CROSS APPLY sys.dm_exec_sql_text(R.sql_handle) Q
--WHERE R.status in ('runnable')
ORDER BY R.start_time

There’s also an interesting script, completely free on http://www.brentozar.com.

How to auto increment a particular column without using IDENTITY?

  • Using sequences if you're using SQL Server 2012
  • Using GUIDs and NEWID() function.
    Non clustered indexes include the clustered index(=PK) as a pointer, that might exclude the GUID option, due to the size of indexes, and reduced performance.
  • Using MAX(yourColumn) within a transaction and setting the right serialization level. However, I'm not a fun of this method.

T-SQL Split function

There are some excellent options available at: http://www.sqlperformance.com/2012/07/t-sql-queries/split-strings, but my favourite solution remains this:

DECLARE
@InputString NVARCHAR(MAX) = 'token1,token2,token3,token4,token5'
, @delimiter varchar(10) = ','

DECLARE @xml AS XML = CAST(('<X>'+REPLACE(@InputString,@delimiter ,'</X><X>')+'</X>') AS XML)
SELECT C.value('.', 'varchar(10)') AS value
FROM @xml.nodes('X') as X(C)

Copy into a separate database a list of tables having auto-increment columns

The following script copies many tables from a source DB into another destination DB, taking into account that some of these tables have auto-increment columns.

DECLARE
          @SourceDB nvarchar(128)= N'SourceDB'
        , @RowIndex AS TINYINT = 1
        , @TableName AS NVARCHAR(128)
        , @NoRows AS INT
        , @NoTables AS TINYINT
        , @Sql AS NVARCHAR(MAX)
        , @Message as NVARCHAR(MAX)
        , @ListOfColumns as NVARCHAR(MAX)
        , @HasIdentityColumn BIT       

USE DestinationDB

       
-- Fill list of tables to be copied
DECLARE @ListOfTables AS Table (RowIndex TINYINT IDENTITY(1,1), TableName NVARCHAR(128))
INSERT INTO @ListOfTables (TableName)
VALUES
          ('YourTable1')
        , ('YourTable2')
        , ('YourTable3')       
        , ('YourTable4')                               
        , ('YourTable5')         

-- Get number of tables to copy
SELECT @NoTables = COUNT(*)
FROM @ListOfTables
       
-- For each table      
WHILE @RowIndex <= @NoTables
BEGIN  
        SELECT @TableName = TableName FROM @ListOfTables WHERE RowIndex = @RowIndex
        SET @Message = CHAR(13) + CAST (@RowIndex as VARCHAR) + '. Process table ' + @TableName
        PRINT @Message
                       
        -- Get actual number of rows of table in destination DB
        SELECT @NoRows = SUM(pa.rows)
        FROM
                sys.tables ta
                INNER JOIN sys.partitions pa ON pa.OBJECT_ID = ta.OBJECT_ID
        WHERE ta.is_ms_shipped = 0 AND pa.index_id IN (1,0)     AND ta.name= @TableName
       
        -- If it is already => proceed with next table                 
        IF @NoRows > 0
        BEGIN
                SET @RowIndex = @RowIndex + 1
                CONTINUE
        END    
       
        -- Get list of columns
        SET @ListOfColumns = NULL      
        SELECT @ListOfColumns = COALESCE(@ListOfColumns + ', ', '') + '[' + name + ']'
        FROM sys.columns
        WHERE object_id = object_id(@TableName)
               
        -- Check if table has identity column  
        SET @HasIdentityColumn = 0
        IF EXISTS ( SELECT COLUMN_NAME
                                FROM INFORMATION_SCHEMA.COLUMNS
                                WHERE
                                        TABLE_NAME = @TableName
                                        AND COLUMNPROPERTY(OBJECT_ID(TABLE_NAME), COLUMN_NAME, 'ISIDENTITY') = 1)
        BEGIN
                SET @HasIdentityColumn = 1
        END
                                               
        IF @HasIdentityColumn = 1
        BEGIN
                -- Copy data by de-activating identity column
                SET @Sql = 'SET IDENTITY_INSERT {0} ON' + CHAR(13) +
                        'INSERT INTO {0} ({1}) SELECT {1} FROM {2}.dbo.{0}' + CHAR(13) +
                        'SET IDENTITY_INSERT {0} OFF'                                  
                SET @Sql = REPLACE(@Sql, '{0}', @TableName)
                SET @Sql = REPLACE(@Sql, '{1}', @ListOfColumns)
                SET @Sql = REPLACE(@Sql, '{2}', @SourceDB)
                EXEC sp_executesql @Sql
        END
        ELSE
        BEGIN
                -- Copy data
                SET @Sql = 'INSERT INTO {0} ({1}) SELECT {1} FROM {2}.dbo.{0}'
                SET @Sql = REPLACE(@Sql, '{0}', @TableName)
                SET @Sql = REPLACE(@Sql, '{1}', @ListOfColumns)
                SET @Sql = REPLACE(@Sql, '{2}', @SourceDB)
                EXEC sp_executesql @Sql
        END
               
        PRINT @Sql     
                       
        -- Process next table
        SET @RowIndex = @RowIndex + 1
END
GO

PRINT 'Script executed successfully!'
GO

T-SQL script for implementing transactional replication in SQL Server

T-SQL script that creates a transactional publication and its associated subscription using the following stored procedures:

  • sp_addlogreader_agent
  • sp_addpublication
  • sp_addarticle

DECLARE
        -- Name of the database where resides the table to be replicated
        @publicationDB AS SYSNAME = 'MyPublicationDB'

        -- Name of the publication
        , @publicationName AS SYSNAME = 'MyPublicationName'

        -- Table that will be replicated (published)
        , @publicationTable AS SYSNAME = 'MyTableToReplicate'

        -- Name of the database where the table will be replicated to
        , @subscriptionDB AS SYSNAME = 'MySubscriptionDB'

        -- Server where subscription database resides on
        , @subscriberServerName AS SYSNAME = @@SERVERNAME

        -- Login
        , @login AS SYSNAME = 'YourUser'               

        -- Password
        , @password AS SYSNAME = 'YourPassword'

        -- Snapshot folder
        , @snapshotFolder AS NVARCHAR(255) = N'D:\MSSQL\REPL'


USE MyPublicationDB

-- Enable transactional or snapshot replication on the publication database.
EXEC sp_replicationdboption
               @dbname=@publicationDB
               , @optname=N'publish'
               , @value = N'true';

-- Execute sp_addlogreader_agent to create the agent job.
EXEC sp_addlogreader_agent
               @publisher_login = @login
               , @publisher_password = @password
               , @publisher_security_mode = 0;

-- Create a new transactional publication with the required properties.
EXEC sp_addpublication
               @publication = @publicationName
               , @status = N'active'
               , @allow_push = N'true'
               , @allow_pull = N'true'
               , @independent_agent = N'true'
               , @snapshot_in_defaultfolder = 'false'
               , @alt_snapshot_folder = @snapshotFolder
               , @immediate_sync = N'true'

-- Create a new snapshot job for the publication, using a default schedule.
EXEC sp_addpublication_snapshot
               @publication = @publicationName
               , @publisher_login = @login
               , @publisher_password = @password
               , @publisher_security_mode = 0

-- Add a article for the YoutTable table.
EXEC sp_addarticle
               @publication = @publicationName
               , @article = @publicationTable
               , @source_owner = 'dbo'
               , @source_object = @publicationTable
               , @schema_option = 0x000000000803509F
               , @type = N'logbased'
               , @destination_owner = N'dbo'
               , @destination_table = @publicationTable
               , @vertical_partition = N'false'

--Add a push subscription to a transactional publication.
EXEC sp_addsubscription
               @publication = @publicationName
               , @subscriber = @subscriberServerName
               , @destination_db = @subscriptionDB
               , @subscription_type = N'push';

--Add an agent job to synchronize the push subscription.
EXEC sp_addpushsubscription_agent
               @publication = @publicationName
               , @subscriber = @subscriberServerName
               , @subscriber_db = @subscriptionDB
               , @subscriber_login = @login
               , @subscriber_password = @password
               , @subscriber_security_mode = 0
GO

For checking the replication is correctly put in place:

SELECT * FROM syspublications WHERE name = @publicationName
SELECT name, * FROM sysarticles
SELECT * FROM syssubscriptions

Pages

Subscribe to RSS - T-SQL