sql-server-2012

How to restore a SQL Server database even if it is in use

You can restore a SQL Server database that by executing the following script that kills all active connections:

USE master
ALTER DATABASE <databasename> SET SINGLE_USER WITH ROLLBACK IMMEDIATE

-- execute restore operation here

ALTER DATABASE <databasename> SET MULTI_USER

Best practices for creating a computed column in SQL Server table

A computed column is computed from an expression that can use other columns in the same table.

ALTER TABLE mytable ADD fullname AS firstname + ' ' + lastname

By marking a computed column as PERSISTED, you can create an index it.

ALTER TABLE mytable ADD fullname AS firstname + ' ' + lastname PERSISTED
CREATE INDEX IX_mytable_fullname ON MyTable (fullname)

How to grant sysadmin role to a user

Use sp_addsrvrolemember to add a login as a member of a fixed server role.

-- Create login first
USE [master]
CREATE LOGIN <login_name> WITH PASSWORD = '<YourPassword>', DEFAULT_DATABASE=[master];

-- Or create login from a Windows domain account
CREATE LOGIN [MyDomain\MyUser] FROM WINDOWS;
USE [master]

-- Grant role to newly created login
EXEC master..sp_addsrvrolemember @loginame = N'MyDomain\MyUser', @rolename = N'sysadmin'

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]

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

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

Pages

Subscribe to RSS - sql-server-2012