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