Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Towards the innovative SQL ideas
Browse by Tags · View All
sql server 125
sql 124
ms sql server 119
ms sql 118
database 109
tsql 81
#SQL Server 78
t-sql 75
#sql 71
sql server general 67

Archive · View All
April 2011 14
July 2011 12
May 2011 12
August 2011 11
June 2011 10
September 2011 8
December 2011 6
November 2011 6
September 2013 5
June 2013 5

SPARSE Column - New feature of SQL Server 2008

Apr 7 2011 2:17AM by Paresh Prajapati   

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
-- 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

Tags: sql, sql server 2008, sql server 2005, tsql, sql server, ms sql, ms sql server, t-sql, new features, enhancements,


Paresh Prajapati
6 · 23% · 7431
1
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

1  Comments  

  • OK, I'll admit. I had no idea if you put a number after "GO" it would do that command x number of times. Thanks for these little tidbits of info.

    commented on May 4 2011 5:15PM
    Amy
    493 · 0% · 77

Your Comment


Sign Up or Login to post a comment.

"SPARSE Column - New feature of SQL Server 2008" rated 5 out of 5 by 1 readers
SPARSE Column - New feature of SQL Server 2008 , 5.0 out of 5 based on 1 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]