stored-procedures

Find locks in SQL Server

dm_tran_locks contains info about all current locks, granted or pending.

select *
from sys.dm_tran_locks

Use stored procedures instead of embedded T-SQL

Use stored procedures whenever a client application needs to send Transact-SQL to SQL Server. Stored procedures offer many benefits:

  • Reduces network traffic and latency, boosting application performance.
  • Stored procedure execution plans can be reused, staying cached in SQL Server's memory, reducing server overhead.
  • Stored procedures help promote code reuse. While this does not directly boost an application's performance, it can boost the productivity of developers by reducing the amount of code required, along with reducing debugging time.
  • Stored procedures can encapsulate logic. You can change stored procedure code without affecting clients (assuming you keep the parameters the same and don’t remove any result sets columns). This saves developer time.
  • Stored procedures provide better security to your data. If you use stored procedures exclusively, you can remove direct SELECT, INSERT, UPDATE, and DELETE rights from the tables and force developers to use stored procedures as the method for data access.

Performance statistics for stored procedures

By looking at the results returned by dm_exec_procedure_stats DMV, we can gather info about how a stored procedure is performing:

SELECT
        DB_NAME(database_id), p.name as SPName, execution_count
        , total_physical_reads, total_physical_reads / execution_count as avg_physical_reads
        , total_logical_reads, total_logical_reads / execution_count as avg_logical_reads
        , total_elapsed_time, total_elapsed_time / execution_count as avg_elapsed_time
        , QP.query_plan
FROM
        sys.procedures P
        JOIN sys.dm_exec_procedure_stats S ON S.object_id = P.object_id
        CROSS APPLY [sys].[dm_exec_query_plan] (S.[plan_handle]) QP
WHERE
        S.database_id = DB_ID()
        AND P.is_ms_shipped = 0

Versioning of stored procedures

SQL Server provides a built-in mechanism for versioning of stored procedures.
You can create more versions for the same stored procedures by adding “;” and version number at the end of stored procedure name. If version number is not specified, SQL Server presumes it to be 1. Let’s have a look on the following sample:

CREATE PROCEDURE myproc;1
AS
PRINT 'You have run version 1 of myproc stored procedure'
GO

CREATE PROCEDURE myproc;2
AS
PRINT 'You have run version 2 of myproc stored procedure'
GO

Running a specific version of the procedure is straightforward:

EXEC myproc;1

or
EXEC myproc;2

One very interesting thing I should remark is you are allowed to use different signatures for each version. Among of drawbacks I would like to point out you can’t drop just a specific version using DROP PROCEDURE statement (you can delete all versions at a time).

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

Subscribe to RSS - stored-procedures