Create a Numbers Table with Buckets Using a Recursive CTE

I needed a numbers table from –400 to 5000. I needed every number to be in a specific bucket for a report. For instance number 55 would be in the bucket "31 – 60".

This code elegantly creates the data for the number ranges (without listing each of them) and for those buckets.


with myNumberCounts as
(
select CAST(-400 as int) NumValue
union all
select NumValue + 1
from mynumberCounts
where numvalue + 1 <= 5000
)
select NumValue
, case
when NumValue <= 30 then '0 - 30'
when NumValue >30 and NumValue <= 60 then '31 - 60'
when NumValue > 60 and NumValue <= 90 then '61 - 90'
when NumValue > 90 and NumValue <= 120 then '91 - 120'
when NumValue > 120 and NumValue <= 150 then '121 - 150'
when NumValue > 150 and NumValue <= 180 then '151 - 180'
when NumValue > 180 and NumValue <= 365 then '181 - 365'
else '365+'
end AS DayBucket
from myNumberCounts
option (maxRecursion 0)

San Diego Code Camp Slides

Here is the link for the slides for the T-SQL Tips and Tricks presentation at code camp:

http://www.slideshare.net/ellisike/quick-easy-sql-tips

A Bug Using INTERSECT and ORDER BY

I was teaching a TSQL course to Total Jobs Group in London, UK, and we stumbled on the following bug. This script uses the Northwind sample database on SQL Server 2008 R2.

use northwind

select contactName, Phone
from customers
intersect
select lastname + ', ' + firstname
, homephone
from employees
order by contactName

If you execute this statement with INTERSECT and ORDER BY, you get the following error:

Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.

If you remove the ORDER BY, it works just fine. This is definitely a bug and I found that it was reported on Connect. I'll test this out to see if they've fixed this in Denali.

Find All The Columns in Indexes with SQL Azure

In SQL Server on-premise, this query is often used to find all the columns that are currently participating in an index:

SELECT OBJECT_NAME(sysindexkeys.id) Table_Name,
            syscolumns.name Column_Name,
            sysindexes.name Index_Name,
    CASE WHEN
                INDEXKEY_PROPERTY(sysindexes.id, sysindexes.indid, sysindexkeys.keyno, 'IsDescending')=0
                THEN 'Ascending'
    ELSE    'Descending'
    END SORT_ORDER
    FROM sysindexkeys
            INNER JOIN syscolumns
            ON sysindexkeys.colid=syscolumns.colid
        AND sysindexkeys.id=syscolumns.id
            INNER JOIN sysindexes
            ON sysindexkeys.indid=sysindexes.indid
        AND sysindexkeys.id=sysindexes.id
            


In SQL Azure, we don't have sysindexes or sysindexkeys. Instead, we use sys.indexes and sys.index_columms. It looks like this:

SELECT OBJECT_NAME(sysindexkeys.OBJECT_ID) Table_Name,
            syscolumns.name Column_Name,
            sysindexes.name Index_Name,
    CASE WHEN is_descending_key = '0' THEN 'Ascending'
    ELSE 'Descending'
    END AS SORT_ORDER,
            type_desc AS indexType
    FROM sys.index_columns sysindexkeys
            INNER JOIN syscolumns
            ON sysindexkeys.index_column_id=syscolumns.colid
        AND sysindexkeys.OBJECT_ID=syscolumns.id
            INNER JOIN sys.indexes sysindexes
            ON sysindexkeys.index_id=sysindexes.index_id
        AND sysindexkeys.OBJECT_ID=sysindexes.OBJECT_ID
            

Father of the Year Candidate at Desert Code Camp 2011

As I was walking around the campus at Desert Code Camp last month, a man stopped me. This guy had tattoos riding up his arm, a flat-brim hat, baggy jeans, and looked like he just walked off the construction site...not your typical code camp attendee. Trailing him was a skinny 12-year-old boy wearing a pacman t-shirt.

"Excuse me, I have no idea what my son is saying. Can you please translate?"

His son wanted to go to a specific class. I found one close enough on the schedule and pointed them on their way. Later, as I was running around after my own sons, I saw the man walk out of the class his son was in. He found a chair by the door, sat down, and waited. As I walked around code camp that day, I saw the same sight over and over again. This guy was either walking his son to the next class, or waiting outside the door.

I wonder what he was thinking about with all that free time that day. Perhaps, "why doesn't my boy like to throw the football?" Or, "why can't we make furniture together?" I wonder how many super smart kids are born to parents that are not as generous and thoughtful as that man. I wonder how many brilliant minds the world has lost out on because of bad luck or circumstance.

On the flip side, maybe one of my sons was meant to be an NFL Quarterback, but we'll never know. They're stuck in code camp classes.

Outlook 2011 for Mac (OsX) Won't Open New Email Window and Reply All Doesn't Work

Today, none of my email buttons would work in Outlook. After some digging, I found this solution:

http://jimmyblake.com/2010/11/09/microsoft-outlook-2011-not-opening-e-mail-compose-window/

I reinstalled Safari and it finally fixed it.

Database Design Fundamentals - Louis Davidson's SQL Pass 2010 Talk

I've been watching old SQL Pass talks from last year's conference. Today I watched Louis Davidson's talk, found here:

http://sqlpass.eventpoint.com/topic/details/AD270S


He is an interesting speaker, who talks funny because he's from Tennessee. My favorite is the pronounciation of varchar(max) as vare care (max). That rhymes, by-the-way.

His presentation covers database normalization from first normal form through Boyce/Codd normal form. He also talks about de-normalization and offers tips and techniques for good design. Forty-nine minutes into his presentation, he has a brilliant slide:



He says that if you find a lot of parsing and function calls in queries, particularly on the same column over and over again, you have strong evidence of a design that needs to be revisited. I totally agree. As soon as he said it, it hit me like a ton of bricks. If you see parsing functions over and over again, you have opened yourself up to bad data and inconsistant results. You are overly-relying on report authors and developers to get the parsing formulas right every time they touch that data. That won't happen. The database needs to be refactored so that data is easy to retrieve and store.

I've thought about this specifically, but I've never considered it as a good general rule.

Here are links for Louis Davidson:

His Website
His Blog
His Latest Book on Database Design for SQL Server 2008
His Twitter: @SqlDr