{"id":1363,"date":"2012-07-10T00:00:00","date_gmt":"2012-07-10T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/null-friendly-using-sparse-columns-and-column-sets-in-sql-server\/"},"modified":"2021-09-29T16:21:48","modified_gmt":"2021-09-29T16:21:48","slug":"null-friendly-using-sparse-columns-and-column-sets-in-sql-server","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/null-friendly-using-sparse-columns-and-column-sets-in-sql-server\/","title":{"rendered":"NULL-Friendly: Using Sparse Columns and Column Sets in SQL Server"},"content":{"rendered":"
\n

Sparse columns and column sets can be used in conjunction, and are ideal for fields that contain mostly NULL values. Sparse columns handle NULL values extremely efficiently; column sets combine all sparse columns into an XML representation as a new column. <\/p>\n

Sparse columns are designed to allow a relational database to store and process relatively unstructured data, where any individual entity may have a modest selection from a very wide set of attributes. This sort of data can be found frequently in scientific, pharmaceutical, and medical applications. EAV<\/a> (Entity\/Attribute\/Value) storage techniques were engineered for such data; however, EAV data storage has proved to be somewhat inefficient in such areas as query speed and storage cost. <\/p>\n

Sparse columns work well where a high proportion of the data in a column is NULL, because negligible storage space is used for the NULL representation. A table that is wide in theory can be made narrow in practice by implementing sparse columns, and the addition of filtered indexes provides effective searches on the sparse data. Sparse columns support the use of check constraints, as long as the constraints allow NULL values (a sparse column requirement). Constraints introduce basic data type checking, and thus allow for a good range of validation and integrity-checking techniques. <\/p>\n

Column sets provide a solution to the difficulties of efficient imports and exports of sparse data. The untyped XML representation that is generated for each row interacts directly with each sparse field. The XML field accepts inserts, and its data can be extracted and modified easily. This feature effectively supports certain types of serialized<\/a> object data from various applications. <\/p>\n

Sparse Columns <\/h2>\n

The SPARSE column property is a special, NULL-friendly column option – introduced with SQL Server 2008.  Sparse column data storage is extremely efficient for NULL values. In fact, a NULL value requires no space at all<\/strong> in a sparse column – MSDN<\/a> states that “…when the column value is NULL for any row in the table, the values require no storage.” However, the storage requirement for a non-NULL value is increased by up to 4 bytes when the SPARSE column property is used. Given that trade-off, Microsoft recommends not using sparse columns unless the percentage of NULL values in a column is high enough that a 20 percent to 40 percent storage savings gain would result. The ratio of NULLs to real values that would warrant implementing a sparse column differs for each data type. For example, when using the datetime<\/strong> type, 52% of the values must be NULL in order to save 40% in storage, but for the bit<\/strong> data type, 98% must be NULL. Fortunately, Microsoft has compiled a chart<\/a> to easily find the percentage information for each data type. <\/p>\n

Sparse columns are ordinary columns, with the addition of the SPARSE property. To create a table with sparse columns, simply use the SPARSE keyword: <\/p>\n

CREATE TABLE Royalty\n(  \n   Chronology INT PRIMARY KEY,\n   FirstName VARCHAR(50) NULL,\n   LastName VARCHAR(50) SPARSE,\n   CrownYear INT SPARSE,\n   Region VARCHAR(100) SPARSE,\n   Motto VARCHAR(100) SPARSE,\n)\nGO<\/pre>\n
In order to make a column sparse after-the-fact, use an ALTER TABLE statement: <\/p>\n
ALTER TABLE Royalty\nALTER COLUMN FirstName VARCHAR(50) SPARSE\nGO <\/pre>\n
This can also be accomplished by changing the ‘Is Sparse<\/strong>‘ column property to ‘Yes<\/strong>‘ in table design view in the SQL Server Management Studio GUI: <\/p>\n
\"1519-image001.png\"<\/p>\n
A sparse column must be NULLABLE, so the NULL keyword is optional. A sparse column is populated in the same manner as a normal column: <\/p>\n
INSERT Royalty(Chronology, FirstName, CrownYear, Region, Motto)\nVALUES (1, 'Catherine', 1509, 'Aragon', 'Humble and Loyal')\nGO\n\nINSERT Royalty(Chronology, FirstName, LastName, CrownYear, Motto)\nVALUES (2, 'Anne', 'Boleyn', 1533, 'La Plus Heureuse');\nGO\n\nSELECT * FROM Royalty\nGO<\/pre>\n
\"1519-image002.png\"<\/p>\n
Comparing NULL Storage <\/h2>\n
To demonstrate the space savings gained when using sparse columns, we will create two new tables that contain many NULL values, and then examine the amount of space used for storage in each table. First, we’ll create an Employees_sparse table with three sparse columns: <\/p>\n
CREATE TABLE Employees_sparse (\n   EMP_ID INT IDENTITY(5001,1) PRIMARY KEY, \n   SSN CHAR(9) NOT NULL, \n   TITLE CHAR(10) SPARSE NULL, \n   FIRSTNAME VARCHAR(50) NOT NULL, \n   MIDDLEINIT CHAR(1) SPARSE NULL, \n   LASTNAME VARCHAR(50) NOT NULL, \n   EMAIL CHAR(50) SPARSE NULL)\nGO <\/pre>\n
Next, an identical table without<\/strong> the SPARSE property enabled: <\/p>\n
CREATE TABLE Employees (\n   EMP_ID INT IDENTITY(5001,1) PRIMARY KEY, \n   SSN CHAR(9) NOT NULL, \n   TITLE CHAR(10) NULL, \n   FIRSTNAME VARCHAR(50) NOT NULL, \n   MIDDLEINIT CHAR(1) NULL, \n   LASTNAME VARCHAR(50) NOT NULL, \n   EMAIL CHAR(50) NULL)\nGO <\/pre>\n
Before we populate these tables, let’s add a CHECK<\/a> constraint to the EMAIL field. Sparse columns DO support CHECK constraints. We want to ensure that each email address in the table will contain the ‘@’ symbol, and that the ‘@’ symbol appears only<\/strong> once<\/strong> in each email address: <\/p>\n
ALTER TABLE Employees_sparse\nADD CONSTRAINT chkEmailEmpSparse CHECK \n(\n   LEN(REPLACE(EMAIL,'@',''))+1=LEN(EMAIL)\n)\nGO\n\nALTER TABLE Employees\nADD CONSTRAINT chkEmailEmp CHECK \n(\n   LEN(REPLACE(EMAIL,'@',''))+1=LEN(EMAIL)\n)\nGO<\/pre>\n
To save time, we’ll use RedGate’s SQL Data Generator<\/a> to insert 50<\/strong>000<\/strong> Employee records into the Employees_sparse table. <\/p>\n
Now that we have test data in the table, let’s take a look at portion of it to verify that the distribution of data in the sparse columns is realistic: <\/p>\n
SELECT TOP 10 * \nFROM Employees_sparse\nGO <\/pre>\n
\"1519-image003.png\"<\/p>\n
We see a good sampling of the data, where there are very few values in the columns that have the SPARSE property enabled. The Data Generator recognized that TITLE, MIDDLEINIT, and EMAIL are sparse columns, and should contain very few values. <\/p>\n
Because we want to compare data storage size between the two tables, we need to insert identical<\/strong> data into the non-sparse table (Employees). We’ll copy over all 50000 records from the sparse table: <\/p>\n
INSERT INTO Employees (SSN, TITLE, FIRSTNAME, MIDDLEINIT, LASTNAME, EMAIL)\nSELECT SSN, TITLE, FIRSTNAME, MIDDLEINIT, LASTNAME, EMAIL \nFROM Employees_sparse\nGO <\/pre>\n
To view the sparse table’s storage savings, we’ll run the sp_spaceused<\/strong><\/a> system stored procedure on both tables: <\/p>\n
sp_spaceused 'Employees'\nGO\n\nsp_spaceused 'Employees_sparse'\nGO <\/pre>\n
\"1519-image004.png\"<\/p>\n
We see that the storage difference between the two tables is quite notable, including index sizes, when the data is very sparse<\/strong>. However, the space savings that were gained can be lost quickly in the event that many NULL values are replaced with real values. <\/p>\n
Sparse Column Limitations <\/h2>\n
A few things to keep in mind when considering using sparse columns: <\/p>\n