Puzzle Solved

I have been really getting into RSS feeds lately and have subscribed to alot of ones that interest me. I personally have been using the Google Reader on the iGoogle homepage. It’s a neat way to keep tabs on my varied readings. One that caught my eye was a challenge by Pinal Dave. Pinal challenged his readers to solve a puzzle that would return the size of each index for a speficied table.

He started with a simple sp_spaceused [tableName] command that returned the index_size of the table and a selection from sys.indexes that returned a list of all of the indexes on said table. But it was up to his readers to figure out how to get the index_size of each index, so that the sum of all of them would add up to teh result from sp_spaceused. Read the article to follow along. My answer is the 7th down (mine is simpler and suprising different from the other answers). Pinal will post the solution with due credit on his blog. Hopefully, I did well, seeing that he has almost 11 million readers.

Here is a quick overview of the puzzle:

My answer was: DECLARE @objname nvarchar(776), @id int

SELECT @objname = 'TableToExamine', @id = object_id(@objname)

SELECT i.*, CASE WHEN ps.usedpages > ps.pages THEN (ps.usedpages - ps.pages) ELSE 0 END * 8 indexsize FROM sys.indexes i INNER JOIN ( SELECT OBJECT_ID, index_id, SUM (used_page_count) usedpages, SUM ( CASE WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count) ELSE lob_used_page_count + row_overflow_used_page_count END )pages FROM sys.dm_db_partition_stats WHERE object_id = @id GROUP BY object_id, index_id ) ps on i.index_id = ps.index_id WHERE i.object_id = @id

Well, that wasn’t my exact answer. I had a variable declaration that I really didn’t need, but hey, I did it in like 5 mins. So this version is a little cleaner. :)

ENJOY! And in the spirit of fun, See if you can do it better!