Sparse Columns in SQL Server 2008
Sparse Columns are another new feature of SQL Server 2008. They are offering a trade-off between taking more space to hold data, but none at all when they are empty. They don't get you over the 1024 column limit, but could mean you can squeeze more columns into the 8,060 byte row limit for SQL Server.
Like everything in SQL Server you need to know when to use them and when to avoid.
Advantages of their usage:
• Storing a null in a sparse column takes up no space at all.
• To any external application the column will behave the same
• Sparse columns work really well with filtered indexes as you will only want to create an index to deal with the non-empty attributes in the column.
• You can create a column set over the sparse columns that returns an xml clip of all of the non-null data from columns covered by the set. The column set behaves like a column itself. Note: you can only have one column set per table.
• Change Data Capture and Transactional replication both work, but not the column sets feature.
And the disadvantages:
• If a sparse column has data in it it will take 4 more bytes than a normal column e.g. even a bit (0.125 bytes normally) is 4.125 bytes and unique identifier rises form 16 bytes to 20 bytes.
• Not all data type can be sparse: text, ntext, image, timestamp, user-defined data type, geometry, or geography or varbinray (max) with the FILESTREAM attribute cannot be sparse.
• computed columns can't be sparse (although sparse columns can take part in a calculation in another computed column)
• You can't apply rules or have default values.
• Sparse columns cannot form part of a clustered index. If you need to do that use a computed column based on the sparse column and create the clustered index on that (which sort of defeats the object).
• Merge replication doesn't work.
• Data compression doesn't work.
• Access (read and write) to sparse columns is more expensive, but I haven't been able to find any exact figures on this.
There are some rules whether to use them or not.
Basically, if more than 64% of your values are null in an int column then use sparse columns, and the longer the data type the lower the threshold for using sparse columns.
And few samples, how you can set up them.
Simply put the keyword SPARSE within a create table statement:
CREATE TABLE Customers
(CustomerID int PRIMARY KEY,
FirstName varchar(20) NOT NULL,
LastName varchar(20) NOT NULL,
Phone varchar(15) SPARSE NULL)
Selects against this table will work exactly as for normal columns whether the sparse column is included as a column in the select column or a filter in a where clause.
More similar topics with SQL tips:
Oracle database - How to select several random records from a table
Database DB2 - How to select several random records from a table
MySQL database - How to select several random records from a table
PostgreSQL - How to select several random records from a table
Microsoft SQL Server - How to select several random records from a table