David's Blog

Sooner or later, you’ll come across the need to pass “lists” of items into a SQL Server Stored Procedure. Unfortunately, SQL Server doesn’t help us out much here. There are a lot of approaches¹, but most of them seem more “workaround” than “solution” in nature.

When dealing with fixed length data-items, you can make use of the [sequence_generator] function that I showed in an earlier post. Here’s an example function to extract UNIQUEIDENTIFIER values from a VARBINARY(max) input parameter:

CREATE FUNCTION [dbo].[unpivot_uniqueidentifiers](@uniqueidentifiers VARBINARY(max))

      RETURNS TABLE

      WITH SCHEMABINDING

AS RETURN

      SELECT v = CAST(SUBSTRING(@uniqueidentifiers, seq.i, 16) AS UNIQUEIDENTIFIER)

            FROM [dbo].[sequence_generator](DATALENGTH(@uniqueidentifiers) / 16, 1, 16) seq

You can easily adapt the above function for INTEGER, DATETIME and other fixed-length data-types. Turning the “list” into a table is straightforward:

DECLARE @input VARBINARY(max)

–Build a “list” of (up to 100) UNIQUEIDENTIFIERs

SELECT @input = ISNULL(@input, CAST(NEWID() AS BINARY(16))) + CAST(NEWID() AS BINARY(16))

      FROM [dbo].[sequence_generator](RAND() * 100, 0, 1)

–”unpivot” into a table

SELECT ui.v
      FROM [dbo].[unpivot_uniqueidentifiers](@input) ui

¹ See this article by Erland Sommarskog for a detailed discussion of many approaches (including a variation of the method discussed here) to representing lists in SQL Server.

I’ve come across many SQL Server database designs where the vast majority of the data-types used were INTEGER, NVARCHAR, UNIQUEIDENTIFIER and DATETIME.

If you have a domain table, with less than say 100 rows, it has an INTEGER Primary Key and it’s rarely updated; perhaps TINYINT would meet your needs?

If you’re storing ASCII character data now and in the future, e.g. an “email address” column, there’s no need to use NVARCHAR, use VARCHAR instead. URLs and codes (e.g. ISO country codes) are further examples that may not require Unicode support.

When storing date/time information, do you really need granularity down to the nearest three milliseconds? If not, maybe SMALLDATETIME (nearest minute) will meet your requirements instead of DATETIME?

How about fixed length strings? Use (N)CHAR instead of (N)VARCHAR – save those two bytes of storage that the CHARACTER VARYING types use to record the length of the string.

If you’re careful when choosing your data-types you’ll reap the benefits in terms of reduced storage requirements and faster performing queries – reduced Memory, I/O and CPU storage/overhead (from greater index/data page density) almost always translates to improved performance. You’ll be making more efficient use of buffer-cache (more data in cache and/or more room for the plan-cache (which can be at least as large as the buffer cache for some workloads)) and transfer less data over your (relatively) slow LAN/WAN connection to the client resulting in lower latency and hence better client response times. In fact, the knock-on benefits are so compelling; it’s well worth that little extra effort up-front with your schema design.

A note on implicit data-conversion
Ensure that your literals and variable data-types match up with the column data-type. Otherwise, you may (in certain circumstances) inadvertently prevent an index from being eligible for consideration.

What if you want to have the output from a stored procedure written to a temporary table, but you don’t want to have to worry about defining the temp table beforehand? Unfortunately, SQL Server doesn’t have an EXECUTE […]INTO [#temp_table] syntax, or similar. However, as a workaround, you can use SELECT INTO and OPENQUERY (looping-back to the same server) to achieve the desired result:

SELECT *
      INTO #who
      FROM OPENQUERY(YOUR_SERVER_NAME, ‘EXEC sp_who’)

You’ll need to define your server as a linked server for the above to work.

This technique is also a useful way of figuring out the data-types returned by a particular stored procedure:

SELECT TOP 0 *
      INTO #who
      FROM OPENQUERY(YOUR_SERVER_NAME, ‘EXEC sp_who’)

EXEC tempdb..sp_columns ‘#who’

There are occasions when it’s useful to have a function that returns a sequence. Here’s the function that I use for this purpose:

CREATE FUNCTION [dbo].[sequence_generator] (@iterations INT, @base INT, @increment SMALLINT)
    RETURNS TABLE
    WITH SCHEMABINDING
AS RETURN
WITH [seq] AS (
    SELECT d = ‘x’ UNION ALL SELECT ‘x’ UNION ALL SELECT ‘x’ UNION ALL SELECT ‘x’
        UNION ALL SELECT ‘x’ UNION ALL SELECT ‘x’ UNION ALL SELECT ‘x’ UNION ALL SELECT ‘x’
        UNION ALL SELECT ‘x’ UNION ALL SELECT ‘x’ UNION ALL SELECT ‘x’ UNION ALL SELECT ‘x’
        UNION ALL SELECT ‘x’ UNION ALL SELECT ‘x’ UNION ALL SELECT ‘x’ UNION ALL SELECT ‘x’
)
    SELECT TOP(@iterations) i = (@base) + ((ROW_NUMBER() OVER(ORDER BY s1.d) - 1) * @increment)
        FROM [seq] s1, [seq] s2, [seq] s3, [seq] s4, [seq] s5, [seq] s6, [seq] s7, [seq] s8

To get the first 100 rows of a zero-based sequence with +1 increments:

SELECT *
    FROM [dbo].[sequence_generator](100, 0, 1)

I’ll be referring back to this function in future posts.

Welcome to my blog. Many of the posts you’ll find here will be C++ / C# / .NET or Microsoft SQL Server related, but I’ll occasionally post entries about other stuff too.