Category: Blog

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.