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
Number | Character |
1 | T |
2 | h |
3 | i |
4 | s |
5 | |
6 | I |
7 | s |
8 | |
9 | R |
10 | e |
11 | a |
12 | l |
13 | l |
14 | y |
15 | |
16 | C |
17 | o |
18 | o |
19 | l |
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