sql-server-2008

Detecting fragmentation for all indexes in a SQL Server database

The first step in deciding which defragmentation method to use is to analyze the index to determine the degree of fragmentation. By using sys.dm_db_index_physical_stats DMV, you can detect fragmentation in a specific index, all indexes on a table or indexed view, all indexes in a database, or all indexes in all databases. For partitioned indexes, sys.dm_db_index_physical_stats also provides fragmentation information for each partition.

The result set returned by the sys.dm_db_index_physical_stats dynamic management function includes the following columns:
avg_fragmentation_in_percent: the percent of logical fragmentation (out-of-order pages in the index)
fragment_count: the number of fragments (physically consecutive leaf pages) in the index
avg_fragment_size_in_pages: average number of pages in one fragment in an index
avg_record_size_in_bytes: This number simply represents a useful measure for the amount of data stored within the record (index or heap).

SELECT
     OBJECT_NAME(S.OBJECT_ID) as TableName,
     I.name as IndexName,
     S.avg_fragmentation_in_percent
FROM
     sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) as S
     INNER JOIN sys.indexes AS I ON S.OBJECT_ID = I.OBJECT_ID AND S.index_id = I.index_id
WHERE
     S.database_id = DB_ID()
ORDER BY
     TableName, IndexName

After the degree of fragmentation is known, check how you can "resolve" the fragmentation.

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]

Regular Expressions for Efficient SQL Querying

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

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

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