Why & When should I use SPARSE COLUMN? (SQL SERVER 2008)

by

A sparse column doesn’t use 4x the amount of space to store a value, it uses a (fixed) 4 extra bytes per non-null value. (As you’ve already stated, a NULL takes 0 space.)

  • So a non-null value stored in a bit column would be 1 bit + 4 bytes = 4.125 bytes. But if 99% of these are NULL, it is still a net savings.

  • A non-null value stored in a GUID (UniqueIdentifier) column is 16 bytes + 4 bytes = 20 bytes. So if only 50% of these are NULL, that’s still a net savings.

So the “expected savings” depends strongly on what kind of column we’re talking about, and your estimate of what ratio will be null vs non-null. Variable width columns (varchars) are probably a little more difficult to predict accurately.

This Books Online Page has a table showing what percentage of different data types would need to be null for you to end up with a benefit.

So when should you use a Sparse Column? When you expect a significant percentage of the rows to have a NULL value. Some examples that come to mind:

  • A “Order Return Date” column in an order table. You would hope that a very small percent of sales would result in returned products.
  • A “4th Address” line in an Address table. Most mailing addresses, even if you need a Department name and a “Care Of” probably don’t need 4 separate lines.
  • A “Suffix” column in a customer table. A fairly low percent of people have a “Jr.” or “III” or “Esquire” after their name.

More Related Contents:

  1. Can I set ignore_dup_key on for a primary key?
  2. Single or multiple databases
  3. how to create this table in sql server?
  4. Efficiently convert rows to columns in sql server
  5. How can I get column names from a table in SQL Server?
  6. How can I select the first day of a month in SQL?
  7. Multi-statement Table Valued Function vs Inline Table Valued Function
  8. SQL Server Insert if not exists
  9. SQL – How to store and navigate hierarchies?
  10. Select statement to find duplicates on certain fields
  11. How to set table name in dynamic SQL query?
  12. Using a Variable in OPENROWSET Query
  13. How to find current transaction level?
  14. How do I see active SQL Server connections?
  15. Composite Primary key vs additional “ID” column?
  16. Check for file exists or not in sql server?
  17. UPDATE if exists else INSERT in SQL Server 2008 [duplicate]
  18. Group DateTime into 5,15,30 and 60 minute intervals
  19. Why is a UDF so much slower than a subquery?
  20. Is there a SELECT … INTO OUTFILE equivalent in SQL Server Management Studio?
  21. Cannot use a CONTAINS or FREETEXT predicate on table or indexed view because it is not full-text indexed
  22. Should I use SQL_Variant data type?
  23. Which SQL query is faster? Filter on Join criteria or Where clause?
  24. UPDATE and REPLACE part of a string
  25. Generate random int value from 3 to 6
  26. Improve SQL Server query performance on large tables
  27. Backup a single table with its data from a database in sql server 2008
  28. COALESCE Function in TSQL
  29. Script entire database SQL-Server
  30. Try_Convert for SQL Server 2008 R2

Leave a Comment