sql-server-2008

Code template for transactions

Code template for correctly handling transactions in SQL Server:

SET XACT_ABORT ON;

BEGIN TRY
        BEGIN TRANSACTION
                -- Your script here

        COMMIT TRANSACTION
END TRY
BEGIN CATCH
        IF XACT_STATE() != 0  
                ROLLBACK TRANSACTION

        -- Re-throw error
        EXEC ThrowError
END CATCH
GO

Sample of ThrowError function:

CREATE PROCEDURE ThrowError
AS
BEGIN
   DECLARE
     @ErrorNumber INT
     , @ErrorMessage NVARCHAR(4000)
     , @ErrorSeverity INT
     , @ErrorState INT
     , @ErrorProcedure NVARCHAR(128)
     , @ErrorLine INT
     
   SELECT @ErrorNumber = ERROR_NUMBER()    
        , @ErrorMessage = ERROR_MESSAGE()
        , @ErrorSeverity = ERROR_SEVERITY()
        , @ErrorState = ERROR_STATE()
        , @ErrorProcedure = ERROR_PROCEDURE()
        , @ErrorLine = ERROR_LINE()

    SELECT @ErrorNumber, @ErrorMessage, @ErrorSeverity, @ErrorState, @ErrorProcedure, @ErrorLine
   
    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState )
END
GO

How to create a copy of a table using Transact-SQL

The easiest way to create a copy of a table is to use a Transact-SQL command. Use SELECT INTO to extract all the rows from an existing table into the new table.The new table must not exist already. 

USE [DestDB]
SELECT * INTO <DestinationTableName> FROM [SourceDB].[dbo].[SourceTableName]

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;

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.

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 - sql-server-2008