Make huge space savings by using SPARSE columns

I’ve blogged before about Getting more than 1024 columns on a table , this is done by using sparse columns. Whilst this is potentially useful for people with insane table designs, sparse columns aren’t just for this. My experience over the past few years has shown that sparse columns are useful for almost all databases when you have columns that are largely null i.e. sparse.

A recent client was able to reduce the size of the table by 60% by changing columns to sparse. The way this is achieved is by the storage engine not using any space even for fixed size columns when a column is null, although on the flip side a little more space is used when a value is stored. The trick is to see what ratio you have or null values to non null values. There is a break even point where using sparse doesn’t pay off but in my experience every database has some tables where there are ints or datetime columns that are largely null.

So have a look at your tables and where you see lots of nulls consider using sparse columns

To change a column to sparse just issue an alter table statement.

ALTER TABLE <table> ALTER COLUMN <column> <datatype> SPARSE

However be careful as this causes considerable data movement and it may be quicker to cerate a new table with the correct column definitions and then do an insert into statement. Once copied you can rename and add the relevant constraints. This is likely to be quicker as the copy will be a minimally logged operation (if recovery mode is simple or bulk logged).

1 more note this huge space saving only applies to fixed width data types, such as int, bigint, datetime, numeric, decimal, float.

Published Friday, December 17, 2010 2:08 AM by simonsabin

Comments

# Twitter Trackbacks for Make huge space savings by using SPARSE columns - Simons SQL Blog [sqlblogcasts.com] on Topsy.com

Pingback from  Twitter Trackbacks for                 Make huge space savings by using SPARSE columns - Simons SQL Blog         [sqlblogcasts.com]        on Topsy.com

Friday, December 17, 2010 3:29 AM by SimonS Blog on SQL Server Stuff

# Make huge space savings by using SPARSE columns

I’ve blogged before about Getting more than 1024 columns on a table , this is done by using sparse columns