Sequence Table Ideas
Yesterday I introduced the concept of a Sequentially numbered table, and the value of having a persistent on available for any instance of SQL Server. Personally, I like to create one in my model database resulting in a Sequence table being included whenever I create a new database. This does not help me when I restore or attach a database from another source.
Today I’m providing a few examples using an assumed existing sequential table of numbers. The table contains the numbers 0 through 2147483647 which is every possible integer value for an INT data type in SQL Server.
CREATE TABLE Sequence
(
Number INT NOT NULL
,CONSTRAINT PK_Sequence PRIMARY KEY CLUSTERED (Number)
)
You can do so many different things with a Sequence table. Here are a few demonstrations. Some are more practical than others. My intention is to allow you to see ways the concept may be used.
First some string manipulation counting the number of words in a sentence. I create a function counting the words in a string as delimited by a space. Of course this could blow up if someone had more than one space character. The example assumes you have a Sequence table created as defined previously.
CREATE FUNCTION getWordCount_sfn (@Sentence VARCHAR(MAX))
RETURNS INT
AS
BEGIN
DECLARE @result int = 0
DECLARE @Delimiter CHAR(1) = ' '
IF LEN(@Sentence) > 0
BEGIN
SELECT @Result = COUNT(1)
FROM Sequence
WHERE SUBSTRING(@Sentence, number, 1) = @Delimiter
AND Number BETWEEN 1 AND LEN(@Sentence)
SET @result += 1
END
RETURN @result
END
/* Confirm it returns a count of 9 words */
declare @text varchar(4000) = 'The big black fox jumped over the lazy dog'
SELECT dbo.getWordCount_sfn(@text)
/* Confirm it returns zero word count for an empty string */
SET @Text = ''
SELECT dbo.getWordCount_sfn(@text)
/* Confirm it returns a count of 1 for a single word */
SET @Text = 'Hello'
SELECT dbo.getWordCount_sfn(@text)
Of course you could use the same concept to parse a comma separated value list.
Sequence tables are especially useful with Dates. Here is a simple query returning the last 31 dates ending today. The query assumes the existence of a Sequence table as defined above.
SELECT DATEADD(day, -number, CONVERT(DATE, GETDATE()))
FROM Sequence
WHERE Number BETWEEN 0 and 30
ORDER BY Number Desc
Here is an example using the POWER function in conjunction with a sequence table. I don’t think this specific example is useful generally. However, it demonstrates the fact that you can perform functions on incrementing values easily for different situations. For example, you could generate a list of ranges for categorizing data rather easily. This example assumes the existence of a Sequence table as defined above.
/* Retrieve the Int Equivalent of the binary value 0101010101010101 */
SELECT SUM(POWER(2, CONVERT(INT, Number)))
FROM Prime.dbo.Sequence
WHERE NUMBER BETWEEN 0 and 31
AND NUMBER % 2 = 0
Those are a few ideas to get your little grey cells working. Here are some other examples and links from John with a lot of other more practical uses of a Sequence table.
John Writes:
Here are some more useful examples of the use of numbers tables in SQL queries. Not all of this code is mine, but unfortunately I have lost the references to where I got them (just didn’t want to appear to be claiming credit for code that I did not write).
This first sample shows how to remove non-alphanumeric characters from a given string. This example also uses the number table concept without actually having a physical table saved on disk or in memory. The CTEs will create a 65536 number range to work with:
DECLARE @String VARCHAR(8000),
@AllowedPattern VARCHAR(100),
@Holder VARCHAR(8000);
SET @String ='!@#$% 123 ??abc##rd,,,';
SET @AllowedPattern ='0-9a-z' ;
SET @Holder ='' ;
;WITHt1 AS (SELECT1 N UNION ALLSELECT 1 N),
t2 AS (SELECT1 N FROM t1 x,t1 y),
t3 AS (SELECT1 N FROM t2 x,t2 y),
t4 AS (SELECT1 N FROM t3 x,t3 y),
tally AS (SELECTROW_NUMBER()OVER (ORDERBY (SELECTNULL)) ASnum
FROMt4 x, t4 y)--select COUNT(*) from tally --> 65536
SELECT @Holder =@Holder + SUBSTRING(@String,num,1)
FROM tally
WHERE num <=LEN(@String)
AND PATINDEX('['+@AllowedPattern+']',SUBSTRING(@String,num,1))= 1 ;
SELECT @Holder; -- should return 123abcrd
This code will actually create a tally table and populate it with 11 million rows (most will not need that many, but I have had to in the past while parse delimited TEXT data to convert to row based records):
USE <databaseof choice>;
--Supress the auto-display of rowcounts for appearance/speed
SET NOCOUNT ON;
--=============================================================================
-- Create and populate a Tally table
--=============================================================================
--===== Conditionally drop
IF OBJECT_ID('dbo.Tally')IS NOTNULL
BEGIN
DROPTABLE dbo.Tally;
END
--===== Create and populate the Tally table on the fly
SELECT TOP11000000 --equates to more than 30 years of dates
IDENTITY(INT,1,1)AS N
INTO dbo.Tally
FROM master.dbo.syscolumnssc1
,master.dbo.syscolumnssc2;
--===== Add a Primary Key to maximize performance
ALTER TABLEdbo.Tally
ADD CONSTRAINTPK_Tally_N
PRIMARY KEYCLUSTERED (N)WITH FILLFACTOR= 100;
--===== Let the public use it - OPTIONAL
GRANT SELECT,REFERENCES ONdbo.Tally TOPUBLIC;
SET NOCOUNT OFF;
-- SELECT COUNT(*) FROM ldr_maintenance.dbo.Tally
The above code is probably in these links from the SQLServerCentral.com site, where Jeff Modene has posted much of his work with this concept (I believe he is the originator of it). These links provide a wealth of information on the concepts and how to use a tally table in a variety of situations:
http://www.sqlservercentral.com/articles/Tally+Table/70735/
http://www.sqlservercentral.com/articles/Tally+Table/72993/
http://www.sqlservercentral.com/Forums/Topic543264-8-1.aspx
http://www.sqlservercentral.com/articles/T-SQL/62867/
http://www.sqlservercentral.com/articles/Tally+Table/70738/
http://www.sqlservercentral.com/articles/T-SQL/67899/
http://www.sqlservercentral.com/scripts/GO/70951/
I hope that information helps folks work toward moving away from RBAR (row by agonizing row). It has certainly helped
Well, I had never seen the acronym RBAR before…but it really resounds with me.
If you want to see more specific detailed examples I have quite a few on SSWUG availablle in articles and sessions from virutal conferences. I demonstrate Dates extensively as a single session, and also have a session on unit testing SQL Code built off date manipulation with a Sequence table.
Why not take a moment and share your most useful technique with a Sequence table. Leave a note below or drop me an Email at btaylor@sswug.org.
Cheers,
Ben
$$SWYNK$$
Featured Article(s)
SQL Server 2012 Temporary Tables Optimization Tips
Here you can find some helpful tips to use SQL Server 2012 temporary tables.
Featured White Paper(s)
Encryption Key Management Simplified
Managing encryption keys is a fundamental step to securing encrypted data, meeting compliance requirements, and preventing da… (read more)