Tag: T-SQL

We published an article recently at SQL Server Central on Tally Tables in Fabric from John Miner. In it he showed how this can be efficient. A day after he published it, he sent me an addendum to note that GENERATE_SERIES was available in Fabric and that could be used.

I ran a few tests last week, but as I read the comments on John’s article, I realized that there were 3 ways of setting up these tally tables that I’ve used and thought I’d summarize them a bit in this post. There’s a fourth way, but I haven’t used it.

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

Method 1 Using System Tables

The first method, which I saw Jeff Moden use many years ago involves reading from system tables. The code typically looks like this:

SELECT ROW_NUMBER () OVER (ORDER BY
                              (SELECT NULL))
FROM
   sys.all_columns ac1
   CROSS JOIN sys.all_columns ac2;

Since this table has 12000+ rows in it, the cross join is 12k * 12 k, which is a lot. The row_number() function gives you sequential numbers in a list.

This code works, but I can never remember which table and it does read from disk (or memory) to get the values. I suspect it’s slightly slower in lots of code than the other methods, but perhaps not enough to go and refactor old code.

Method 2 Using CTEs

The method I’ve liked to use is with CTEs. I have a SQL Prompt snippet set up with tt to give me this code.

WITH myTally(n)
AS
(SELECT n = ROW_NUMBER() OVER (ORDER BY (SELECT null))
  FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) a(n)
   CROSS JOIN (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) b(n)
)
SELECT n
FROM myTally

This CTE has two 10 row “tables” that give me 100 rows (10*10). If I needed more, I can copy/paste the “cross join” line and change the b to a c and I’ve got 1000 rows. Repeat that until you don’t need more rows.

This is simple code, it’s in a snippet for me, and easy to expand. I’m not reading from anything and I can set the size as small or large as needed.

Method 3 Using GENERATE_SERIES

The last method is just a select from the GENERATE_SERIES() function. I can give it the number of rows, so this gives me 100 rows.

SELECT value FROM GENERATE_SERIES(1, 100, 1) AS gs;

I haven’t used this because I’m often on SQL 2019, not 2022, thought that likely should change.

In any case, this works well for getting a large number of rows, and has the advantage of me being able to set a starting point, so if 1 isn’t appropriate, I can start at 7 or 29 or anything else. I can also set a step to skip some numbers.

I like that this is less code and built in as a function, but only in SQL Server 2022+

Summary

I haven’t given any reason to pick any of these over the other. The post from last week shows that GENERATE_SERIES seems to be slightly faster, but that wasn’t really a comprehensive performance test. I like both method 2 and 3, and in modern version I’d lean towards using method 3 as it’s built in and less code.

I’ll do a performance test elsewhere and write a bit about GENERATE_SERIES and the options available.

SQL New Blogger

This post took me about ten minutes to write, as the code is simple and the longest part was really copy/pasting links and code from SSMS or articles. The rest was quick and easy.

This is a short post that can showcase your learning, and your thinking about different methods. I’ve given a few examples of that above.

I had someone reach out about generate_series() recently, saying they hadn’t realized this was a new feature in SQL Server 2022. They were wondering if it was better than using a tally table.

I didn’t want to do an exhaustive test, but I thought I’d take a minute and try a couple simple things just to see.

A First Test

The first thing was to just generate a million numbers. Rather than just get the numbers. I decided to use a quick DATEADD() to create a list of calendar dates. Here’s the code:

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
-- Create a Tally table with 1 million numbers
WITH Tally (n)
AS ( SELECT TOP (1000000)
             ROW_NUMBER () OVER (ORDER BY
                                   (SELECT NULL)) AS Number
      FROM
        master.dbo.spt_values a
        CROSS JOIN master.dbo.spt_values b)
SELECT DATEADD(DAY, n, GETDATE())
  FROM tally

SELECT DATEADD( DAY, value, GETDATE()) FROM GENERATE_SERIES(1, 1000000, 1)

Since this does read from tables, I ran it twice. The first time, the tally table took 243ms, so I re-ran it and saw this drop to 172ms. The results were consistent for Generate_series, which was 110ms.

A Second Test

I grabbed Jeff Moden’s code for random numbers and adjusted a second query to use GENERATE_SERIES(). The code is below.

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

--===== Declare some obviously named variables
DECLARE @NumberOfRows INT,
@StartValue   INT,
@EndValue     INT,
@Range        INT
;
--===== Preset the variables to known values
SELECT @NumberOfRows = 1000000,
@StartValue   = 400,
@EndValue     = 500,
@Range        = @EndValue - @StartValue + 1
;
--===== Conditionally drop the test table to make reruns easier in SSMS
IF OBJECT_ID('tempdb..#SomeTestTable','U') IS NOT NULL
DROP TABLE #SomeTestTable
;
--===== Create the test table with "random constrained" integers and floats
-- within the parameters identified in the variables above.
SELECT TOP (@NumberOfRows)
SomeRandomInteger =  ABS(CHECKSUM(NEWID())) % @Range + @StartValue,
SomeRandomFloat   = RAND(CHECKSUM(NEWID())) * @Range + @StartValue
INTO #SomeTestTable
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2

SELECT TOP (@NumberOfRows)
SomeRandomInteger =  ABS(CHECKSUM(NEWID())) % @Range + @StartValue,
SomeRandomFloat   = RAND(CHECKSUM(NEWID())) * @Range + @StartValue
INTO #SomeTestTable2
FROM GENERATE_SERIES(1, @NumberOfRows, 1)

When I ran this, I see these results:

Execution times are close. Slightly faster with GENERATE_SERIES(), but fairly consistent across runs. In running this 10 times, there were 3 runs where the tally table was faster, and once just under 300ms. A few times the time was the same, but always within 15-16ms. Not sure that means much.

This isn’t a really exhaustive test, and don’t take this as a recommendation either way for your code. Test how they both work in your system, and certainly think about the impact of storing a tally table vs. generating one on the fly vs the GENERATE_SERIES().

However, it seems that GENERATE_SERIES() is worth looking at if you are on SQL Server 2022 or later.

One interesting concept in SQL Server is Deferred Name Resolution. This is something many developers struggle with understanding how this works and where it works.

In the Microsoft docs, there is a specific section in the CREATE TRIGGER docs that covers Deferred Name Resolution. This is a short section, and I’ve reproduced it below:

SQL Server allows for Transact-SQL stored procedures, triggers, and batches to refer to tables that don’t exist at compile time. This ability is called deferred name resolution.

I don’t know how batches are compiled, but procs and triggers are compiled for sure. What this statement says is that I can reference a table in a proc or trigger that doesn’t exist. When I create the trigger or proc, the reference is deferred at compile time and resolved at runtime.

Let’s see how this works. I’ll run this code in SSMS, all at once. In this code, I create a database in one batch, switch to it in the next, and then create a proc in the third.

CREATE DATABASE DNRTest

GO

USE DNRTest

go

CREATE PROCEDURE dnrproc

AS

SELECT * FROM sdfsfdsfs

GO

If we look in SSMS, this works.

In my database, I have only one object, the stored procedure.

However, if I execute this, it fails.

The table doesn’t exist, so the proc fails. However, I can not create the table and re-run the proc, and it works.

This is handy as I might create procs that reference temp tables, which don’t exist until they’re created. Often this happens in the proc, but if we were to try and resolve the reference at compile time, it would fail.

This also works in triggers as I might often script a table and triggers that reference a second table. When I run that script, I don’t want to trigger creation to fail, so I defer the name resolution until the trigger fires. This way my scripts can be organized logically.

This also works with functions, as seen below.