XML SQL Input Parameter With Element Attributes

Github Gist =)

Cool!

SQL Server Pivot

Example (thanks to http://goo.gl/oCM0q):

declare @data as table (Product varchar(10), Name varchar(20), Value varchar(10))

insert into @data values ('Beer', 'ProductCode',    'MSTF')
insert into @data values ('Beer', 'LocationScript', 'Office')
insert into @data values ('Milk', 'ProductCode',    'MSTF')
insert into @data values ('Milk', 'ProductSource',  'c.60')
insert into @data values ('Milk', 'LocationScript', 'Office')
insert into @data values ('Soda', 'ProductCode',    'APPL')
insert into @data values ('Soda', 'ProductSource',  'c.60')
insert into @data values ('Soda', 'LocationScript', 'Industry')
insert into @data values ('Wine', 'ProductSource',  'c.90')
insert into @data values ('Wine', 'Alias',  'Lorem')

select * from @data

SELECT Product, ProductCode, ProductSource, LocationScript, Alias
FROM (
 SELECT Product, Name, Value
 FROM @data
) dt
PIVOT (MAX(Value) FOR Name IN (ProductCode, ProductSource, LocationScript, Alias)) AS pv
ORDER BY Product

Result:

Product    Name                 Value
---------- -------------------- ----------
Beer       ProductCode          MSTF
Beer       LocationScript       Office
Milk       ProductCode          MSTF
Milk       ProductSource        c.60
Milk       LocationScript       Office
Soda       ProductCode          APPL
Soda       ProductSource        c.60
Soda       LocationScript       Industry
Wine       ProductSource        c.90
Wine       Alias                Lorem

(10 row(s) affected)

Product    ProductCode ProductSource LocationScript Alias
---------- ----------- ------------- -------------- ----------
Beer       MSTF        NULL          Office         NULL
Milk       MSTF        c.60          Office         NULL
Soda       APPL        c.60          Industry       NULL
Wine       NULL        c.90          NULL           Lorem

(4 row(s) affected)

Cannot drop the database because it is being used for replication

The database used to be part of a replication but apparently something remained somewhere else.

The following commands helped:

use master
go

exec sp_helpreplicationdb 
exec sp_removedbreplication 'Northwind'

ALTER DATABASE Northwind SET SINGLE_USER WITH ROLLBACK IMMEDIATE 
ALTER DATABASE Northwind SET MULTI_USER

DROP DATABASE Northwind 

sp_who and sp_who2 Blocking processes

From
http://stackoverflow.com/questions/2234691/sql-server-filter-output-of-sp-who2 and
http://sqlserverplanet.com/dba/a-better-sp_who2-using-dmvs-sp_who3

With sp_who and sp_who2 we can determine what process is blocking a table for example. We can use the following script to filter out the sp_who2 output:

DECLARE @Table TABLE(
        SPID INT,
        Status VARCHAR(MAX),
        LOGIN VARCHAR(MAX),
        HostName VARCHAR(MAX),
        BlkBy VARCHAR(MAX),
        DBName VARCHAR(MAX),
        Command VARCHAR(MAX),
        CPUTime INT,
        DiskIO INT,
        LastBatch VARCHAR(MAX),
        ProgramName VARCHAR(MAX),
        SPID_1 INT,
        REQUESTID INT
)

INSERT INTO @Table EXEC sp_who2

SELECT  *
FROM    @Table
WHERE ....

Another way is to use a custom sp_who3 sp for this:

CREATE PROCEDURE [dbo].[sp_who3] 

AS
BEGIN

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT
    SPID                = er.session_id
    ,BlkBy              = er.blocking_session_id      
    ,ElapsedMS          = er.total_elapsed_time
    ,CPU                = er.cpu_time
    ,IOReads            = er.logical_reads + er.reads
    ,IOWrites           = er.writes     
    ,Executions         = ec.execution_count  
    ,CommandType        = er.command         
    ,ObjectName         = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid)  
    ,SQLStatement       =
        SUBSTRING
        (
            qt.text,
            er.statement_start_offset/2,
            (CASE WHEN er.statement_end_offset = -1
                THEN LEN(CONVERT(nvarchar(MAX), qt.text)) * 2
                ELSE er.statement_end_offset
                END - er.statement_start_offset)/2
        )        
    ,STATUS             = ses.STATUS
    ,[Login]            = ses.login_name
    ,Host               = ses.host_name
    ,DBName             = DB_Name(er.database_id)
    ,LastWaitType       = er.last_wait_type
    ,StartTime          = er.start_time
    ,Protocol           = con.net_transport
    ,transaction_isolation =
        CASE ses.transaction_isolation_level
            WHEN 0 THEN 'Unspecified'
            WHEN 1 THEN 'Read Uncommitted'
            WHEN 2 THEN 'Read Committed'
            WHEN 3 THEN 'Repeatable'
            WHEN 4 THEN 'Serializable'
            WHEN 5 THEN 'Snapshot'
        END
    ,ConnectionWrites   = con.num_writes
    ,ConnectionReads    = con.num_reads
    ,ClientAddress      = con.client_net_address
    ,Authentication     = con.auth_scheme
FROM sys.dm_exec_requests er
LEFT JOIN sys.dm_exec_sessions ses
ON ses.session_id = er.session_id
LEFT JOIN sys.dm_exec_connections con
ON con.session_id = ses.session_id
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt
OUTER APPLY 
(
    SELECT execution_count = MAX(cp.usecounts)
    FROM sys.dm_exec_cached_plans cp
    WHERE cp.plan_handle = er.plan_handle
) ec
ORDER BY
    er.blocking_session_id DESC,
    er.logical_reads + er.reads DESC,
    er.session_id
 
END

SQL Assembly Error

Exception:

An error occurred in the Microsoft .NET Framework while trying to load assembly id 65536. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE. Run the query again, or check documentation to see how to solve the assembly trust issues

Possible solution:

sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

ALTER DATABASE database_name SET TRUSTWORTHY ON 

USE database_name
GO

EXEC sp_changedbowner 'sa'

Create CLR Assembly SQL Function

Create a cs code file with the desired functionality. Example:

using System;
using System.Data;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;

public class TimeZoneHelper
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static DateTime MountainToUtc(DateTime mountainDateTime)
    {
         TimeZoneInfo zone = TimeZoneInfo.FindSystemTimeZoneById("Mountain Standard Time"); // "Mountain Standard Time" it's the ID regardless of daylight saving time (i.e. no matters if right now is daylight saving time, the ID name remains the same)

         DateTime utcTime = TimeZoneInfo.ConvertTimeToUtc(mountainDateTime, zone); // it automatically takes care of daylight saving time

         return utcTime;
    }
      
    [Microsoft.SqlServer.Server.SqlFunction]
    public static DateTime PacificToUtc(DateTime pacificDateTime)
    {
         TimeZoneInfo zone = TimeZoneInfo.FindSystemTimeZoneById("Pacific Standard Time"); // "Pacific Standard Time" it's the ID regardless of daylight saving time (i.e. no matters if right now is daylight saving time, the ID name remains the same)

         DateTime utcTime = TimeZoneInfo.ConvertTimeToUtc(pacificDateTime, zone); // it automatically takes care of daylight saving time

         return utcTime;
    }
}

Save the file as TimeZoneHelper.cs
Open the Visual Studio 2008 Command Prompt (I also tried the 2010 version but it didnt work) and run the following:

csc /target:library C:\Users\jon.connor\Downloads\TimeZoneHelper.cs

This will create a TimeZoneHelper.dll in the same directory where the source file is in.
In SQL Server run the following to enable CLR code to run:

sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

ALTER DATABASE Northwind SET TRUSTWORTHY ON;
GO

You also have to grant permissions to a SQL Server login:

GRANT UNSAFE ASSEMBLY TO some_user;
GO

Now you can create the assembly in the Northwind database

CREATE ASSEMBLY TimeZoneHelperAssembly 
FROM 'C:\Users\jon.connor\Downloads\TimeZoneHelper.dll' 
WITH PERMISSION_SET = UNSAFE
GO

Finally create functions based on the assembly methods.

CREATE FUNCTION dbo.MountainToUTC(@mountainDateTime datetime) 
RETURNS datetime 
AS EXTERNAL NAME 
[TimeZoneHelperAssembly].[TimeZoneHelper].[MountainToUtc]
GO

CREATE FUNCTION dbo.PacificToUTC(@pacificDateTime datetime) 
RETURNS datetime 
AS EXTERNAL NAME 
[TimeZoneHelperAssembly].[TimeZoneHelper].[PacificToUtc]
GO

And now you can start using these functions as you would normally do:

select dbo.MountainToUTC('2012-04-13 16:00:00'), 
       dbo.PacificToUTC('2012-04-13 16:00:00')

Thanks to http://goo.gl/W4u42

Group by Date SQL Query

From: http://goo.gl/LfvLO
The following queries group the result set by month:

select Year(CreatedOnUTC), Month(CreatedOnUTC), COUNT(1) cont 
from ServiceLog 
group by Year(CreatedOnUTC), Month(CreatedOnUTC)
order by Year(CreatedOnUTC) desc, Month(CreatedOnUTC) desc

select dateadd(month, datediff(month, 0, CreatedOnUTC),0), COUNT(1) cont 
from ServiceLog
group by dateadd(month, datediff(month, 0, CreatedOnUTC),0)
order by dateadd(month, datediff(month, 0, CreatedOnUTC),0) desc

Kill All Active Connections To A Database

use master

ALTER DATABASE [Northwind] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

ALTER DATABASE [Northwind] SET MULTI_USER

Thanks to:
http://goo.gl/QqmX2
http://goo.gl/xNM2m

Default Field Value at Table Creation Time

Syntax:

[FieldName] type CONSTRAINT [constraint_name] DEFAULT default_value

Example:

CREATE TABLE [Clients]

(

    ClientID  bigint   NOT NULL IDENTITY(1,1) CONSTRAINT [PK_Clients] PRIMARY KEY,

    CreatedOn datetime NOT NULL CONSTRAINT [DF_Clients_CreatedOnUtc] DEFAULT GETDATE()

    ...

)

And for an already created table:

ALTER TABLE Clients

ADD CONSTRAINT [DF_Clients_CreatedOn] DEFAULT GETDATE() FOR CreatedOn

Unique Fields at Table Creation Time

Sintax:

[FieldName] type CONSTRAINT [constraint_name] UNIQUE

Example:

CREATE TABLE [Clients]
(
    ClientID int NOT NULL IDENTITY(1,1) CONSTRAINT [PK_Clients] PRIMARY KEY,
    ServiceCode varchar(50) NOT NULL CONSTRAINT [UQ_Clients_ServiceCode] UNIQUE
    ...
)

Example 2:

CREATE TABLE [Clients]
(
    ClientID int NOT NULL IDENTITY(1,1) CONSTRAINT [PK_Clients] PRIMARY KEY,
    ServiceCode varchar(50) NOT NULL,
    CONSTRAINT [UN_Clients_Unique] UNIQUE (ClientID, ServiceCode)
    ...
)

SQL Server Config For Remote Access

SQL Loop with Temp Table

declare @id int 

while exists(select 1 from #ids)
begin
 select top 1 @id = id from #ids
 
 --
 -- do stuff
 --
 
 delete from #ids where id = @id
end

Substring and Character Position

String fucntions

declare @name varchar(10) = 'SMITH,JON' 

SELECT SUBSTRING(@name, CHARINDEX(',', @name)+1, LEN(@name)) AS Name

Result:

Name
----------
JON

RESEED Table

SQL Command:
    DBCC CHECKIDENT(Employees, reseed, 1073741824)

Result/Message:
    Checking identity information: current identity value '44', current column value '1073741824'.
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

XML SQL Input Parameter

XML:

 
  50
  Jon
  Doe
  A
 
 
  99
  Jon
  Connor
  A
 
 
  1235
  James
  Bond
  B
 

SQL:

declare @Clients xml

set @Clients = 
'
 
  50
  Jon
  Doe
  A
 
 
  99
  Jon
  Connor
  A
 
 
  1235
  James
  Bond
  B
 
'

declare @clientsTable as table
(
    ID       int,
    Name     varchar(20),
    LastName varchar(20),
    Class    char(1)
)

-- IMPORTANT: 
-- value in col.value MUST be lowercase, otherwise this INSERT will fail.
INSERT INTO @clientsTable (ID
                          ,Name
                          ,LastName
                          ,Class)
SELECT   col.value('id[1]',       'int')         AS ID
        ,col.value('name[1]',     'varchar(20)') AS Name
        ,col.value('lastname[1]', 'varchar(20)') AS LastName
        ,col.value('class[1]',    'char(1)')     AS Class
FROM @Clients.nodes('//Client') tab(col)


select * from @clientsTable

Result:

ID          Name                 LastName             Class
----------- -------------------- -------------------- -----
50          Jon                  Doe                  A
99          Jon                  Connor               A
1235        James                Bond                 B

Random Number SQL

From http://goo.gl/vzyje

-- random integer BETWEEN 4 AND 6 - [4, 6]
SELECT 4 + CONVERT(INT, (6-4+1)*RAND())

NOLOCK

SELECT *
FROM Users u (NOLOCK)
INNER JOIN Permissions p (NOLOCK) ON p.UserID = u.UserID
WHERE u.IsActive = 1
ORDER BY u.CreatedOn ASC

Query Tables Size

From http://goo.gl/dF6Cv
and http://goo.gl/jxTTa

CREATE TABLE #TempTable
(
    tableName varchar(100),
    numberofRows varchar(100),
    reservedSize varchar(50),
    dataSize varchar(50),
    indexSize varchar(50),
    unusedSize varchar(50)
)

INSERT INTO #TempTable
EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?'"

SELECT *, cast(cast(replace(reservedSize, ' KB', '') as int)/1024 as varchar) + ' MB' AS Size 
FROM  #TempTable 
ORDER BY cast(replace(reservedSize, ' KB', '') as int) DESC

Result:

Rename column

Script:

IF EXISTS (select 1 from sys.objects where name = 'Users') begin -- maybe this IF is redundant.
 if exists ( select 1 from sys.tables t
             join sys.columns c on c.object_id = t.object_id
             where t.name = 'Users' and c.name = 'UserName') begin
  exec sp_RENAME 'Users.UserName', 'LoginName' , 'COLUMN'
 end
end
GO

New NOT NULL column on existing table

Script:

if not exists ( select 1 from sys.tables t
                join sys.columns c on c.object_id = t.object_id
                where t.name = 'PushInterfaceRESTLog' and c.name = 'UserType')
BEGIN
    ALTER TABLE PushInterfaceRESTLog 
    ADD UserType varchar(100) NULL 
END
GO

if exists ( select 1 from sys.tables t
            join sys.columns c on c.object_id = t.object_id
            where t.name = 'PushInterfaceRESTLog' and c.name = 'UserType')
BEGIN
    UPDATE PushInterfaceRESTLog SET UserType = 'Vendor'
    
    ALTER TABLE PushInterfaceRESTLog 
    ALTER COLUMN UserType varchar(100) NOT NULL  
END
GO