Voice of the DBA

Tag: T-SQL

  • CHOOSE’ing a Beer: #SQLNewBlogger

    We recently published an article on CHOOSE at SQL Server Central. I thought it was a good intro, but as someone noted in the comments, how do you use CHOOSE? Do you have to hard code choices?

    This post shows you don’t.

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

    A Scenario

    I have a table that contains some data. In this case, about beer. I like beer, and this was a fun little demo. I’m not recreating the DDL because, well, you might like different beers.

    2025-03_0145

    In any case, this is simple to set up.

    If I wanted to choose some data from this table based on an index, I could do something like this. This code populates the first index in choose with beers and the second with brewers. CHOOSE is 1-based indexing.

    DECLARE @i INT = 1;
SELECT
   CHOOSE (@i, beername, brewer)
FROM dbo.Beer AS b2;

    This returns me the beers.

    2025-03_0146

    If I changed the value to 2, I get brewers. I show both below.

    2025-03_0147

    How would I use this? Maybe a user is asking to edit either a home or shipping address. I can index these by returning the column data as index 1 or 2, and linking the user suggestion to the index. They choose home, we pass in 1. If we qualify the query with a WHERE clause to one customer, they get just their data to edit.

    I could even do something silly, like getting values from different places. For example, here I’ll use string_split on a value.

    DECLARE @i INT = 2;
DECLARE @s VARCHAR(20) = 'Vodka,Tequila,Bourbon'

; WITH a (value)
AS
(SELECT a.value FROM STRING_SPLIT(@s, ',', 1) AS a
  WHERE a.ordinal = 1
),
  b (value)
AS
(SELECT a.value FROM STRING_SPLIT(@s, ',', 1) AS a
  WHERE a.ordinal = 2
),
  c (value)
AS
(SELECT a.value FROM STRING_SPLIT(@s, ',', 1) AS a
  WHERE a.ordinal = 3
) 
SELECT
   CHOOSE (@i, a.value, b.value, c.value)
  FROM a, b, c

    This is silly, but it does return an acceptable answer.

    2025-03_0148

    I don’t know that there are many places that I’d use CHOOSE, but as I play with it, I can see that it could be a handy tool at times with a little creativity.

    SQL New Blogger

    This post took me about 15 minutes to write after I saw a comment. I set up a scenario and posted a reply, then took that code to structure this post. The STRING_SPLIT piece was the longest, as I had to futz with code, but I show some use of a new feature and how I might incorporate this into an application.

    You could write your own creative blog on this, probably in 30 minutes or less. I bet you’d get asked about this in an interview as it’s kind of funny.

  • DATEADD Truncates the Number Parameter: #SQLNewBlogger

    This was an interesting thing I saw in a Question of the Day submission. I hadn’t thought about the issue, but apparently DATEADD truncates values rather than rounding them. I’m not sure why that is the case, but it is.

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

    The Scenario

    Imagine that I have someone enter a value for the number of hours to include in a report. I enter 5 and the report divides this in half to go back 2.5 hours and forward 2.5 hours. I run this code at the top of my code block:

    DECLARE @hours NUMERIC(4, 2) = 5;
DECLARE @start DATETIME, @end datetime
SET @start = DATEADD (hour, -@hours / 2, GETDATE ())
SET @end = DATEADD (hour, @hours / 2, GETDATE ())

    Now, what do you think are the resulting start and end times? I’d assume this works and the function sorts out how much of an hour is .5 or .4 or whatever.

    Here’s the interesting result. Look at the time interval in the end result.

    2025-03_0091

    It’s 4. I entered 5 hours, but I get 4 hours. I bet a lot of us would let this bug slip through as reading the datetimes we’d miss this wasn’t actually 5 hours.

    Apparently DATEADD actually truncates a non-integer value. The parameter notes that the 2nd parameter, the number to add to the date value, resolves to an integer. It also notes that DATEAD truncates, not rounds, values that have a decimal fraction.

    Those are two very important distinctions. That could result in calculations that are way off from what people expect if you are trying to include data in a query and you are trying to do parts of time. You might need to separately calculate all your different date/time parts.

    If you need to do fractional work with dates, you can’t use DATEADD.

    To me that seems lacy, but is it? Let me know.

    SQL New Blogger

    This is a short example of something that a person pointed out to me, and I never knew. I decided to make a quick test (the code above) and then write about this. I could have included other examples, or shown how this might mess up different situations in my code.

    You could do the same thing in 30 minutes or less and point out an interesting piece of knowledge that your future employers might find interesting. They might even want to interview someone that learns things like this.

  • Tally Table Alternatives: #SQLNewBlogger

    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.

  • 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.