How Tos

Create a system stored procedure

TimChapman — Thu, 29 Oct 2009 23:28:00 GMT

A system stored procedure is a proc that is stored in the master database that you can run in the context of other databases on the instance. For example, sp_msforeachtable is an example of a system stored procedure. You can run this procedure in any database on the instance and it will loop through the tables in the database and issue a command against each.

As an example, here is a stored procedure I wrote when SQL Server 2005 first came out. This procedure uses a dynamic management view to recurse through any blocking connections and indicates the connection that is doing the blocking along w/ the blocking statement. Making this a system stored procedure allows you to have the procedure in a centralized location, but with the advantage of being able to run it from any database on the instance.

USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*********************************************************************************************
*  Procedure Name:  dbo.sp_Blocking
*  Author:  Tim Chapman
***********************************************************************************************/
CREATE PROCEDURE [dbo].[sp_Blocking]
AS
BEGIN
    IF OBJECT_ID('tempdb..#Processes') IS NOT NULL
        DROP TABLE #Processes

    SELECT 
        s.spid, BlockingSPID = s.blocked, DatabaseName = DB_NAME(s.dbid),
        s.program_name, s.loginame, ObjectName = OBJECT_NAME(objectid, s.dbid), 
        Definition = CAST(text AS VARCHAR(MAX)), StatementBeginTime = last_batch
    INTO #Processes
    FROM 
        sys.sysprocesses s
        CROSS APPLY sys.dm_exec_sql_text (sql_handle)
    WHERE 
        s.spid > 50;

    WITH Blocking
    (
        SPID, BlockingSPID, BlockingObject, BlockingStatement, RowNo, LevelRow, 
        StatementBeginTime, BlockingStatementBeginTime
    ) 
    AS
    (
        --//BASE CASE FOR QUERY
        SELECT 
            s.SPID , s.BlockingSPID, s.ObjectName, s.Definition, ROW_NUMBER() OVER(ORDER BY s.SPID), 0 AS LevelRow, 
            s1.StatementBeginTime, s.StatementBeginTime
        FROM 
            #Processes s
            JOIN #Processes s1 ON s.SPID = s1.BlockingSPID
        WHERE 
            s.BlockingSPID = 0
        UNION ALL
        --//RECURSIVE CASE
        SELECT 
            r.SPID,  r.BlockingSPID, r.ObjectName, r.Definition, d.RowNo, d.LevelRow + 1, 
            d.BlockingStatementBeginTime, r.StatementBeginTime
        FROM 
            #Processes r
            JOIN Blocking d ON r.BlockingSPID = d.SPID
        WHERE 
            r.BlockingSPID > 0
    )
    SELECT 
        SPID, 
        BlockingSPID, 
        BlockingObject, 
        RowNo, 
        LevelRow, 
        BlockingStatementBeginTime, 
        StatementBeginTime, 
        PotentialBlockingDurationS = ABS(DATEDIFF(ms, StatementBeginTime, BlockingStatementBeginTime))/1000.0
    FROM Blocking
    ORDER BY 
        RowNo, LevelRow
END

Once my procedure has been created in the master database, I can use a system stored procedure to mark the procedure as a system stored procedure. :)

EXECUTE sp_ms_marksystemobject 'sp_Blocking'

From there, I can run the following in any database and it will produce any blocking procedures.

EXECUTE sp_Blocking

run_duration in SQL Server system tables

Feodor Georgiev — Mon, 26 Oct 2009 15:31:00 GMT

If you are interested in the run duration of the jobs on your SQL Server you would most likely query the system tables sysjobs and sysjobhistory from the msdb database in a manner similar to this:

select name

,enabled

,run_status

,run_date

,run_time

,run_duration

from msdb.dbo.sysjobs sj

join msdb.dbo.sysjobhistory sjh

on sj.job_id = sjh.

What is interesting (and yes, it could be misleading!) is that the run_duration is presented by the following formula:

(hours*10000) + (minutes*100) + seconds

The bottom line is: do NOT think that the run duration is converted to seconds. Here is an example: if a job has a duration of 102950, this means that the job executed in 10 hours, 29 minutes and 50 seconds.

sp_MSForEachDB - leashed and unleashed

Feodor Georgiev — Fri, 09 Oct 2009 09:56:00 GMT

First things first - a few words on Microsoft undocumented procedures (and tools): it is this simple - USE THEM WITH CAUTION. There are several out there, and they are all great. If you compare sp_who (documented) with sp_who2 (UN-documented) you will see the advantages.

In the case of sp_MSForEachDB (UN-documented), there are several warnings I would like to bring to your attention:
1. Do not abuse it, since it is a global cursor.
2. UN-documented means that if something goes wrong, you will not be getting support

To the point:

sp_MSForEachDB accepts several parameters:

@RETURN_VALUE is an integer that is set to the return value associated with SP
@command1 is a nvarchar(2000) field for specifying the first command to run against each database
@replacechar is a nchar(1) field that represents the character used in the commands you are executing that will be replaced with the database name prior to being executed
@command2 is a nvarchar(2000) field for specifying the second command to run against each database
@command3 is a nvarchar(2000) field for specifying the third command to run against each database
@precommand is a nvarchar(2000) field for specifying a command to be run prior to processing any commands (@command1, @command2, @command3) against any databases
@postcommand is a nvarchar(2000) field for specifying a command to be run after all the commands against all databases have been processed.

Example:

exec dbo.sp_msForEachDb @command1='SELECT DB_NAME(DB_ID(''?'')) AS DatabaseName'

So far all is clear.

Here come the trick questions: How do I get to filter out some of the databases? Does this procedure always have to run for ALL databases?

Answers: Yes - we can filter out some databases. Here is how:

For example, we know that the system databases have id from 1 to 4. So, if we were able to run the procedure under a condition of "database id > 4", then we are set. HOW?

exec dbo.sp_msForEachDb @command1='IF DB_ID(''?'') > 4 BEGIN select DB_NAME(DB_ID(''?'')) AS DatabaseName END'

Good. And to bring it even further:

exec dbo.sp_msForEachDb @command1='IF DB_ID(''?'') in (1,3,5,7) BEGIN select DB_NAME(DB_ID(''?'')) AS DatabaseName END'

And so on...

Bottom line:

Think about this: BI (Business Intelligence) tools give similar functionality. In SQL Server we have "Maintenance Plans" GUI which is the essential functionality needed to create packages which run towards any set of databases.
Furthermore, if you would like to be elaborate, then you would use the BI tools (SSIS in particular) which give endless possibilities for loops, variable mapping, conditional task flows, and so on.

Determining Age through T-SQL

BrandonGalderisi — Wed, 22 Apr 2009 14:52:00 GMT

Sometimes it is necessary to calculate a person's age. I've seen people use a simple datediff on YEAR but that is not sufficient. The reason why that is not sufficient is because it ONLY evaluates a difference on the YEAR value of the date. So that means from December 31, 2008 to January 1, 2009 it would return 1. Obviously that's not correct. You have to also compensate for the day of the year.

There are a couple of ways this can be done. You can do inline SQL to calculate the value all the time, but the calculation takes a bit of code so it won't make reading it very easy. I prefer to use a User Defined Function.

In this user defined function the first thing we do is determine the difference in years. This is where most procedure I've seen stop. The next thing we do is add the value, stored in @Age, to the @BirthDate variable. This makes the year of the @BirthDate value the same year as the @AsOfDate. This is important because now we can do a simple > calculation. If @BirthDate hasn't come yet, then your age hasn't increased yet.

create function [dbo].[fn_GetAgeAsOf] (@BirthDate datetime, @AsOfDate datetime)
returns int
AS
begin
declare @Age   smallint
set @age=datediff(yy, @birthdate,@asofdate)
set @birthdate = dateadd(yy, @age, @birthdate)
if @birthdate>@AsOfDate
  set @Age=@Age-1
return @age
end
go

The function above will work, even when you are calculating age in a leap year. The below function, shows how using a simple datepart(dy,@Birthdate) > datepart(dy,@AsOfDate) will not work. The reason it won't work is the February 29 (during a leap year) and March 1 (during a non-leap year) both have the "Day of the Year" value of 60. This will throw off the calculation for all dates after February.

WARNING!!! This is not the function we are suggesting to use.  It is merely to show you why it doesn't work.

create function [dbo].[fn_GetAgeAsOf3] (@BirthDate datetime, @AsOfDate datetime)
returns int
AS
begin
declare @Age   smallint
select @age=datediff(yy, @birthdate,@asofdate)
if datepart(dy,@birthDate)>datepart(dy,@AsOfDate)
  set @Age=@Age-1
return @age
end
go

Now let's run a couple of scripts with these two methods to show ow they work when spanning a leap year both with the leap year being selected as the date and March 1 being selected.

select [dbo].[fn_GetAgeAsOf3]('2007-03-01','2008-02-29'),[dbo].[fn_GetAgeAsOf]('2007-03-01','2008-02-29')
select [dbo].[fn_GetAgeAsOf3]('2007-03-01','2008-03-01'),[dbo].[fn_GetAgeAsOf]('2007-03-01','2008-03-01')

Below is the cleanup scripts for this exercise.

if object_id('[dbo].[fn_GetAgeAsOf]') is not null
     drop function [dbo].[fn_GetAgeAsOf]
if object_id('[dbo].[fn_GetAgeAsOf3]') is not null
     drop function [dbo].[fn_GetAgeAsOf3]

I hope that you find this useful and understand now how it can be a little more complicated than the simple solution when doing calculations in SQL Server. To use the fn_AgeAsOf function to determine the current age, simply pass the current date as the @AsOfDate parameter. Be mindful that if you simply pass in GETDATE() without stripping the time component, you can run into problems with the times being different and not doing a straight day calculation.

How to determine the Sunday of a given week!

BrandonGalderisi — Mon, 06 Apr 2009 15:40:00 GMT

Sometimes it is necessary for reporting purposes to query data for a caldendar week. When doing so, us the the datepart function to retrieve the weeknum will not work because if a week spans more than one year it will only return the data for the same year. For example, January 1, 2009 is a Thursday. So the only dates that are in Week one of 2009 are January 1-3 (Thursday-Saturday). So how would you include from Sunday forward? The below function will help you.

This function will return, for a given @InputDate, the Sunday directly preceeding it. If the supplied @InputDate is a Sunday, it will return itself. This function also returns only the DATE portion, stripping the time.

Here's the function as well as a couple of sample calls.

Oops, We've found a bug.  We are temporarily removing this function while we fix it.

There you have it. Now if you need to run a query against all data from a table where the created_dttm is in the week of January 1, 2009.

declare @Sunday datetime
set @Sunday = dbo.fn_SundayOfWeek ('2009-01-01')

select * from SomeTable
where created_dttm >= @Sunday 
  and created_Dttm < @Sunday-7

The above query says to return all records whose created_dttm is between the Sunday before and the Saturday after, January 1, 2009.

I hope you found this useful. Check back soon to see what other useful utility scripts we may have provided.

Splitting a string into a table of characters

BrandonGalderisi — Wed, 25 Mar 2009 03:55:00 GMT

Sometimes you may not be able to see the characters in your data in SQL Server Mangement Studio (or another application). This simple function will allow you to pass an input string in, and it will return the individual characters as rows from a Table Valued Function.

First the code:

if object_id('[dbo].[fn_CharsToTable]') is not null
     drop function [dbo].[fn_CharsToTable]
go
create function [dbo].[fn_CharsToTable](@InputString varchar(max))
returns @Values TABLE
     (position      int not null primary key clustered
     ,theNChar      char(1)
     )
as
/************************************************************
*
*    Author:        Brandon Galderisi
*    Last modified: 25-Mar-2009
*    Purpose:       splits an input string (@InputString) 
*                   into a table of single characters.
*    
*
*************************************************************/
begin

insert into @Values (position,theNChar)
select n, substring(@InputString, n,1)
from    dbo.vw_Nums
where   n <= datalength(@InputString)
order by n
               
return
end
/*
-- The purpose of vw_Nums is if the source database does not have a numbers table.  This view (vw_nums)
-- will allow the parsing of up to 4 billion+ character strings with the above function.  Whether a static
-- table or this view is used for fn_DelimitedToTable, it can only split a string as long as the source 
-- numbers table.
Requires a nunbers table or this view:
create view vw_Nums
as
with   cte0 as (select 1 as c union all select 1), 
       cte1 as (select 1 as c from cte0 a, cte0 b), 
       cte2 as (select 1 as c from cte1 a, cte1 b), 
       cte3 as (select 1 as c from cte2 a, cte2 b), 
       cte4 as (select 1 as c from cte3 a, cte3 b), 
       cte5 as (select 1 as c from cte4 a, cte4 b), 
       nums as (select row_number() over (order by c) as n from cte5)
       select n from nums 
 
 
 
Sample Usage:
select * from [dbo].[fn_CharsToTable]('a|%25basdf|%25c|%25d')
select theValue from [dbo].[fn_CharsToTable]('a')
select * from [dbo].[fn_CharsToTable]('a basdf c d')

select * from [dbo].[fn_CharsToTable]('a|%25b
asdf|%25c|%25d')*/
GO

Using the function is simple. You simply select from it like any other Table Valued Function.

select position,theNChar,ascii(thenChar) as 'AsciiValue' from dbo.fn_charstotable('hello, my name is Brandon')

The result will be the position of the character within the string, the character itself (if it's not printable, you won't see it), and the ASCII value of the character.

The below is a unicode version of the same code:

if object_id('[dbo].[fn_CharsToTable]') is not null
     drop function [dbo].[fn_CharsToTable]
go
create function [dbo].[fn_CharsToTable](@InputString nvarchar(max))
returns @Values TABLE
     (position      int not null primary key clustered
     ,theNChar      nchar(1)
     )
as
/************************************************************
*
*    Author:        Brandon Galderisi
*    Last modified: 25-Mar-2009
*    Purpose:       splits an input string (@InputString) 
*                   into a table of single characters.
*    
*
*************************************************************/
begin

insert into @Values (position,theNChar)
select n, substring(@InputString, n,1)
from    dbo.vw_Nums
where   n <= datalength(@InputString)/2
order by n
               
return
end
/*
-- The purpose of vw_Nums is if the source database does not have a numbers table.  This view (vw_nums)
-- will allow the parsing of up to 4 billion+ character strings with the above function.  Whether a static
-- table or this view is used for fn_DelimitedToTable, it can only split a string as long as the source 
-- numbers table.
Requires a nunbers table or this view:
create view vw_Nums
as
with   cte0 as (select 1 as c union all select 1), 
       cte1 as (select 1 as c from cte0 a, cte0 b), 
       cte2 as (select 1 as c from cte1 a, cte1 b), 
       cte3 as (select 1 as c from cte2 a, cte2 b), 
       cte4 as (select 1 as c from cte3 a, cte3 b), 
       cte5 as (select 1 as c from cte4 a, cte4 b), 
       nums as (select row_number() over (order by c) as n from cte5)
       select n from nums 
 
 
 
Sample Usage:
select * from [dbo].[fn_CharsToTable]('a%25basdf%25c%25d')
select theValue from [dbo].[fn_CharsToTable]('a')
select * from [dbo].[fn_CharsToTable]('a basdf c d')

select * from [dbo].[fn_CharsToTable]('a%25b
asdf25c%25d')*/
GO

The usage of the function is the same, except instead of the ASCII() function, you would use the UNICODE() function.

select position,theNChar,unicode(thenChar) as 'AsciiValue' from dbo.fn_charstotable('hello, my name is Brandon')

Updating values in one table with values from another table

BrandonGalderisi — Thu, 19 Mar 2009 21:24:00 GMT

Sometimes, it is necessary to update a field in one table, with information from another table. In this article, I will go over with you a few different methods for doing this. At the bottom there is a link to download the entire SQL script used in this post.

The first thing that we will do is create a few temporary tables to test with. We will be loading these temporary tables, intentionally leaving the count columns in the #Employers table NULL because after each update we will set them back to NULL to verify that each update statement works.

create table #Employers
     (employer_id             int primary key clustered
     ,employer_name           nvarchar(50) not null unique
     ,history_employee_count  int null
     ,current_employee_count  int null
     )
go
create table #Employees
     (employee_id             int primary key clustered
     ,employee_fname          nvarchar(50) not null
     ,employee_lname          nvarchar(50) not null
     )
go
create table #Employment
     (employment_id           int identity primary key clustered
     ,employee_id             int not null
     ,employer_id             int not null
     ,start_date              datetime not null
     ,end_date                datetime null
     )
go

Next we will load the tables with sample data. This sample data will load 7 "fictional" employers, 8 fictional employees and map those employees to employers

insert into #Employers (employer_id,employer_name) 
     values(1,N'Acme Anvil Co.')
insert into #Employers (employer_id,employer_name) 
     values(2,N'Kwik-E-Mart')
insert into #Employers (employer_id,employer_name) 
     values(3,N'The Android''s Dungeon & Baseball Card Shop')
insert into #Employers (employer_id,employer_name) 
     values(4,N'Happy-Go-Lucky Toy Factory')
insert into #Employers (employer_id,employer_name) 
     values(5,N'SQL Server Nation')
insert into #Employers (employer_id,employer_name) 
     values(6,N'ABC Co.')
insert into #Employers (employer_id,employer_name) 
     values(7,N'XYZ Co.')
go
insert into #Employees (employee_id,employee_fname,employee_lname) 
     values(1,N'Apu',N'Nahasapeemapetilon')
insert into #Employees (employee_id,employee_fname,employee_lname) 
     values(2,N'Sanjay',N'Nahasapeemapetilon')
insert into #Employees (employee_id,employee_fname,employee_lname) 
     values(3,N'Jeff',N'Albertson')
insert into #Employees (employee_id,employee_fname,employee_lname) 
     values(4,N'Peter',N'Griffin')
insert into #Employees (employee_id,employee_fname,employee_lname) 
     values(5,N'Jonathon',N'Weed')
insert into #Employees (employee_id,employee_fname,employee_lname) 
     values(6,N'The',N'Roadrunner')
insert into #Employees (employee_id,employee_fname,employee_lname) 
     values(7,N'Brandon',N'Galderisi')
insert into #Employees (employee_id,employee_fname,employee_lname) 
     values(8,N'Tim',N'Chapman')
go
insert into #Employment(employee_id,employer_id,start_date,end_date) 
     values(1,2,'1989-12-17',null)
insert into #Employment(employee_id,employer_id,start_date,end_date) 
     values(2,2,'1989-12-17',null)
insert into #Employment(employee_id,employer_id,start_date,end_date) 
     values(3,3,'1989-12-17',null)
insert into #Employment(employee_id,employer_id,start_date,end_date) 
     values(4,4,'1999-01-31','2002-02-14')
insert into #Employment(employee_id,employer_id,start_date,end_date) 
     values(5,4,'1999-01-31','2002-02-14')
insert into #Employment(employee_id,employer_id,start_date,end_date) 
     values(6,1,'1959-01-01','1989-12-31')
insert into #Employment(employee_id,employer_id,start_date,end_date) 
     values(7,5,'2009-03-04',null)
insert into #Employment(employee_id,employer_id,start_date,end_date) 
     values(8,5,'2009-03-04',null)
insert into #Employment(employee_id,employer_id,start_date,end_date) 
     values(7,6,'2007-11-28',null)
insert into #Employment(employee_id,employer_id,start_date,end_date) 
     values(8,7,'2008-06-01',null)
go

Now that we have sample data to work with, let's go over the various methods for performing these updates.

The first method that we will use is the correlated sub-select. If you are familiar with sub-selects, both correlated and non-correlated, then this will be somewhat easy to understand. But just in case, I'll explain how this works. A sub-select is a select statement that returns a single value to be used in a select statement. The sub-select is correlated because it refers to a table that exists solely outside of the sub-select and is used to determine the value returned by the sub-select.

update #employers
set   history_employee_count = 
          (select count(distinct employee_id) 
           from #employment 
           where employer_id = #employers.employer_id
          )
     ,current_employee_count = 
          (select count(distinct employee_id) 
           from #employment 
           where employer_id = #employers.employer_id 
             and start_date <= getdate() 
             and (end_date > getdate() 
                  or 
                  end_date is null
                 )
          )

go
select * from #Employers

As you can see, the counts accurately represent the historical, and current employee counts for each company. Our goal, will be that each result set be identical to what you see below since this is the accurate result.

The next method that can be used in this situation is a JOIN to a derived table. If you are not familiar with them, a derived table is a select statement wrapped in parentheses that returns more than one column. One of the columns returned from the derived table is used to JOIN back to the table to be updated and the other is the value that will be used to update #Employers. In this case, we use individual JOIN statements to retrieve each value for the #Employers table. You will that the results of this select statement are identical to the first one.

update #employers
set   history_employee_count = null
     ,current_employee_count = null
go
update #employers
set   history_employee_count = isnull(hec.history_employee_count,0)
     ,current_employee_count = isnull(hcc.current_employee_count,0)
from #employers
  left join 
     (select employer_id, 
      count(distinct employee_id) history_employee_count 
      from #employment 
      group by employer_id
     ) hec
    on #employers.employer_id = hec.employer_id
  left join
     (select employer_id, 
      count(distinct employee_id) current_employee_count 
      from #employment 
      where start_date <= getdate() 
        and (end_date > getdate() 
             or 
             end_date is null
            )
      group by employer_id
     ) hcc
    on #employers.employer_id = hcc.employer_id

go
select * from #Employers

The last method that we will use is also a JOIN to a derived table, but in this case, we will use a single table to return both values. Again, you will see that the results of the select statement are identical to the first

update #employers
set   history_employee_count = null
     ,current_employee_count = null
go
update #employers
set   history_employee_count = isnull(hec.history_employee_count,0)
     ,current_employee_count = isnull(hec.current_employee_count,0)
from #employers
  left join 
     (select employer_id
          ,count(distinct employee_id) history_employee_count 
          ,sum(case when getdate() >= start_date 
                         and (getdate() < end_date or end_date is null) 
                         then 1 
                    else 0 
               end) current_employee_count
      from #employment 
      group by employer_id
     ) hec
    on #employers.employer_id = hec.employer_id
go
select * from #Employers

Finally, this will clean up the temporary tables used in this article.

drop table #Employers
drop table #Employees
drop table #Employment

What this shows is that there is more than one way to do a lot of common tasks. There isn't always a right way, because often times several methods will achieve the same result.

To download the entire SQL script used in this post, click here.

Hashing large data strings with a User Defined Function

BrandonGalderisi — Mon, 16 Mar 2009 01:53:00 GMT

If you have ever tryed to hash data in SQL Server using SQL Server's build in HASHBYTES, you know that that it doesn't accept greater than 8000 bytes of data. While that may work for a lot of scenarios, it doesn't help when you have large strings. Below we will go over the basics of a function that I use for HASHING large data strings.

The basic way that this function works is that it takes the input @String which is nvarchar(max), splits it into 8000 byte (4000 character) chunks and hashes them. Each 8000 byte chunk is hashed using SQL Server's built in HASHBYTES function. The result is then converted into a char(32) string using sys.fn_varbintohexstr so that they may be concatenated. Since each 4000 characters turns into a 32 character HASH, strings up to 1,000,000 characters will be hashed without having to make a recursive call. While the below function utilizes a CROSS JOINED common table expression to generate the numbers it will need, the optimal way to achieve this would be to use a numbers table.

create function dbo.fn_hashbytesMAX
     (@string nvarchar(max)
     ,@Algo    varchar(10)
    )
    returns binary(16)
as
/************************************************************
*
*    Author:        Brandon Galderisi
*    Last modified: 03-MAR-2009
*    Purpose:       uses the system function hashbytes as well
*                   as sys.fn_varbintohexstr to split an 
*                   nvarchar(max) string and hash in 8000 byte 
*                   chunks hashing each 8000 byte chunk,,
*                   getting the 32 byte output, streaming each 
*                   32 byte output into a string then hashing 
*                   that string.
*
*************************************************************/
begin
     declare    @concat       varchar(max)
               ,@concatX      XML
               ,@NumHash      int
               ,@HASH         binary(16)
     set @NumHash = ceiling((datalength(@string)/2)/(4000.0))
    /* HashBytes only supports 8000 bytes so split the string if it is larger */
    if @NumHash>1
    begin
                                                        -- # * 4000 character strings
          ;with a as (select 1 as n union all select 1) -- 2 
               ,b as (select 1 as n from a ,a a1)       -- 4
               ,c as (select 1 as n from b ,b b1)       -- 16
               ,d as (select 1 as n from c ,c c1)       -- 256
               ,e as (select 1 as n from d ,d d1)       -- 65,536
               ,f as (select 1 as n from e ,e e1)       -- 4,294,967,296 = 17+ TRILLION characters
               ,factored as (select row_number() over (order by n) rn from f)
               ,factors as (select rn,(rn*4000)+1 factor from factored)

          select @concat = cast((
          select right(sys.fn_varbintohexstr
                         (
                         hashbytes(@Algo, substring(@string, factor - 4000, 4000))
                         )
                      , 32) + ''
          from Factors
          where rn <= @NumHash
          for xml path('')
          ) as nvarchar(max))


          set @HASH = dbo.fn_hashbytesMAX(@concat ,@Algo)
    end
     else
     begin
          set @HASH = convert(binary(16), hashbytes(@Algo, @string))
     end

return @HASH
end
go

The below sample script will take a 100 character base string and expand it's size by concatenating the reverse of itself as it loops 16 times. The result is a 3,276,800 character string which is then hashed using the fn_HASHBYTESMAX user defined function


declare    @nv      nvarchar(max)
          ,@i       int
          ,@dt      datetime
set @i=1
set @NV=N'012345789a!@#$%^&*()!@#$%^&*()9abcdeghijkl!@#$%^&*()mno012345689abcde2356!@#$%^&*()789abcdefgijklmno'
while @i<16
     select @NV=@NV+reverse(@NV),@i=@i+1


set @dt=getdate()
select  dbo.fn_hashbytesMAX(@nv,'MD5')
select datediff(ms,@dt,getdate()) as 'Hash Time (ms)',datalength(@nv)/2 as 'String Character Length'
go

The above query will generate two results. The first is the BINARY(16) output of the function. The second is the time (in ms) that it took to hash the string and the size of the string. The machine that this was tested on is no powerhouse by today's standard, but you will see that it was able to hash a 3M+ character string in just 273ms.

I hope that you find this function useful. In the future we will be making use of this function to allow fast, INDEX searching of large fields.

User Defined Functions

BrandonGalderisi — Sat, 07 Mar 2009 17:50:00 GMT

User defined functions have many uses in SQL Server. Functions are useful to help clean up complex string manipulation logic in other areas of code and many other things. But convenience is not free. There is overhead in using functions and we will go over that later in the article.

There are two types of User Defined Functions in SQL Server, Scalar Valued and Table Valued Functions. Scalar Valued functions are used to return a single value whereas Table Valued functions return a table. The table can be one or more columns and one or more rows. While the structure for creating each type of function is nearly identical, how you use them is different.

First let's create a basic Scalar Valued function to show the proper way to construct your function. For Scalar Valued Functions, you start with the keywords "create function". The next thing is the name of the function. While it is not necessary to provide the schema name when naming the function, you will need to provide the schema name when using the function. The next thing you provide is the keyword "returns" followed by the data type to be returned. Returns says "This is the type of value that the function will return. The next thing is the keyword "as". Like in stored procedures, it separates the definition of the function from the code of the function. The next thing to know is that the code in a function MUST be contained within a BEGIN/END block. And the last thing to know is that the last thing that a function must do is RETURN the value.

This basic function uses the dateadd and datediff functions to strip the time portion of the @DateTime parameter to have it return the DATE at midnight.

create function dbo.fn_GetDateOnly(@DateTime datetime)
returns datetime
as
begin
return dateadd(d, datediff(d, 0, @DateTime), 0)
end
go

Now how do we use scalar valued functions? You use the function by selecting it.

select dbo.fn_GetDateOnly(getdate())

The result will be the the date which you run it with the time of midnight.

You can also use it to format a datetime column as you select it from your table.

select dbo.fn_GetDateOnly(SomeDateField) as SomeDate
from SomeTable

Next we will create a simple Table Valued Function. This function accepts a datetime parameter and then inserts the date of, before and after into the table for the function.

create function dbo.fn_DatesTable(@BaseDate datetime)
returns @Dates table (theDate datetime)
as
begin
insert into @Dates (Thedate)
values(Dateadd(d, datediff(d, 0, @BaseDate)-1, 0))
insert into @Dates (Thedate)
values(Dateadd(d, datediff(d, 0, @BaseDate), 0))
insert into @Dates (Thedate)
values(Dateadd(d, datediff(d, 0, @BaseDate)+1, 0))
return
end
GO

The below SQL shows you the difference in calling a Table Valued function as opposed to Scalar Valued functions. You don't select it, you select from it.

select * from dbo.fn_datestable('2009-03-07 12:00 pm')

The results will be:

Another, less commonly used syntax for creating a table valued function is below. This syntax is useful when you are using a user defined function in place of a view because you need to specify parameters.

create function dbo.fn_dosomething()
returns  TABLE
as

return 
(         select cast(1 as int) as Id, cast(N'Hello' as nvarchar(50)) as theValue
union all select 2,'Good Bye'
)
GO

Now comes the bad news. While functions are useful tools, they are not for every scenario. There is some overhead to using functions. The below Test script will loop 100,000 times and assign the function

declare 
      @SampleDate   datetime
     ,@dt           datetime
     ,@dt2          datetime
     ,@i            int
declare @res table
     (Method        varchar(20)
     ,Time_In_Ms    int
     )
set @SampleDate = getdate()
set @dt = getdate()
set @i=1
while @i<100000
begin
     set @dt2=dbo.fn_GetDateOnly(@sampleDate+@i)
set @i=@i+1
end
insert into @Res(Method,time_In_Ms)
values('Function', datediff(ms, @dt, getdate()))

set @dt = getdate()
set @i=1
while @i<100000
begin
     set @dt2=dateadd(d, datediff(d, 0, @SampleDate+@i), 0)
set @i=@i+1
end
insert into @Res(Method,time_In_Ms)
values('SQL', datediff(ms, @dt, getdate()))
select * from @Res

As you can see by this output, executing the same statement through a function is slower than using an inline SQL.

Now that you know the basics of functions, you are ready to get into some tougher examples. Look for more postings specific on scalar and table valued functions.

Table Valued Functions

BrandonGalderisi — Sat, 07 Mar 2009 17:33:00 GMT

A table valued function is a function that returns a table instead of a single value. In this post, we will go over the Table Valued Functions in more detail than we did in the general topic covering User Defined Functions. There are many uses for table valued functions so we will go over a couple of examples to ensure that you will be successful when you begin to create your own.

The first thing you need to understand is how you define a function as a Table Valued Function. This is done by defining a table variable as the type of value returned. The syntax for declaring the output table variable is similar to how you would declare a table variable in a stored procedure with the exception that instead of using the DECLARE keyword, you use RETURNS. The next thing, like every other function, is that you must have

In the below example we do a simple insert of two values into the table and returns.

create function dbo.fn_dosomething()
returns @A_Table TABLE
     (ident    int identity primary key clustered
     ,theValue nvarchar(50)
     )
as
begin

insert into @A_Table(theValue) values('Hello')
insert into @A_Table(theValue) values('Good Bye')

return
end
go

The below example is made to take a string containing a name and divide it into the first and last name. The below function will take a single input parameter and divide it into first and last name. This would be useful if, due to poor design decisions, the user table contained a single field for name instead of first and last names. Since the name field is a single field, you have no control over what format people entered their data in. They could have done "Last Name, First Name", "First Name Last Name" or simply "First Name". This function implements business logic to determine how the various values should be parsed.

The Function:

create function dbo.fn_SplitName(@Name varchar(100))
returns @Names table (FirstName varchar(100), LastName varchar(100))
as
begin
if datalength(@Name)>0
begin
     if charindex(',',@name)>0           -- Supports "Lastname, Firstname" Format
          insert into @Names (LastName, FirstName)
          select left(@Name, charindex(',',@Name)-1), 
                      ltrim(rtrim(right(@Name, charindex(',', reverse(@Name))-1)))
     else if charindex(' ', @Name)>0     -- Supports "Firstname LastName" Format
          insert into @Names (LastName, FirstName)
          select ltrim(rtrim(right(@Name, charindex(' ', reverse(@Name))-1))), 
                      left(@Name, charindex(' ',@Name)-1)
     else                                -- If no match above, return as first name.
          insert into @Names (FirstName)
          values(@Name)

end
return
end
go

This function searches for certain characters within the passed variable. It is not intended to be a useful function for parsing ALL formats of names, those have to be defined by your organization or needs. If you have the need for a specific User Defined Function, please post it in our SQL Help Forums and someone will help you out as soon as possible.

Table Valued Functions are useful in the place of views where you need to define parameters for what data is selected.

A View of Numbers

BrandonGalderisi — Sat, 07 Mar 2009 05:35:00 GMT

The below view will generate a list of numbers ranging from 1 - 4,294,967,296. This view can be used in place of a physical table of numbers for some small operations but since it utilizes several large cross joins it is not very efficient once it starts counting into large numbers.

create view vw_Nums
as
with   cte0 as (select 1 as c union all select 1), 
       cte1 as (select 1 as c from cte0 a, cte0 b), 
       cte2 as (select 1 as c from cte1 a, cte1 b), 
       cte3 as (select 1 as c from cte2 a, cte2 b), 
       cte4 as (select 1 as c from cte3 a, cte3 b), 
       cte5 as (select 1 as c from cte4 a, cte4 b), 
       nums as (select row_number() over (order by c) as n from cte5)
       select n from nums 
go

This view will be referenced for use in the user defined functions that we publish in case you don't have a numbers table

Converting a delimited string into a table

BrandonGalderisi — Sat, 07 Mar 2009 05:25:00 GMT

In this article, I will show you how you can convert a delimited string of data into a table of data. In order to do this we will use a table valued function. There are many ways to achieve this but this how I like to do it.

In order to use the function, you will need a numbers table. Since that's a topic that we haven't covered here yet, we will utilize a view that builds one on the fly. The view can be found in the A View of Numbers post. If you have a numbers table, replace "vw_nums" with the name of the table in the below function and replace "n" with the column name that contains your numbers.

Run the following SQL to create the function.

create function [dbo].[fn_DelimitedToTable](@DelimitedString nvarchar(max), @Delimiter nvarchar(32))
returns @Values TABLE
     (ident         int not null identity primary key clustered
     ,thePosition   int not null
     ,theValue      nvarchar(max)
     )
as
begin

insert into @Values (thePosition,theValue)
select n
  ,substring(@delimiter + @DelimitedString + @delimiter
            ,n + (datalength(@delimiter)/2)
            ,charindex(@delimiter, @delimiter + @DelimitedString + @delimiter
                      ,n + datalength(@delimiter)/2) - n - datalength(@delimiter)/2
                      ) as string_value
from    dbo.vw_Nums
where
    n <= (datalength(@delimiter + @DelimitedString + @delimiter)/2) - (datalength(@delimiter)/2)
and substring(@delimiter + @DelimitedString + @delimiter, n, (datalength(@delimiter)/2)) = @delimiter


return
end

GO

This function works by utilizing the numbers table to represent the characters in the string. This allows SQL Server to quickly find the @Delimiter in the string without doing any sort of while loops or cursors. It accepts a @DelimitedString and a @delimiter as input, and outputs three values. The @DelimitedString input parameter is a nvarchar(max) input parameter, but the actual string to be parsed can be no longer than the number of values in your numbers table.The "ident" field is an identity field that represents the position of the parsed string within the @DelimitedString. The field "thePosition" represents the starting position of the value within the @DelimitedString. The field "theValue" is the value within the @DelimitedString that falls between two instances of the @Delimiter.

Sample usage:
This will show you the basics of how this function works and what you can expect to see.

select * from [dbo].[fn_DelimitedToTable]('Jim,John,Mike,Bob,Tom',',')

As you can see, it returns the five names in the string, in the order they appeared. This script is useful when an application has to pass in a delimited list of values to search for. By splitting the values into a table it can be joined to for selecting multiple values.

This example shows that you can use a delimiter greater than one character. This is useful when your data can contain any character and there is no ONE character that you can use to delimit the string.

select * from [dbo].[fn_DelimitedToTable]('a|%25basdf|%25c|%25d','|%25')

Now let's talk about some possible uses for this type of functionality.

Let's say that your application needs to search and return a list of employees. There are many ways to pass in the parameters. One of which is XML. But SQL Server isn't the most efficient as processing XML so for this case we will use a delimited string. The first thing we need to do is setup our test table

Note: If the table name looks strange, I use a partial newid() value to make the table unique. Although we recommend that you never test on a production server, or even in a used database on a development server, we try to make the names unique to prevent conflicting with your existing tables.

create table dbo.employees_FB8AD58DADAF
     (userid        int identity primary key clustered
     ,firstname     nvarchar(50) not null
     ,lastname      nvarchar(50) not null
     )
GO
insert into dbo.employees_FB8AD58DADAF(firstname,lastname)
          select 'Jim', 'Smith'
union all select 'John', 'Smith'
union all select 'Mike', 'Smith'
union all select 'Bob', 'Smith'
union all select 'Tom', 'Smith'
union all select 'Tim', 'Smith'
union all select 'Rob', 'Smith'
union all select 'Matt', 'Smith'

Now let's see how we can use the above function to search. The below procedure accepts a @userlist parameter. The @userlist parameter is a comma delimited list of user first names to search for in the table we created. When we create and run the below procedure

create procedure dbo.up_employeesearch
      @UserList   nvarchar(max)
as
set nocount on
select e.userid,e.firstname,e.lastname from dbo.employees_FB8AD58DADAF e
  join [dbo].[fn_DelimitedToTable](@userlist,N',') t
    on e.firstname = t.thevalue
go
exec dbo.up_employeesearch @userlist = 'Jim,John,Mike,Bob,Tom'

We get the following results:

There are a lot of uses for this type of functionality and I'm sure you can already think of a few. Just remember that before you start making too much use of this, you should consider converting it to use a numbers table instead of the vw_nums view.

Stored Procedures

BrandonGalderisi — Fri, 06 Mar 2009 23:54:00 GMT

Stored procedures are part of the core functionality of a DBMS. SQL Server is no exception to that. Stored procedures have many uses. They can be used to handle basic CRUD operations as well as complex logic. There are several things that you have to consider when it comes to building stored procedures for you database.

Is this something that has to be done in the database?
Is this something that should be done in the database?
Does my database server have the capacity to handle this as it's use scales.

Above there are three questions you need to ask yourself but they are not the only three. Since there is no way for us to answer any of these questions, we'll talk briefly about it and concentrate on the what you can do and how.

Is this something that has to be done in the database?
There are benefits to keeping your logic contained in the database. It's easy to patch by installing new procedures or updating existing ones. All of the logic is contained inside of one location instead of being spread out in embedded SQL in applications.

Is this something that should be done in the database?
SQL Server's primary purpose is to store your information. And it's great at a lot of things, and not at others. The more work that is done in the database, the higher the load placed on the database server. This can lead to a lot of issues including increased CPU and memory usage which can take away from SQL Server's ability to do what it HAS to do.

Does my database server have the capacity to handle this as it's use scales?
I can't tell you how many times I've heard people say "there will only be 10 users" or "there will never be more than a few hundred records". If you write inefficient SQL because you expect small usage, then by habit you will write inefficient SQL for large scale operations. I'm not saying to over-engineer every table. Sometimes it is OK to bend the rules a little. But it has to be a clear and conscious decision to do such

Now on to Stored Procedures. I will first start by showing a very simple stored procedure that shows the structure, then we'll discuss it's structure. There are are lot of different things to know, even about the basics of stored procedures. So this will be a rather long posting.

CREATE PROCEDURE dbo.up_SelectHello
as
set nocount on
select 'Hello'
return
go

Above is a very basic, and impractical, procedure that does nothing other than select the word 'Hello'. To create a stored procedure, you start off by saying "CREATE PROCEDURE". The next thing that you do is give the name. You do not have to specify the schema, but it's a good idea to do so. Above, I have selected the dbo schema. The next thing after the name is the keyword "as". As has many uses in SQL Server. In stored procedures, it is used to separate the definition of the stored procedure from the code of the stored procedure. The next line, "set nocount on" is not specific to stored procedures, but it will prevent the stored procedure from outputing the number of rows effected by various operations in the query. Next is the simple select statement followed by return. Return is not required in stored procedures. But when a return is executed, the procedure exits immediately.

EXEC dbo.up_SelectHello

Calling the procedure from within SQL Server Management Studio is as simple as running the above command in a query window. An important thing to note is that if you are only executing one command in a batch, you do not need to include the EXEC. So the below statements are all valid was to call the procedure.

EXEC dbo.up_SelectHello

dbo.up_SelectHello

dbo.up_SelectHello
go
select 'Something Else'

This last is valid because the GO separates the first and second statement into two different batches.

CREATE PROCEDURE dbo.up_SelectInput
  @Input nvarchar(100)
as
set nocount on
select @input
return
go

The above goes one step further and uses an input parameter as the text to select. While this procedure is equally useless, it shows the structure of allowing input parameters. The structure is that you include the variable name and it's data type between the procedure name and the "as" statement. You can also provide multiple parameters by separating them by commas, as shown below.

CREATE PROCEDURE dbo.up_SelectInput
  @Input   nvarchar(100)
 ,@Input2  nvarchar(100)
as
set nocount on
select @input + @input2
return
go

Going to the next level, you can provide default values for input parameters.

CREATE PROCEDURE dbo.up_SelectInput
  @Input nvarchar(100) = N'Hello'
as
set nocount on
select @input
return
go

What this does is allow the parameter to have a value if the parameter is not specified in the execution call. This does not mean that if you specify NULL that it will use the value So if the following 3 procedure calls are made:

EXEC dbo.up_SelectInput @input=N'Hello'
EXEC dbo.up_SelectInput 
EXEC dbo.up_SelectInput NULL

The results in order will be:

Note that for the last call, I did not specify @input in the EXEC statement. SQL Server does not require you to specify the parameter name when passing them to a procedure call. Instead, it will accept the parameters in the order in which they are provided. But if you specify the name for a parameter, you must specify the name for all subsequent parameters.

So with the following procedure:

CREATE PROCEDURE dbo.up_SelectInput
  @Input   nvarchar(100) = N'hello'
 ,@Input2  nvarchar(100) = N'goodbye'
as
set nocount on
select @input + N' ' + @input2
return
go

You will get the following results with these commands:

exec dbo.up_SelectInput @input=N'SQL Server' ,@input2=N'Nation'

exec dbo.up_SelectInput N'SQL Server',@input2=N'Nation'

exec dbo.up_SelectInput @input=N'SQL Server', N'nation'

Msg 119, Level 15, State 1, Line 1
Must pass parameter number 2 and subsequent parameters as '@name = value'. After the form '@name = value' has been used, all subsequent parameters must be passed in the form '@name = value'.

Output parameters allow you to get data OUT of a stored procedure. In order to specify a parameter as an OUTPUT parameter, you add the keyword OUTPUT after the data type definition. This does not mean that you can not pass a value in if it is defined as output, but it allows you to pass a value out. In the below procedure, you will see that we are concatenating the first two parameters and assigning it to the third parameter.

CREATE PROCEDURE dbo.up_SelectInput
  @param1  nvarchar(100) = N'hello'
 ,@param2  nvarchar(100) = N'goodbye'
 ,@param3  nvarchar(100) output
as
set nocount on
set @param3 = @param1 + N' ' + @param2  
return
go

Now to get the value from the parameter, it is a little different. Since executing the procedure will not select any data, we have to retrieve the value using a parameter. Below we will pass in the parameter @output to accept the value of @param3.

declare @output nvarchar(100)
exec dbo.up_SelectInput @param1 = N'SQL Server', @param2 = 'Nation', @param3 = @output output
select @output

Selecting @output you will see the the following result.

The last thing that I want to cover in this is the difference between return codes and output parameters. The return code is the status of the procedure. It is the value that is returned by the RETURN statement in the procedure. By default, if you do not specify a value in the RETURN statement, the default of 0 is used. You can only return an INTEGER from a procedure. You can not return dates, or any other data types. Only integers. The default return code of 0 typically indicates that that the procedure executed successfully.

Let's test using return codes. We will start with the same basic procedure that we have been using. and we will show how to call the procedure to capture the return code. Unlike output parameters, return codes are captured using the "exec @parameter = procedure" syntax.

CREATE PROCEDURE dbo.up_SelectInput
  @param1  nvarchar(100) = N'hello'
 ,@param2  nvarchar(100) = N'goodbye'
 ,@param3  nvarchar(100) output
as
set nocount on
set @param3 = @param1 + N' ' + @param2  
return 
go
declare @output          nvarchar(100)
       ,@ReturnCode      INT
exec @ReturnCode = dbo.up_SelectInput @param1 = N'SQL Server', @param2 = 'Nation', @param3 = @output output

select @ReturnCode

As you will see, the value of @returncode is 0.

CREATE PROCEDURE dbo.up_SelectInput
  @param1  nvarchar(100) = N'hello'
 ,@param2  nvarchar(100) = N'goodbye'
 ,@param3  nvarchar(100) output
as
set nocount on
set @param3 = @param1 + N' ' + @param2  
return 1
go

declare @output          nvarchar(100)
       ,@ReturnCode      INT
exec @ReturnCode = dbo.up_SelectInput @param1 = N'SQL Server', @param2 = 'Nation', @param3 = @output output

select @ReturnCode

As you will see, the value of @returncode is 1.

Return codes are useful for passing information back about what happened in a procedure. For example a user authentication procedure could be used to validate that a username and password hash passed in are valid for a particular user. Using return codes you could differentiate between a successful authentication, a failed password match, an invalid username, or anything else you would desire.

I hope that you enjoyed this overview on the basic structure of stored procedures. Understanding how to construct stored procedures is an essential skill that you will need if you want to become a SQL Server master.

Changing the display format of DATETIME values

BrandonGalderisi — Fri, 06 Mar 2009 03:24:00 GMT

Often times, people want to change the format of a datetime value returned from SQL Server. There are some things that you need to consider before doing this. The first thing that you need to be aware of is that SQL Server stores DATETIME values in an 8 byte value in the database, not as a string. So changing the display format will change your DATETIME into a VARCHAR The SMALLDATETIME datatype is stored in a 4 byte value. SQL Server 2008 introduces several new date datatypes including DATETIME2, DATE, and TIME. The DATE and TIME data types will solve a lot of headaches for a lot of people because often times when people want to convert a DATETIME value to a varchar, it is because they want to display only the date or time portions of the value.

To display a date in a different format other than the default, you will need to know the format code for the date. For a full list of format codes, visit the Informational Downloads section. Below, we will show some of the more popular formats.

declare @Date datetime
set @Date = cast('2008-04-01 12:01:23.123' as datetime)

Format Code	Format	SQL	Output
1	MM/DD/YY	select convert(char(8), @date, 1)	04/01/08
3	DD/MM/YY	select convert(char(8), @date, 3)	01/04/08
7	MMM DD, YY	select convert(char(10), @date, 7)	Apr 01, 08
9	MMM D YYYY H:MM:SS:nnnAP	select convert(varchar(32), @date, 9)	Apr 1 2008 2:01:23:123PM
101	MM/DD/YYYY	select convert(char(10), @date, 101)	04/01/2008
103	DD/MM/YYYY	select convert(char(8), @date, 103)	01/04/2008
107	MMM DD, YYYY	select convert(char(12), @date, 107)	Apr 01, 2008
111	YYYY/MM/DD	select convert(char(10), @date, 111)	2008/04/01
112	YYYYMMDD	select convert(char(8), @date, 112)	20080401

You will find the above codes useful if you are generating SQL Queries that must display formatted data. But it is always best to leave your datetime values as DATETIME values and leave the formatting to your application where you can!