A Seasonal Query

UPDATED 29 Dec 2008: Back to the tried-and-true source code formatting method, per Kalen’s comment and my response. 

UPDATED 24 Dec 2008: I got this from a couple of different sources, so I didn’t realize when I drafted this post that Dirk wrote this query.  Yesterday.  Dirk is obviously a genius with a huge spirit. 

Courtesy of SQL Ranger Dirk Gubbels, here’s a query for the season.

Run this query in the master database on a SQL Server 2005 or SQL Server 2008 instance.  Text output works best, but grid output is okay.  It’s guaranteed safe..

with FirstTable as (select top 14 row_number() over (order by name) therow from master.sys.objects)
, SecondTable as (select replicate(char(32),15) theLine)
, ThirdTable as (select replicate(char(124),3) theOtherLine)
, ForthTable as (
select  (
select left(db_name(4),1) ) + (
select substring(db_name(2),2,1) ) + (
select replicate(substring(db_name(1),6,1),2) ) + (
select replace(schema_name(4),‘s’,) ) + (
select char(max_length * 2)  from master.sys.types where system_type_id = 36) + (
select top 1 substring(wait_type,10,2) from master.sys.dm_os_wait_stats where wait_type like ‘PageIo%’ ) + (
select substring(@@version,4,1) ) + (
select substring(object_name(55),4,2) ) + (
select convert(char(1),(reverse(convert(char(7),name)))) from sys.configurations where configuration_id = 124 ) + (
select left(db_name(1),3) ) theEnd
select case  therow 
      when 11 then stuff( theLine,(datalength(theLine)/2) 1,3,TheOtherLine) 
      when 13 then upper(theEnd )
      else stuff( theLine,(datalength(theLine)/2) (theRow/2),therow,replicate(char(42),therow)) end ‘ ‘
from firstTable
cross join SecondTable
cross join ThirdTable
cross join ForthTable
where therow%2!=0

Best wishes for a blessed and happy holiday!


Comments (12)

  1. Anonymous says:

    This post is a cousin of sorts to last December’s post regarding the OBJECT_ID function. I learned this

  2. Anonymous says:

    If this keeps up, I might need to define a tag for this stuff.. Our old friend Adam Machanic , perhaps

  3. Anonymous says:

    Here’s another seasonal query for you : select datediff ( dd , ’10/20/2008′ , ‘3/3/2009’ ) ———–

  4. Anonymous says:

    On the heels of Christmas Eve’s seasonal query from Dirk Gubbels comes this gem from Madhivanan (with

  5. Anonymous says:

    Stephen Forte has Dirk’s code as well,

  6. Anonymous says:

    I was catching up on my blog reading and came across a very nice post on Ward Pond’s blog ; he gives

  7. Anonymous says:

    I was catching up on my blog reading and came across a very nice post on Ward Pond’s blog ; he gives

  8. Anonymous says:

    @Mike: I agree with you completely.  It’s a wonderful use of a lot of nifty SQL Server 2005+ features.  I’m a big CTE fan, so I’d add that to your list..

    And any time you see a modulus operator, you KNOW something fun is going on.

    Thanks for your note, Mike!

  9. Anonymous says:

    The law of unintended consequences is an amazing thing. When I posted Dirk Gubbels’ holiday greeting

  10. Hi Ward

    Thanks for the great query!

    I found when I copied and pasted into SSMS, there were no line breaks; it came across all as one long line, so it was very difficult to get any kind of idea as to what the query was doing.

    It was particularly difficult when I got a syntax error message…trying to debug one long line of code was a challenge.

    It turned out that the breaks before the word ‘cross’ were being swallowed, so the last part of the query was this:

    from firstTablecross join SecondTablecross join ThirdTablecross join ForthTablewhere therow%2!=0

    I think I’ll blog this with my adjustments to the code. I hope that’s ok.

    Happy New Year!


  11. Mike Walsh says:

    Interesting, good use of features (over, various functions, a dmv and a system table) as well :)