Thursday, April 21, 2011

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:

INDEXKEY_PROPERTY(, sysindexes.indid, sysindexkeys.keyno, 'IsDescending')=0
THEN 'Ascending'
ELSE    'Descending'
FROM sysindexkeys
INNER JOIN syscolumns
ON sysindexkeys.colid=syscolumns.colid
INNER JOIN sysindexes
ON sysindexkeys.indid=sysindexes.indid

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,
CASE WHEN is_descending_key = '0' THEN 'Ascending'
ELSE 'Descending'
type_desc AS indexType
FROM sys.index_columns sysindexkeys
INNER JOIN syscolumns
ON sysindexkeys.index_column_id=syscolumns.colid
INNER JOIN sys.indexes sysindexes
ON sysindexkeys.index_id=sysindexes.index_id
AND sysindexkeys.OBJECT_ID=sysindexes.OBJECT_ID

Sunday, April 17, 2011

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.