June 15, 2011 at 11:57 pm
How do we find out the sparse columns in a table using a query?
June 16, 2011 at 12:58 am
Try this:
select * from sys.columns where is_sparse = 1 and OBJECT_NAME(object_id) = '<table>'
June 16, 2011 at 1:03 am
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?
June 16, 2011 at 3:05 am
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.
June 24, 2011 at 9:04 am
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?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply