SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Quick Scalar Tables–#SQLNewBlogger

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

               SELECT   2014
               SELECT   2013
               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;


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.

Filed under: Blog Tagged: SQLNewBlogger, syndicated, T-SQL

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest


Leave a comment on the original post [voiceofthedba.wordpress.com, opens in a new window]

Loading comments...