Sparse Columns in SQL Server

Sparse Columns in SQL Server

Sparse column is introduced in SQL Server 2008, which is designed to store null values. If you store any null value in sparse column it doesn’t occupy any space on the database. If you store any non null value in sparse column it takes 4 bytes extra space. For example if you store bigint in database usually it requires 8 bytes if you store this value in sparse column it will occupy 12 bytes.

CREATE TABLE Students_Sparse (Id int IDENTITY(1,1), NAME VARCHAR(30), Address1 VARCHAR(30), Address2 VARCHAR(30), ADDRESS3 VARCHAR(30) SPARSE)

CREATE TABLE Students (Id int IDENTITY(1,1), NAME VARCHAR(30), Address1 VARCHAR(30), Address2 VARCHAR(30), ADDRESS3 VARCHAR(30))

INSERT INTO students_sparse VALUES ('anand','Hyderabad','AP','Andhra Pradesh')

go 1000

INSERT INTO students VALUES ('anand','Hyderabad','AP','Andhra Pradesh')

go 1000

-- If you insert non null values in to sparse column the size of table will be huge then an ordinary column table

sp_spaceused Students_Sparse

go

sp_spaceused Students

TRUNCATE TABLE Students_Sparse

TRUNCATE TABLE Students

Below example shows if you leave the sparse column by entering null values the column doesn’t occupy any space and there will be a huge change in space

INSERT INTO students_sparse VALUES ('anand','Hyderabad','AP',null)

go 1000

INSERT INTO students VALUES ('anand','Hyderabad','AP','Andhra Pradesh')

go 1000

-- If you insert non null values in to sparse column the size of table will be huge then an ordinary column table

sp_spaceused Students_Sparse

go

sp_spaceused Students

Filtered Index in SQL Server

Filtered Index is a new feature in SQL Server 2008, it is an optimized non-clustered index created on a subset of data. The definition of the index will have where clause in it. It provides huge performance improvement when we query a subset of data from a large table. These filtered indexes are relatively small when comparing to normal Indexes and queries will be less expensive in terms of I/O.

Note: We can’t create a filter index on complex WHERE clause queries and it doesn’t allow LIKE in where clause, we can use simple operators. Filtered Indexes can be rebuild online.

CREATE INDEX idx_hostName ON Total_Hosts(ServerName) WHERE Active = 1

SELECT si.index_id, si.name, si.type_desc, si.filter_definition FROM sys.indexes si, sys.tables st

WHERE si.object_id = st.object_id AND st.name= ‘Total_Hosts’

SQL Server Agent with Express Edition

If you install SQL Server Express Edition, it will install only Database Engine Services.

In configuration manager it shows SQL Server and SQL Server Agent services, but SQL Server agent in disable state. Why it installed SQL Server Agent is because if you perform upgrade from Express Edition to any other edition it enables the service after validating the installation and also no need to replace all files at the time of upgrade, because of that it installs SQL Server Agent service along with Express Edition.

Express Edition comes in different versions.

Express Edition with SSMS

Database Engine, Import and Export Data (No SSMS)

Express Edition with Advanced Features

Database Engine, SSMS, Reporting Services Configuration Manager, BIDS, Import and Export Data.