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

This actually comes from Itzik Ben-Gan, by way of Kevin Feasel.  Kevin’s post is about CROSS APPLY, which is something I need to play with more because I hadn’t thought about this.

However, the one thing I did see as interesting is the use of VALUES to get a quick table. Here’s an example. Suppose I want to get a list of the last four years in a table for some reason, I can do this:

WITH    cteYears ( Yr )

          AS ( SELECT   2015

               UNION
               SELECT   2014
               UNION
               SELECT   2013
               UNION
               SELECT   2012
             )
    SELECT  Yr
    FROM    cteYears;

Or I could be more compact and do this:

WITH    cteYears ( Yr )
          AS ( SELECT  Yr = y
               FROM ( VALUES (2016), (2015), (2014), (2013) ) a(y)
             )
    SELECT  Yr
    FROM    cteYears;

Is one arguably easier? Certainly some might like the UNION, but it’s a lot more typing, and I think it can be easier to make some mistake in editing. The VALUES clause can easily simulate a table, and you can quickly see groups of rows as well.

WITH    cteYears ( Yr, champ )
          AS ( SELECT   y
                      , c
               FROM     ( VALUES ( 2016, 'Broncos')
                           , ( 2015, 'Patriots')
                           , ( 2014, 'Seahawks')
                           , ( 2013, 'Ravens') ) a ( y, c )
             )
    SELECT  Yr, Champ
    FROM    cteYears;

SQLNewBlogger

This is a quick item I noted, and one I’ve started to use more and more to build quick tables. It seems much easier for me to visualize and create the virtual table. I have started to use this to mock data, or run quick tests.