Search  Search  

Sparse Columns in SQL Server 2008
  More Windows ... on Twitter on YouTube on flickr on Pinterest on Facebook on Google+ on Tumblr on LinkedIn

Free wallpapers of the hottest girls of the 2014 Winter Olympics

Complete guide for Euro 2012’, about 300 hundred pages filled with comprehensive information about Euro 2012

Euro 12 - teams, managers, players, fixtures, referees, the brand new ‘Tango 12’ Adidas ball and much, much more. Few hundreds amazing photos.

E-books, free e-books, Euro 2012 e-books

Auckland events in photos at

1 500 photos of fun-run 'Ports of Auckland Round the Bays 2012' at

Ocean swim - more than 1 500 photos of State King of the Bays Forums forums Forums - articles, news, links Forums - articles, news, links 

Photos of State King of the Bays event at

‘Complete guide for Euro 2012’: Given profile all 368 footballers with their photos.

‘Complete guide for Euro 2012’: Read about star players of each squad. Find out who is the key player of each team. What Pele expects of Euro 2012?

‘Complete guide for Euro 2012’: Compare annual salary of coaches participating in the Euro-2012 - and how it happened that the team of the most paid coach finished the last in their group?

‘Complete guide for Euro 2012’: Euro 2012 footballers: who are the most expensive players? Who are the Rising stars at Euro 2012?

E-books, free e-books, Euro 2012 e-books
 Rating: 3.3      Rate this article   29 
 13 Apr 2012

Sparse Columns in SQL Server 2008


Sparse Columns are another new feature of SQL Server 2008. They are offering a trade-off between taking more space to hold data, but none at all when they are empty. They don't get you over the 1024 column limit, but could mean you can squeeze more columns into the 8,060 byte row limit for SQL Server.

Like everything in SQL Server you need to know when to use them and when to avoid.

Advantages of their usage:

• Storing a null in a sparse column takes up no space at all.
• To any external application the column will behave the same
• Sparse columns work really well with filtered indexes as you will only want to create an index to deal with the non-empty attributes in the column.
• You can create a column set over the sparse columns that returns an xml clip of all of the non-null data from columns covered by the set. The column set behaves like a column itself. Note: you can only have one column set per table.
• Change Data Capture and Transactional replication both work, but not the column sets feature.
And the disadvantages:
• If a sparse column has data in it it will take 4 more bytes than a normal column e.g. even a bit (0.125 bytes normally) is 4.125 bytes and unique identifier rises form 16 bytes to 20 bytes.
• Not all data type can be sparse: text, ntext, image, timestamp, user-defined data type, geometry, or geography or varbinray (max) with the FILESTREAM attribute cannot be sparse.
• computed columns can't be sparse (although sparse columns can take part in a calculation in another computed column)
• You can't apply rules or have default values.
• Sparse columns cannot form part of a clustered index. If you need to do that use a computed column based on the sparse column and create the clustered index on that (which sort of defeats the object).
• Merge replication doesn't work.
• Data compression doesn't work.
• Access (read and write) to sparse columns is more expensive, but I haven't been able to find any exact figures on this.
There are some rules whether to use them or not.
Basically, if more than 64% of your values are null in an int column then use sparse columns, and the longer the data type the lower the threshold for using sparse columns.

And few samples, how you can set up them.

Simply put the keyword SPARSE within a create table statement:

(CustomerID int PRIMARY KEY,
FirstName varchar(20) NOT NULL,
LastName varchar(20) NOT NULL,
Phone varchar(15) SPARSE NULL)

Selects against this table will work exactly as for normal columns whether the sparse column is included as a column in the select column or a filter in a where clause.

More similar topics with SQL tips:

Oracle database - How to select several random records from a table

Database DB2 - How to select several random records from a table

MySQL database - How to select several random records from a table

PostgreSQL - How to select several random records from a table

Microsoft SQL Server - How to select several random records from a table

  More similar links:

SQL Server 2008 R2

Database DB2 - How to select several random records from a table.

Microsoft SQL Server 2008 – The latest version.

Adding and Remowing Connections to Microsoft SQL server

PostgreSQL - How to select several random records from a table.

Alter Table, Alter Column in MS SQL Server 2008

MySQL database - How to select several random records from a table.

 Rate this article from 1 to 5 

        Add comment
Your name:
Your comment:
 Tags: SoftFern Tutorials, SoftFern Programming Tutorials, SQL Server 2008, SQL tips, stored procedures, Microsoft SQL server tips, SQL server solutions, SQL databases solutions, Sparse Columns, index, CREATE TABLE
   More Windows ...

  Home page Weird We sell Programming Windows
  News Our Photos Photoshop and Photography SQL server Microsoft Office
  Tech News Our Portfolio iPad Misc Web sites development
  Videos Archive Androids Graphic design Software development
  Auckland and New Zealand About Us Tablet PCs CD/DVD presentation Play games online
  Football Contact us Round the Bays 2012 photos Ocean Swim 2012 photos Auckland Events
  Sport Our Portfolio Stock Images Stock Photos Links Exchange
  Hot Girls Our clients On Twitter Add link Site Map
  Our Twitters’ gadget Site Map Google+ Site Grid
  Health and Beauty Site Grid Pinterest Natalia Soft Fern forums
  Misc News Web pages Facebook Photos, images Hair & Beauty
  On YouTube CD/DVD presentaton Tumblr Daily Motion Flickr
  YouTube - 2 Graphics LinkedIn Live Journal Stumble Upon
  Vimeo - videos Tutorials - Videos Tutorials - Weird Most Popular on Internet Yahts, boats, Tall Ships

SoftFern (New Zealand LTD). 
Design and development by SoftFern 2003 - 2014 Graphics supplied by