I had my first exposure to one of the new SQL 2008 features that I frankly didn’t even know existed. The feature is the ability to turn on an option to make a column sparse. I won’t go into the details of why to use this as others have covered it pretty well here and here.
I was looking into some job failures this morning and one in particular stuck out at me. Essentially we have a new job setup that checks a few databases on a SQL 2008 server looking for tables and indexes that were created and not setup with page compression. The job then attempts to alter these tables or indexes with the page compression option turned on. The reasoning behind when to use compression is for another day and varies based on many factors. In this case we have determined that any new objects in these database should have page compression turned on.
The job is setup to run once a week on Sundays and this morning the server was red in my Quest Spotlight console so I started to dig into why. Here is the error from the Job with some names changed to protect the innocent.
Executed as user: XXXXXXX. Cannot alter table 'yourtablenamehere' because the table either contains sparse columns or a column set column which are incompatible with compression. [SQLSTATE 42000] (Error 11418). The step failed.
First off what a great informative error message, kudos Microsoft. I knew immediately why it failed but was not sure what sparse columns were. So I used my trusty search engine and found some info on sparse columns in SQL 2008. Within 2 minutes I knew what the issue was and that I needed to exclude tables with sparse columns in this process. Essentially leaving these tables without compression. Here is the query in the job that looks for uncompressed objects.
DISTINCT OBJECT_NAME(A.object_id) AS [ObjectName]
sys.partitions A with (nolock)
INNER JOIN sys.objects B
A.object_id = B.object_id
WHERE type = 'U'
AND data_compression = 0
ORDER BY 1;
To quickly fix this problem I added the following line of code to the where clause of this query that identifies objects that are uncompressed which filters out any objects with sparse columns.
AND A.object_id NOT IN ( select distinct object_id from sys.columns where is_sparse = 1
There is probably a much more elegant way of doing this and I’ll continue to look for that but for now the job runs, skips the one table with the Sparse columns in it and does what I want. This server is no longer red on my Spotlight console and all is good for now anyway.