Recently during migration from SQL server 2005 to SQL Server 2008, I have utilized one more feature of SQL server 2008 and that is SPARSE column. It is used to reduce storage data in tables, mostly when tables having 20-30 % NULL values.It is really amazing feature and we can reduce storage space as we can with SPARSE. Here are some demonstration with example.
Creating database and table objects
Let us insert some records in tables.
Now finaly checking the result.
I hope you liked this post of new feature of SQL Server 2008.
Creating database and table objects
-- Creating database CREATE DATABASE SparseDB GO USE SparseDB GO -- Creating table without sparse column CREATE TABLE TablewithoutSparse ( Id int identity(1,1), SparseCol1 int, SparseCol2 varchar(100), SparseCol3 datetime ) GO -- Creating table with sparse column CREATE TABLE TablewithSparse ( Id int identity(1,1), SparseCo1l int SPARSE, SparseCol2 varchar(100) SPARSE, SparseCol3 datetime SPARSE ) GO
Let us insert some records in tables.
-- Inserting 55555 records (All NULL values) in both tables INSERT INTO TablewithoutSparse VALUES (NULL, NULL, NULL) INSERT INTO TablewithSparse VALUES (NULL, NULL, NULL) GO 55555
Now finaly checking the result.
-- Checking rows and space used by both tables EXEC SP_SPACEUSED TablewithoutSparse GO EXEC SP_SPACEUSED TablewithSparse GO
I hope you liked this post of new feature of SQL Server 2008.
Comments
Post a Comment