Create a Sequence with a table or a UDF.

Last week's issue was about a reader contribution, fnc_10_parse_string, a UDF that returns a two column table. The first column of the table is a sequence number. The second column is a character from the input string. Here's sample output from fnc_10_parse_string:

SELECT * FROM
 dbo.fnc_10_parse_string('ABBBCCDEEADBBDADCCCEDCCCCECEC')
GO

(Results)
itemid      item
----------- -------------
1           A
2           B
3           B
4           B
5           C
6           C
7           D
8           E
9           E
10          A
11          D
12          B
13          B
14          D
15          A
16          D
17          C
18          C
19          C
20          E
21          D
22          C
23          C
24          C
25          C
26          E
27          C
28          E
29          C

After reading it Dennis Deming wrote to me with an alternative coding of the function that relied on a sequence table. If you haven't used one before a sequence table is just a table of numbers. Using a sequence table makes it possible to write fnc_10_parse_string without using the identity column. Here's the CREATE TABLE Script for a typical sequence table

CREATE TABLE [dba_Sequence] (
	[SeqNbr] [int] NOT NULL ,
	CONSTRAINT [PK_dba_Sequence] PRIMARY KEY  CLUSTERED 
	(
		[SeqNbr]
	)  
)  
GO

To create and populate the table I employ this stored procedure:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE dba_Sequence_Populate 

    @MaxNbr int = 100000 -- Populate from 0 to this number

/*
* Stored procedure to create and populate a sequence table.
* The number of rows inserted will be one more than @MaxNbr
* because a row is inserted for zero.
*
* Example:
exec dba_Sequence_Populate 10000
****************************************************************/
AS 

DECLARE @Nbr int
      , @FinalCnt int

SET NOCOUNT ON

IF OBJECT_ID('dba_Sequence') IS NOT NULL DROP TABLE dba_Sequence

CREATE TABLE [dba_Sequence] (
	[SeqNbr] [int] NOT NULL ,
	CONSTRAINT [PK_dba_Sequence] PRIMARY KEY  CLUSTERED 
	(
		[SeqNbr]
	)   
)  

SET @Nbr = 0

WHILE @Nbr <= @MaxNbr BEGIN
	INSERT INTO dba_Sequence VALUES(@Nbr)
    SET @Nbr = @Nbr + 1
END

SELECT @FinalCnt=COUNT(*) FROM dba_Sequence

PRINT ' INSERTED ' + convert(varchar(20), @FinalCnt) 
      + ' rows into the dba_Sequence table.'


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

With a populated sequence table Dennis provided this alternative coding for fuc_10_parse_string, slightly modified by me:

CREATE FUNCTION dbo.fnc_10_parse_string(

    @vcString varchar( 8000 )

) RETURNS @tablevalues TABLE( itemid int, item char( 1 )) 
AS BEGIN
    INSERT INTO @tablevalues
        SELECT  SeqNbr,
            SUBSTRING( @vcString, Counter, 1 )
        FROM    dba_Sequence
        WHERE   Counter <= DATALENGTH( @vcString )

    RETURN
END

Dennis actually calls his table Sequence and stores it in the dba database. I use a table named dba_Sequence and create it in each database that needs it so that the database can be moved around to a server that doesn't have the dba database already created. In either case you get the same functionality.

Sequence tables are very useful and I continue to use them on occasion. However, you can achieve the same functionality with a UDF and get a bit more flexibility in the process. This week's UDF, udf_Num_SequenceTAB, returns a sequence table with exactly the entries needed. This relieves the user of worrying two issues:

• Does the number of entries in the sequence table go high enough for every possible query?
• What code has to be added to the WHERE clause to limit the rows used from the sequence table.

Here's the CREATE Function script for udf_Num_SequenceTAB:

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE FUNCTION dbo.udf_Num_SequenceTAB(

    @Start int = 1     -- Start number of the sequence
  , @End   int = 100   -- End number of the sequence
  , @Increment int = 1 -- Increment for each Row
)   RETURNS @Sequence table (Number int)
    WITH SCHEMABINDING
/* 
* Returns a table of integers from @Start to up to and including
* @End.  Each row is incremented by @Increment. 
* The resultset is usually used to join with the results of a 
* GROUP BY query when you want to be sure to produce one row of
* output for each number in the sequence.
*
* Example:
SELECT RowNumber FROM udf_Num_SequenceTAB(1, 10, default)
*
* © Copyright 2003 Andrew Novick http://www.NovickSoftware.com
* You may use this function in any of your SQL Server databases
* including databases that you sell, so long as they contain 
* other unrelated database objects. You may not publish this 
* UDF either in print or electronically.
* Published in T-SQL UDF of the Week Newsletter Vol 2 #35 8/17/04
http://www.NovickSoftware.com/UDFofWeek/UDFofWeek.htm
****************************************************************/
AS BEGIN 

	DECLARE @nCount INT -- Loop counter

	SET @nCount = @Start

	WHILE @nCount <= @End BEGIN
		INSERT INTO @Sequence Values (@nCount)
		SET @nCount = @nCount + @Increment
	END 

	RETURN

END 
GO

grant select on dbo.udf_Num_SequenceTAB to Public
go

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

As a multistatement table valued UDF, udf_Num_SequenceTAB can be used in the FROM clause of any query. Here's an example:

SELECT * FROM udf_Num_SequenceTAB(37, 46, 3)
GO

(Results)
Number      
----------- 
37
40
43
46

Because udf_Num_SequenceTAB can start and end at any number, queries can be run using any sequence values even if they start and stop at number that aren't in the sequence table. Also, even if the query includes only every 2nd or 3rd number, there's no coding required in the WHERE clause.

Now let's take a look at the function fnc_10_parse_string coded using the sequence function.

CREATE FUNCTION dbo.fnc_10_parse_string_2(

    @vcString varchar( 8000 )

) RETURNS @tablevalues TABLE( itemid int, item char( 1 )) 
AS BEGIN
 

    DECLARE @StrLen int
    SET @StrLen = DATALENGTH(@vcString)

    INSERT INTO @tablevalues
        SELECT  [Number],
                SUBSTRING( @vcString, [Number], 1 )
            FROM udf_Num_SequenceTAB(1, @STrLen, 1)

    RETURN
END

Notice that the DATALENGTH function has been used to return the size of the string but that it has to be moved out of the FROM clause because you can't use an expression when invoking a UDF in the from clause. Also notice that I've added the number 2 to the end of the name of the function to distinguish it from the original version and Dennis's alternative. If you run the first query with the modified function you get the same result set as in the original query:

SELECT * FROM
 dbo.fnc_10_parse_string_2('ABBBCCDEEADBBDADCCCEDCCCCECEC')
GO

Thanks to Sloan and Dennis for their contributions.

Do you have a great UDF that you'd like to share?  Or maybe you have a T-SQL problem that you think could be solved by a UDF but you don't know how? Send them to: UDF@NovickSoftware.com

and they might be published in this newsletter.  I try and respond to every request that I get. 

Thanks, 
Andrew Novick