sql-server-2014

Articles about SQL Server 2014, In-Memory OLTP Database Engine, Windows Azure integration, AlwaysOn Availability Groups, business intellligence, data visualization tools, and more.

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

RegEx - Regular expressions for efficient querying in SQL Server

Create function in SQL Server that calls C# method in an assembly via SQL Server CLR.

C# code:

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text.RegularExpressions;

namespace RegEXHelper
{
    public class RegExFunctions
    {
        /// <summary>
        /// Wrapper over .NET RegEx.Matches
        /// </summary>
        /// <param name="Pattern">The regular expression pattern to match.</param>
        /// <param name="MatchString">The string to search for a match.</param>        
        /// <returns></returns>
        [SqlFunction(Name = "RegexFind", IsPrecise = true, IsDeterministic = true,
            FillRowMethodName = "FillMatchRow",
            TableDefinition = "Match_ID INT, FirstIndex INT, [Length] INT, Value NVARCHAR(MAX), Submatch_ID INT, SubmatchName NVARCHAR(255), SubmatchValue NVARCHAR(MAX), Error NVARCHAR(MAX)")]
        public static IEnumerable RegexFind(SqlString Pattern, [SqlFacet (MaxSize=-1)] SqlString MatchString,
            SqlBoolean Global, SqlBoolean Multiline, SqlBoolean IgnoreCase)
        {
            if (Pattern.IsNull)
                throw new ArgumentNullException("Pattern");

            if (MatchString.IsNull)
                throw new ArgumentNullException("MatchString");

            if (Global.IsNull)
                Global = SqlBoolean.True;

            var result = new List<MatchRecord>();

            RegexOptions options = BuildRegexOptions(Multiline, IgnoreCase);
            Regex regEx = new Regex(Pattern.Value, options);
                       
            MatchCollection matches = regEx.Matches(MatchString.Value);
            Int32 numberOfMatches = Global ? matches.Count : (matches.Count>0)?1:0;

            for (Int32 matchIndex = 0; matchIndex < numberOfMatches; matchIndex++)
            {
                Match match = matches[matchIndex];
                var record = new MatchRecord
                                 {
                                     MatchId = matchIndex + 1,
                                     MatchIndex = match.Index + 1,
                                     MatchLength = match.Length,
                                     MatchValue = match.Value
                                 };
                result.Add(record);

                //Ignore first groups as it is equal with first match
                if (match.Groups.Count <= 1)
                    continue;

                for (Int32 groupIndex = 1; groupIndex < match.Groups.Count; groupIndex++)
                {
                    Group group = match.Groups[groupIndex];
                    record = new MatchRecord
                    {
                        MatchId = matchIndex + 1,
                        MatchIndex = match.Index + 1,
                        MatchLength = match.Length,
                        MatchValue = match.Value,
                        GroupId = groupIndex,
                        GroupName = regEx.GetGroupNames()[groupIndex],
                        GroupValue = group.Value                        
                    };
                       
                    result.Add(record);
                }
            }

            return result;
        }

        /// <summary>
        /// Wrapper over .NET RegEx.IsMatch method
        /// </summary>
        /// <param name="Pattern">The regular expression pattern to match.</param>
        /// <param name="MatchString">The string to search for a match.</param>
        [SqlFunction(Name = "RegexIsMatch", IsPrecise = true, IsDeterministic = true)]
        public static Boolean RegexIsMatch(SqlString Pattern, [SqlFacet(MaxSize = -1)] SqlString MatchString,
            SqlBoolean Multiline, SqlBoolean IgnoreCase)
        {
            if (Pattern.IsNull)
                throw new ArgumentNullException("Pattern");

            if (MatchString.IsNull)
                throw new ArgumentNullException("MatchString");
                           
            RegexOptions options = BuildRegexOptions(Multiline, IgnoreCase);
            Regex regEx = new Regex(Pattern.Value, options);
            return regEx.IsMatch(MatchString.Value);
        }

        /// <summary>
        /// Wrapper over .NET RegEx.Replace method
        /// </summary>
        /// <param name="Pattern">The regular expression pattern to match.</param>
        /// <param name="Replacement">The replacement string.</param>
        /// <param name="MatchString">The string to search for a match.</param>
        /// <param name="Count">The maximum number of times the replacement can occur.</param>
        [SqlFunction(Name = "RegexReplace", IsPrecise = true, IsDeterministic = true)]
        [return: SqlFacet(MaxSize = -1)]
        public static String RegexReplace(SqlString Pattern, SqlString Replacement, [SqlFacet(MaxSize = -1)] SqlString MatchString,
            SqlInt32 Count, SqlBoolean Multiline, SqlBoolean IgnoreCase)
        {
            if (Pattern.IsNull)
                throw new ArgumentNullException("Pattern");

            if (MatchString.IsNull)
                throw new ArgumentNullException("MatchString");

            if (Replacement.IsNull)
                Replacement = String.Empty;            

            if (Count.IsNull)
                Count = SqlInt32.MaxValue;

            RegexOptions options = BuildRegexOptions(Multiline, IgnoreCase);
            Regex regEx = new Regex(Pattern.Value, options);
            return regEx.Replace(MatchString.Value, Replacement.Value, Count.Value);
        }

        /// <summary>
        /// Method used by SQL Server to extract column values based on a row. See FillRowMethodName attribute name in RegexSplit method.
        /// </summary>
        public static void FillMatchRow(Object Row, out SqlInt32 MatchId, out SqlInt32 MatchIndex, out SqlInt32 MatchLength, out SqlString MatchValue,
            out Int32? GroupId, out SqlString GroupName, out SqlString GroupValue, out String Error)
        {
            MatchRecord record = (MatchRecord)Row;
            MatchId = record.MatchId;
            MatchIndex = record.MatchIndex;
            MatchLength = record.MatchLength;
            MatchValue = record.MatchValue;
            GroupId = record.GroupId;
            GroupName = record.GroupName;
            GroupValue = record.GroupValue;
            Error = record.Error;
        }
       
        private static RegexOptions BuildRegexOptions(SqlBoolean Multiline, SqlBoolean IgnoreCase)
        {
            if (Multiline.IsNull)
                Multiline = SqlBoolean.True;

            if (IgnoreCase.IsNull)
                IgnoreCase = SqlBoolean.True;

            RegexOptions options = RegexOptions.CultureInvariant;
            options |= (Multiline) ? RegexOptions.Multiline : RegexOptions.Singleline;
            if (IgnoreCase)
                options |= RegexOptions.IgnoreCase;

            return options;
        }
    }
}

T-SQL code for installing assembly, respectively for the creation of stored procedures and functions:

-- Script for installing RegEXHelper assembly
IF EXISTS(SELECT 1 FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'dbo.RegexFind'))
        DROP FUNCTION dbo.RegexFind
GO

IF EXISTS(SELECT 1 FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'dbo.RegexIsMatch'))
        DROP FUNCTION dbo.RegexIsMatch
GO

IF EXISTS(SELECT 1 FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'dbo.RegexReplace'))
        DROP FUNCTION dbo.RegexReplace
GO

IF EXISTS(SELECT 1 FROM sys.assemblies WHERE name = 'RegEXHelper')
        DROP Assembly RegEXHelper
GO

CREATE ASSEMBLY RegExHelper
FROM 'D:\Shared_Temp\RegEXHelper.dll'
WITH PERMISSION_SET = SAFE
GO

-- Create functions and stored procedures in SQL Server
sp_configure 'clr enabled', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO

CREATE FUNCTION dbo.RegexFind
(
        @Pattern NVARCHAR(255)
        , @MatchString NVARCHAR(MAX)
        , @Global BIT = 1
        , @Multiline BIT = 1
        , @IgnoreCase BIT = 1
)
RETURNS TABLE
        (
                Match_ID INT,
                FirstIndex INT,
                [Length] INT,
                Value NVARCHAR(2000),
                Submatch_ID INT,        SubmatchName NVARCHAR(255), SubmatchValue NVARCHAR(2000),
                Error NVARCHAR(MAX)
        ) EXTERNAL NAME RegEXHelper.[RegEXHelper.RegExFunctions].RegexFind
GO

CREATE FUNCTION dbo.RegexIsMatch
(
        @Pattern NVARCHAR(255)
        , @MatchString NVARCHAR(MAX)
        , @Multiline BIT = 1
        , @IgnoreCase BIT = 1
)
RETURNS BIT EXTERNAL NAME RegEXHelper.[RegEXHelper.RegExFunctions].RegexIsMatch
GO

CREATE FUNCTION dbo.RegexReplace
(
        @Pattern NVARCHAR(255)
        , @Replacement NVARCHAR(255)
        , @MatchString NVARCHAR(MAX)
        , @Count INT = NULL    
        , @Multiline BIT = 1
        , @IgnoreCase BIT = 1
)
RETURNS NVARCHAR(MAX) EXTERNAL NAME RegEXHelper.[RegEXHelper.RegExFunctions].RegexReplace
GO

How to get last inserted values

1/ SCOPE_IDENTITY function for IDENTITY column.

2/OUTPUT clause

3/ If you are in a trigger, then inserted table is what you are looking for.

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-2014