SQL SERVER – Working with SPARSE Columns in SQL Server

CREATE TABLE UnSparsed(ID INT IDENTITY(1,1),FirstCol INT,SecondCol VARCHAR(100),ThirdCol SmallDateTime)GOCREATE TABLE Sparsed(ID INT IDENTITY(1,1),FirstCol INT SPARSE,SecondCol VARCHAR(100) SPARSE,ThirdCol SmallDateTime SPARSE)GODECLARE @idx INT = 0WHILE @idx < 50000BEGIN
INSERT INTO UnSparsed VALUES (NULL,NULL, NULL)INSERT INTO Sparsed VALUES (NULL, NULL, NULL)SET @idx+=1ENDGOsp_spaceused 'UnSparsed'GOsp_spaceused 'Sparsed'GODROP TABLE UnSparsed
GODROP TABLE Sparsed
GO

Previously I wrote about SQL SERVER – 2008 – Introduction to SPARSE Columns. Let us understand the concept of SPARSE column in more detail. I suggest you read the first part before continuing reading this article.

All SPARSE columns are stored as one XML column in database. Let us see some of the advantage and disadvantage of SPARSE column.

Advantages of SPARSE column are:

• INSERT, UPDATE, and DELETE statements can reference the sparse columns by name. SPARSE column can work as one XML column as well.
• SPARSE column can take advantage of filtered Indexes, where data are filled in the row.
• SPARSE column saves lots of database space when there are zero or null values in database.

Disadvantages of SPARSE column are:

• SPARSE column does not have IDENTITY or ROWGUIDCOL property.
• SPARSE column can not be applied on text, ntext, image, timestamp, geometry, geography or user defined datatypes.
• SPARSE column can not have default value or rule or computed column.
• Clustered index or a unique primary key index can not be applied SPARSE column. SPARSE column can not be part of clustered index key.
• Table containing SPARSE column can have maximum size of 8018 bytes instead of regular 8060 bytes.
• A table operation which involves SPARSE column takes performance hit over regular column.

Let me know your thoughts about SPARSE column feature of SQL Server 2008, I am very eager to see your point of view on this new feature.

I make a visit to the local user group whenever I get and keeping the community juices up and running for me. I get an opportunity to share some of the learnings and meet tons of new people. In our recent meetup at the SQLBangalore UG, I met a lot of young talent joining the IT field. Many take time to walk up to me and share a moment now and then. It is in this continued journey that inspired me to write this blog.

When I was at the UG meet, I said I write on a lot of topics and showed up this blog. It is rarely that someone fails to stumble onto this space. But one kid walked up to me and asked, what is SPARSE columns? After I explained what it was, he immediately asked me – is there any catch to it? Are there restrictions and limitations? Though there were few that I mentioned, I thought I will write few as part of this blog.

NULL Effect

Sparse columns MUST be nullable. Trying to create a sparse column as not NULL will fail with an error.

CREATE TABLE noNullSparse (Name VARCHAR(20) SPARSE NOT NULL)

Msg 1731, Level 16, State 1, Line 1

Cannot create the sparse column ‘Name’ in the table ‘noNullSparse’ because an option or data type specified is not valid. A sparse column must be nullable and cannot have the ROWGUIDCOL, IDENTITY, or FILESTREAM properties. A sparse column cannot be of the following data types: text, ntext, image, geometry, geography, or user-defined type.

Though the error is not explicit, you can see that the columns marked as SPARSE cannot be NOT NULL fields.

Datatypes to Watch

The above error gives away a lot of information on what are the datatypes that will cause you possible errors if marked as SPARSE.

• geography
• geometry
• image
• ntext
• text
• timestamp
• user-defineddatatypes(UDT)

The other attributes like IDENTITY, FILESTREAMS and ROWGUIDs are also not allowed.

SPARSE with Computed Column

Lesser known is that SPARSE column’s cannot be used with Computed Columns. But, we can use a SPARSE a column inside a Computed Column. Below is a classic example:

-- Trying to mark a computed column as SPARSE will fail with incorrect syntax errorCREATE TABLE Sales1 (MRP INT, Loss TINYINT, Profit AS (MRP - Loss)SPARSE)-- However, including an existing SPARSE column in a computed column is allowedCREATE TABLE Sales2 (MRP INT, Loss TINYINT SPARSE, Profit AS (MRP -Loss))-- CleanupDROP TABLE Sales2

Final Note

The other times we are likely to get an error is to mark a SPARSE column with default values, Primary key, clustered Index, partition key, user defined table type etc. These are some of the restrictions when working with SPARSE columns and will raise an error if used.

Would be great to know if anyone uses SPARSE columns in your designs anywhere? What are the scenario’s you found it useful? Let me know via your comments as it would be a great learning for all.