Saturday, November 27, 2010

Sparse columns do not take any space

Sparse columns are the new feature in SQL SERVER 2008. Sparse columns are better, when dealing with NULL in SQL SERVER. Sparse columns do not take any space. Defining the column as sparse can save a significant amount of disk space but at the cost of more overhead to retrieve non null values. Sparse columns can be defined by using the CREATE TABLE or ALTER TABLE statements.

CREATE TABLE DocumentStore
(
DocID int PRIMARY KEY,
Title varchar(200) NOT NULL,
ProductionSpecification varchar(20) SPARSE NULL,
ProductionLocation smallint SPARSE NULL,
MarketingSurveyGroup varchar(20) SPARSE NULL 
) ;
GO

Advantages of Sparse Columns
  • Storing a null in a sparse column takes up no space at all.
  • Sparse Columns will behave as the ordinary columns; SPARSE column can work as one XML column as well.
  • Sparse columns work really well with filtered indexes, where data are filled in the row. A filtered index on a sparse column can index only the rows that have populated values. This creates a smaller and more efficient index
  • SPARSE column saves database space when there are null values in database.
Disadvantages of Parse Columns
  • SPARSE column does not have IDENTITY or ROWGUIDCOL property.
  • Sparse Column must be null able
  • SPARSE column cannot be applied on text, ntext, image, timestamp, geometry, geography or user defined data types.
  • SPARSE column cannot have default value or rule or computed column.
  • Clustered index or a unique primary key index cannot be applied on SPARSE columns. SPARSE column cannot be part of clustered index key.
  • Table containing SPARSE column can have maximum size of 8018 bytes instead of regular 8060 bytes.
  • A table operation which involves SPARSE column takes performance hit over regular column.
  • Sparse columns are incompatible with data compression (Data compression doesn't work).
  • Merge replication does not support sparse columns
·          



Tuesday, November 16, 2010

SQL Server 2011 , Code Named “Denali” is released

SQL Server 2011 – Code Named “Denali” is released on November 11, 2010 at SQLPASS
You can download CTP1  right now and install on your machine.
SQL Server code-named 'Denali' helps empowers organizations to be more agile in today’s competitive market. Customers will more efficiently deliver mission-critical solutions through a highly scalable and available platform. Industry-leading tools help developers quickly build innovative applications while data integration and management tools help deliver credible data reliably to the right users and extended managed self-service BI capabilities enable meaningful insights.
The major features of the new products are as following:
  •  Enhanced Mission-Critical Platform: an enhanced highly available and scalable platform.
  • Developer and IT Productivity: new innovative productivity tools and features.
  • Pervasive Insight: expanding the reach of BI to business users and end-to-end data integration and management.
Book Online of SQL Server 2011 “Denali” is available here.
Installation guide for SQL Server 2011 “Denali” available here.


Share This