Introduced in SQL Server 2008, The SPARSE column is better at managing database size when null values are a plenty in that particular column.
To declare a particular column as sparse we have to simply specify the keyword ‘sparse’ after declaring the column name and the data type.
Please refer to the below mentioned queries for a better understanding of sparse column and its use.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
CREATE TABLE UnsparsedTable (ID INT IDENTITY(1, 1), Col1 INT, Col2 VARCHAR(100), Col3 SMALLDATETIME, Col4 UNIQUEIDENTIFIER ); GO CREATE TABLE SparsedTable (ID INT IDENTITY(1, 1), Col1 INT SPARSE, Col2 VARCHAR(100) SPARSE, Col3 SMALLDATETIME SPARSE, Col4 UNIQUEIDENTIFIER SPARSE ); GO DECLARE @ctr INT= 0; WHILE @ctr < 90000 BEGIN INSERT INTO UnsparsedTable VALUES (NULL, NULL, NULL, NULL ); INSERT INTO SparsedTable VALUES (NULL, NULL, NULL, NULL ); SET @ctr+=1; END; GO sp_spaceused 'UnsparsedTable'; GO sp_spaceused 'SparsedTable'; GO DROP TABLE UnsparsedTable; GO DROP TABLE SparsedTable; GO |
You can see here that the space occupied by null values sparse columns is far less than the regular column with null value.
Post Comments if this helps
