It's a new feature in SQL Server 2008. It enables us to store the null values effectively. Ideally, if the column is NULL. Then, the data won't be stored in the database and it's cost effective solution to save the space. If your table has more Null values, we can opt this solution.
1. Column Sets - This will provide you the consolidated report of all the sparse columns. We will see this one in our example below.
2. Filtered index - Index will be created on the not null data in the sparse columns.
1. We can create index on the sparse column.
2. Sparse column cannot be added on certain column types like text, filestream datatype, geography, image, ntext etc..,
3. Sparse column could not be bounded with default value or rule cannot be applied on it.
drop table Venkat_Sparse_ColumnCheck
go
-- created a table to check the sparse column
CREATE TABLE dbo.Venkat_Sparse_ColumnCheck
(
ID INT IDENTITY(1,1) NOT NULL,
FIRST_NAME NVARCHAR(50) NOT NULL,
LAST_NAME NVARCHAR(50) NOT NULL,
ADDRESS1 NVARCHAR(20) SPARSE NULL, -- Sparse columns
ADDRESS2 NVARCHAR(20) SPARSE NULL, -- Sparse columns
CITY NVARCHAR(20) SPARSE NULL, -- Sparse columns
STATE NVARCHAR(2) SPARSE NULL, -- Sparse columns
COUNTRY NVARCHAR(10) SPARSE NULL, -- Sparse columns
ZIP_CODE NVARCHAR(20) SPARSE NULL, -- Sparse columns
CONSTRAINT PK_Venkat_Sparse_ColumnCheck PRIMARY KEY (ID)
)
GO
set nocount on
go
insert into dbo.Venkat_Sparse_ColumnCheck (first_name, last_name, ADDRESS1, ADDRESS2, city, STATE, country, zip_code)
values ('Venkatesan', 'Prabu', '1, first raod', null, 'Dharmapuri', 'TN', 'India', NULL);
insert into dbo.Venkat_Sparse_ColumnCheck (first_name, last_name, ADDRESS1, ADDRESS2, city, STATE, country, zip_code)
values ('Subs', 'subs', '2 Second road', null, 'trichy', 'tr', 'india', '636701');
insert into dbo.Venkat_Sparse_ColumnCheck (first_name, last_name, ADDRESS1, ADDRESS2, city, STATE, country, zip_code)
values ('Janu', 'C', '3 Third road', null, 'trichy', 'tr', 'india', '636701');
-- Here, we are getting a very ordinary output.
Select * from dbo.Venkat_Sparse_ColumnCheck
Column Set should be defined with datatype as XML and it should be given the condition as ALL_SPARSE_COLUMNS
go
-- created a table to check the sparse column
CREATE TABLE dbo.Venkat_Sparse_ColumnCheck
(
ID INT IDENTITY(1,1) NOT NULL,
FIRST_NAME NVARCHAR(50) NOT NULL,
LAST_NAME NVARCHAR(50) NOT NULL,
ADDRESS1 NVARCHAR(20) SPARSE NULL, -- Sparse columns
ADDRESS2 NVARCHAR(20) SPARSE NULL, -- Sparse columns
CITY NVARCHAR(20) SPARSE NULL, -- Sparse columns
STATE NVARCHAR(2) SPARSE NULL, -- Sparse columns
COUNTRY NVARCHAR(10) SPARSE NULL, -- Sparse columns
ZIP_CODE NVARCHAR(20) SPARSE NULL, -- Sparse columns
ADDRESS_SET XML COLUMN_SET FOR ALL_SPARSE_COLUMNS, -- Column Set to get all the details of the sparse columns
CONSTRAINT PK_Venkat_Sparse_ColumnCheck PRIMARY KEY (ID)
)
GO
set nocount on
go
insert into dbo.Venkat_Sparse_ColumnCheck (first_name, last_name, ADDRESS1, ADDRESS2, city, STATE, country, zip_code)
values ('Venkatesan', 'Prabu', '1, first raod', null, 'Dharmapuri', 'TN', 'India', NULL);
insert into dbo.Venkat_Sparse_ColumnCheck (first_name, last_name, ADDRESS1, ADDRESS2, city, STATE, country, zip_code)
values ('Subs', 'subs', '2 Second road', null, 'trichy', 'tr', 'india', '636701');
insert into dbo.Venkat_Sparse_ColumnCheck (first_name, last_name, ADDRESS1, ADDRESS2, city, STATE, country, zip_code)
values ('Janu', 'C', '3 Third road', null, 'trichy', 'tr', 'india', '636701');
-- Here, we are getting a very ordinary output.
Select * from dbo.Venkat_Sparse_ColumnCheck
Output for the first record,
Output for the Second record,
Output for the Third record,
Cheers,
Venkatesan Prabu .J
No comments:
Post a Comment