Get In Sequence

oday I wanted to deomonstrate some cool things you can do with a Sequence (or sometimes called Tally) table. I put them in just about every database I create. A tally table simply has one numeric column with a sequence of numbers, say 1 to 1000. Then you can use that table to manipulate other objects in a sql query without having to revert to using a cursor.

Here is a simple example of listing the characters in a string.

DECLARE @STRING VARCHAR(128) = 'This Is Really  Cool'

select  Number, SUBSTRING(@String, Number, 1) As Character
From sandbox..sequence
WHERE   Number BETWEEN 1 AND LEN(@String)

returns

I now turned the characters from a string into a table with two columns. The number column represents the position within the string being parsed. The second column contains the character found in @String at position represented by Number from the Sequence table.

Ok, so that is kinda cool. But I can’t see myself using that very often. Do you have anything more? Well, here is another example.

Here is an interesting way to use SQL to calculate a number to the power of n. (Yes, I know there is already a function POWER built into sql server).

Here is 2 to the power of 10 just hard coded

select 2 * 2 * 2 * 2 * 2 * 2 * 2 * 2 * 2 * 2

Result = 1024

Using a tally table you can do the same thing with a query using variables

DECLARE @Base FLOAT = 2
DECLARE @Result FLOAT = @Base
DECLARE @Power INT = 10

SELECT @Result = @Result * @Base
FROM Sequence
WHERE Number BETWEEN 2 AND @Power

SELECT @Result

Result = 1024

Again, that’s kinda cool. But I don’t think I would ever use that in any production code. Do you have an example that I can use? Well, here is an example I have actually used in production. It locates the last instance of a specific characterstring in a larger character string.

CREATE FUNCTION dbo.LastPosition(@String VARCHAR(8000), @SearchString VARCHAR(128))
RETURNS INT
AS
BEGIN
    DECLARE @Result INT =  0

    ;WITH Positions
    AS
    (
        SELECT  Number
        FROM    Sequence
        WHERE   SUBSTRING(@String, Number, LEN(@SearchString)) = @SearchString
    )
    SELECT @Result = MAX(Number) from Positions
    RETURN @Result
END

SELECT dbo.LastPosition('ABCDEFGHIJKLMNOPABCDEFGHIJKLMNOPABCDEFGHIJKLMNOP', 'DEF')

Result = 36

So, I don’t have examples you may want to use in your code tomorrow. However, what you can learn from the use of a sequence/tally table is that it can, in many cases, replace a cursor or while loop that is based on simply incrementing a variable and then doing the same work over again. If you can put it into a single query, it may often be a more optimized query.

Cheers,

Ben