New T-SQL 2012 features (in a bottle of beer)

SQL Server 2012 comes with some really good new features in the T-SQL language. I like the idea of explaining things through examples, so I made a T-SQL 2012 implementation of the “99 bottles of beer” song (and also submitted it to the 99 bottles of beer website).

The new T-SQL 2012 features I’m using are:

Sequence

The sequence is a number generator that is similar to an IDENTITY column, but not tied to a particular table. The main usage I see is scenarios where you want an id number that is guaranteed unique between tables. You can let multiple tables use the same sequence as default value for a column.

Format

The new T-SQL format allows you to customize the way numbers, dates, etc are converted to strings. Basically it gives you the same functionality as .NET developers have had in their format function. You specify a format, which could be something like “X03″ which then would mean hexadecimal representation with leading zeroes and 3 characters.

Iif

This is a simplified case statement, that evaluates a condition and returns either one of two arguments depending on whether the condition evaluates to true or false. It is just a shorter and more readable way of writing the case statement.

Window functions

This is in my opinion the most powerful new T-SQL feature. It would deserve a full book to explain in depth, but in a nutshell it allows us to work with a subset of rows without having to do self joins or correlated subqueries. For instance we can access previous and next row through functions lag and lead. We can also very easily calculate running totals or moving averages.

There are more T-SQL 2012 news, like the new date functions, parse, try_parse and offset that I would have liked to included if I had found any use for them in the 99 bottles of beer code. Let me know if you come up with ideas!

Below is the source code.

---------------------------------------------------------------
-- 99 BOTTLES OF BEER
-- SQL Server 2012 version. Using Sequence, Format, Iif and Window functions.
-- 
-- (C) Johan Åhlén, 2012
-- Blog: http://www.joinsights.com
-- This is BEERWARE. If you like it - send the author a beer!  
---------------------------------------------------------------

SET NOCOUNT ON

CREATE SEQUENCE dbo.Seq AS SMALLINT
START WITH 99
INCREMENT BY -1
MINVALUE 0
NO CYCLE;

-- Create XML document with 100 elements
DECLARE @xml AS XML = '' + REPLICATE('', 100) + '';

-- Create a temporary table with verse numbers from sequence
-- and use XML document to generate rows
SELECT 
  NEXT VALUE FOR dbo.Seq AS VerseNumber 
  INTO #Verses
  FROM @xml.nodes('//v') X(c)

-- Translate to text parts
SELECT
  VerseNumber,
  IIF(VerseNumber > 0, FORMAT(Versenumber, 'd'), 'No more') +
  IIF(VerseNumber = 1, ' bottle', ' bottles') + ' of beer' AS NumberOfBeers,
  IIF(VerseNumber = 0, 'Go to the store and buy some more', 'Take one down and pass it around') AS WhatToDo
  INTO #TextParts
  FROM #Verses

SELECT
  NumberOfBeers + ' on the wall, ' + 
  LOWER(NumberOfBeers) + '. ' +
  WhatToDo + ', ' + 
  LOWER(ISNULL(LEAD(NumberOfBeers) OVER (ORDER BY VerseNumber DESC), 
               FIRST_VALUE(NumberOfBeers) OVER (ORDER BY VerseNumber DESC))) + 
  ' on the wall.' AS Lyrics
FROM #TextParts
ORDER BY VerseNumber DESC

DROP SEQUENCE dbo.Seq
DROP TABLE #Verses
DROP TABLE #TextParts