“Do It All at Once” Philosophy … A Set Based Approach Using a Tally or Numbers Table

When it comes to database programming, two of the key principles are that the code should run quickly and be scalable.  And this is really where a lot of people coming from traditional programming backgrounds get into the most trouble with SQL Server.  For example, a common task for a database professional is to parse and/or search strings in lists and tables and then put them in a table.  Now, some people would implement this as some type of looping construct, such as a cursor, if they are not familiar with set based processing.  RBAR (row by agonizing row) processing is not good!  It can be likened to the difference between going to a car shop and having them change your tires one at a time, and having a Nascar pit crew change all your tires all at once in 10-20 seconds!

So, when doing database programming, make sure the code is written using the “do it all at once” philosophy, which essentially means using a set based method!

One set based method I like to use is a tally or numbers table.  I am going to show you how it can make some tasks easier, such as parsing a string or even searching a column of a table.

Creating a tally or numbers table
First of all, a tally or numbers table is just simply a table that has numeric values in it.  Here’s an easy way to create one:

INTO dbo.Tally
FROM master.dbo.SysColumns sc1, master.dbo.SysColumns sc2

Parsing a delimited list (string) and populating a table with the results
As I mentioned, a common task with SQL Server programming is to parse a delimited list and put it in a table.  For instance, maybe you are working on a stored procedure that has a parameter that will be sent as a comma-delimited list of unknown length that you need to put in a table.  One requirement you will notice is the code below is that the delimiter has to be at the beginning and end of the string.

DECLARE @Parameter VARCHAR(8000)
DECLARE @SearchString VARCHAR(100)
SELECT @Parameter = ',Element01,Element02,Element03,Element04,Element05,'
SELECT @SearchString = ','

SELECT N, SUBSTRING(@Parameter, N + LEN(@SearchString), ABS(CHARINDEX(@SearchString, @Parameter, N + LEN(@SearchString)) - (N + LEN(@SearchString))))
FROM dbo.Tally
WHERE N < LEN(@Parameter)
AND SUBSTRING(@Parameter, N, LEN(@SearchString)) = @SearchString

Search a table column and populate a table
Another example where this may be useful is when you have to search a column of a table for a specific string.  For example, imagine that a column name in a table changed, and you needed a quick way to search for all the stored procedures where that column name was referenced.

DECLARE @SearchString VARCHAR(100)
DECLARE @ContextLength INT
SELECT @SearchString = 'sql_server'
SELECT @ContextLength = 10

SELECT ObjectName = o.name,
ObjectType =
CASE o.xtype
WHEN 'C' THEN 'CHECK constraint'
WHEN 'D' THEN 'Default or DEFAULT constraint'
WHEN 'F' THEN 'FOREIGN KEY constraint'
WHEN 'FN' THEN 'Scalar function'
WHEN 'IF' THEN 'In-lined table-function'
WHEN 'P' THEN 'Stored procedure'
WHEN 'PK' THEN 'PRIMARY KEY constraint (type is K)'
WHEN 'RF' THEN 'Replication filter stored procedure'
WHEN 'S' THEN 'System Table'
WHEN 'TF' THEN 'Table function'
WHEN 'TR' THEN 'Trigger'
WHEN 'U' THEN 'User table'
WHEN 'UQ' THEN 'UNIQUE constraint (type is K) '
WHEN 'V' THEN 'View'
WHEN 'X' THEN 'Extended stored procedure'
Context = SUBSTRING(CAST(c.text AS VARCHAR(8000)), N - @ContextLength, LEN(@SearchString) + (2 * @ContextLength))
FROM dbo.Tally t
CROSS JOIN dbo.syscomments c
INNER JOIN dbo.sysobjects o ON c.id = o.id
WHERE N < LEN(CAST(c.text AS VARCHAR(8000)))
AND SUBSTRING(CAST(c.text AS VARCHAR(8000)), N, LEN(@SearchString)) = @SearchString

So, when doing database programming always use the “do it all at once” philosophy, or a set based approach.  And one handy method to do this is by using a tally or numbers table.

This entry was posted in Developer, TSQL and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s