Tag: T-SQL

  • A Couple Quick GENERATE_SERIES Tests

    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.

    2025-02_0343

    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:

    2025-02_0344

    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.

  • What is Deferred Name Resolution?

    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.

    2025-02_0326

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

    2025-02_0327

    However, if I execute this, it fails.

    2025-02_0328

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

    2025-02_0329

    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.

    2025-02_0330

  • Declaring a Complex PK in a CREATE TABLE: #SQLNewBlogger

    Recently I was talking with someone who had not named any of the primary keys (PKs) in their database. They used system generated names and when they ran comparisons, they got all sorts of drops and creates they didn’t expect. This post shows how easy it is to declare PKs with names, even with complexity.

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

    The Scenario

    I’m going to use a schema that I have for baseball data. It illustrates the point well, I think.

    For my friend, imagine a table like this one:

    CREATE TABLE [dbo].[salaries](
         [yearID] [int] NOT NULL,
         [teamID] [varchar](3) NOT NULL,
         [lgID] [varchar](2) NOT NULL,
         [playerID] [varchar](9) NOT NULL,
         [salary] [int] NULL,
    PRIMARY KEY CLUSTERED 
    (
         [yearID] ASC,
         [teamID] ASC,
         [lgID] ASC,
         [playerID] ASC
    )
    )

    If I create this table, the system will decide what the PK is. In fact, after I run this, I can see in sys.objects that the name is some random code.

    2025-01_0258

    What’s worse is that if this were an FK and I were dropping these in some script, I’d have issues in other systems. This name is different on each system.

    The better solution is to make a simple change. Before the Primary Key keyword, I can add Constraint and a name, like this:

    CREATE TABLE [dbo].[salaries](
         [yearID] [int] NOT NULL,
         [teamID] [varchar](3) NOT NULL,
         [lgID] [varchar](2) NOT NULL,
         [playerID] [varchar](9) NOT NULL,
         [salary] [int] NULL,
    CONSTRAINT salariesPK PRIMARY KEY CLUSTERED 
    (
         [yearID] ASC,
         [teamID] ASC,
         [lgID] ASC,
         [playerID] ASC
    )
    )

    If I run this, then I have a better named PK.

    2025-01_0259

    Be explicit in your work. It makes for better code and easier, repeatable, reliable deployments.

    SQL New Blogger

    This is a simple thing, a code smell, but one that wastes DBA and developer time. This post shows a simple thing you can do to have better code. This took me about 10 minutes and you could do something similar.

    Write this and maybe someone asks you how to do this in an interview.

  • The Lesser Used Functions

    Recently, I reviewed an article that examined the bitwise functions that were added to T-SQL in SQL Server 2022. As I was looking over the article, I started to wonder if anyone was using these in production code. I used to do bitwise work early in my programming career, when memory and space were tight. However, it always felt like I was hiding some information that a subsequent developer (or my future self), might easily miss.

    I looked through some other changes to the T-SQL language in the last few versions and made a list. This week I wonder if any of you use these functions in production code?

    There are some interesting changes in here, and I can see the use for these functions, but I suspect these are specialized functions built for specific situations (or customers). I don’t expect many people to use them outside of those situations, but maybe I’m wrong. Perhaps some of you like doing bitwise operations, you like the logical CHOOSE/IIF, or maybe you can stomach approximate calculations.

    I’m glad that the T-SQL language continues to grow. I would like to see more changes that ease the development of database code, though I would like Microsoft to ensure these new functions perform well. Some of the changes added in the past haven’t done so, especially when a dotNet function is wrapped with T-SQL. Performance matters and many of these functions will be called in queries that need to compete a result with thousands of rows.

    Let us know today if you’ve found these functions useful in your work, or if there are changes made in the past that you would like to see improved.

    Steve Jones

    Listen to the podcast at Libsyn, Spotify, or iTunes.

    Note, podcasts are only available for a limited time online.