Sparse Columns

Question

Post reply

Sparse Columns

Latheesh NK

SSCertifiable

Points: 6559
More actions
June 15, 2011 at 11:57 pm

#252020

How do we find out the sparse columns in a table using a query?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Suresh B. SSC-Insane Points: 22986 More actions · Answer 1

Try this:

select * from sys.columns where is_sparse = 1 and OBJECT_NAME(object_id) = '<table>'

Latheesh NK SSCertifiable Points: 6559 More actions · Answer 2

Thanks, It worked out well!!!

How can we validate the space saved using SPARSE on a table?

I made some columns as SPARSE columns. However am not able to see any difference in size when chacking with sp_spaceused. Any thoughts Where I went wrong?

Oliiii SSCertifiable Points: 5328 More actions · Answer 3

Depending on the number of rows and the number of sparse column in your table you might not see any differences in size.

You can use this to see all the details of your table:Select * From sys.dm_db_index_physical_stats(db_id(),object_id('MyBigTablePK'), NULL,NULL,'SAMPLED')

On a table with 2mil records 3 regular columns (no nulls) and 10 sparse column vs 10 non sparse column i get the following numbers (after rebuilding indexes):

Original size: 7535 pages (99.8% page space used)

Original +10 sparse column with 1% non null: 7751 pages (99.7% page space used)

Original +10 non-sparse columns with 1% non null: 7928 pages (99.8% page space used)

As you see you need a lot of records and sparse columns to start seeing a difference.

If you don't have enough rows or sparse columns, the size difference will be lost in the % page space used.

Shaira SSCrazy Points: 2432 More actions · Answer 4

Question ... I have a table that has been in use for about 6 months. Currently a couple of the columns are below the NULL percentage that MS recommends. If I make these columns SPARSE to take advantage of the optimized storage, are there issues if I have to 'remove' the SPARSE later on if the trend changes and these columns start to become populated with more data?