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.
Reference : Pinal Dave (https://blog.sqlauthority.com)
27 Comments. Leave new
Its not simple as it appears to me, I tried it on my user table with 1000000 records but with no benefit although middlename column, featured column have 900000 null records. Here is the details of how I did it. Am I not doing it correctly or missing somewhere, Please suggest if anyone has used it in practice.
Thanks.
select top 400000 * into user_with_sparse from dbo.users
select top 400000 * into user_without_sparse from dbo.users
ALTER TABLE user_with_sparse ALTER COLUMN middlename varchar(40) SPARSE NULL
ALTER TABLE user_with_sparse ALTER COLUMN Featured smallint SPARSE NULL
sp_spaceused ‘user_with_sparse’
user_with_sparse 400000 377120 KB 377032 KB 16 KB 72 KB
sp_spaceused ‘user_without_sparse’
user_without_sparse 400000 375280 KB 375184 KB 16 KB 80 KB
HI friends,
as per theory in sql server we can create 1024 non sparse column and 100000 sparse columns but when i checked, we cant create more than 1024 column in sql server per table.
also we would like to discuss, if we keep not null value in sparse column,it take same size as we store it into non sparse column. but as per theory it should take 4 byte extra. example the datetime take 8 byte to stored date in non sparse column. but as per theory it should take 12 byte(8+4) byte when we store it in sparse columns. but i dint find this practically. Please suggest me if i am wrong.
Hi,
some confusion hopefully you can clarify it :
•Table containing SPARSE column can have maximum size of 8018 bytes instead of regular 8060 bytes.
•XML data has a size limit of 2 GB. If the combined data of all the nonnull sparse columns in a row exceeds this limit, the query or DML operation will produce an error.
The statements are confusing one states limit is 8018 while the other states 2GB ?
Please help .
Regards,
Raj
can we insert the data into sparse columns by import wizard
You mentioned that the sparse column is being store as an XML. I don’t think this is accurate. If you have a column set with the sparse columns definition in a table, that is the XML representation of the sparse values. But if you examine the pages (dbcc page) you will see the data is stored in the native data type. Also if you run a query (with execution plan) and include the column set XML column in a select query,,, you will see that there is a scalar function that is being called ( suggesting that the XML even for column sets are stored in the native format ) is acting as calculated field. Unless there is something I am missing, but I am looking at the physical pages…or has it changed after SQL 2008?