SPARSE COLUMN-SQL SERVER 2008

SPARSE COLUMN-SQL SERVER 2008

SQL SERVER 2008's new feature SPARSE COLUMN. Sparse columns are ordinary columns that have an optimized storage for null values. Sparse columns reduce the space requirements for null values at the cost of more overhead to retrieve nonnull values. Consider using sparse columns when the space saved is at least 20 percent to 40 percent. Sparse columns and column sets are defined by using the CREATE TABLE or ALTER TABLE statements.

All SPARSE columns are stored as one XML column in database. Let us see some of the advantage and disadvantage of SPARSE column.

Advantages of SPARSE column are:

• INSERT, UPDATE, and DELETE statements can reference the sparse columns by name. SPARSE column can work as one XML column as well.
• SPARSE column can take advantage of filtered Indexes, where data are filled in the row.
• SPARSE column saves lots of database space when there are zero or null values in database.
• A non-null value stored in a GUID (UniqueIdentifier) column is 16 bytes + 4 bytes = 20 bytes. So if only 50% of these are NULL, that's still a net savings.
• 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.

Disadvantages of SPARSE column are:

• SPARSE column does not have IDENTITY or ROWGUIDCOL property.
• SPARSE column can not be applied on text, ntext, image, timestamp, geometry, geography or user defined datatypes.
• SPARSE column can not have default value or rule or computed column.
• Clustered index or a unique primary key index can not be applied SPARSE column. SPARSE column can not 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.
• 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. (Changed17/5/2009 thanks Alex for spotting the typo)
• 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.

·          

Syntax to create sparsed columns

CREATE TABLE Sparsed(ID INT IDENTITY(1,1),

FirstCol INT SPARSE,

SecondCol VARCHAR(100) SPARSE,

ThirdCol SmallDateTime SPARSE)

GO

SQLFacts: Injection in SQL SERVER

SQLFacts: Injection in SQL SERVER: SQL injection is a technique where  hacker  can inject SQL commands into an SQL statement, via web page.Injected SQL commands can comprom...

SQLFacts: SQL Server Exception Handling by TRY…CATCH

SQLFacts: SQL Server Exception Handling by TRY…CATCH: SQL Server Exception Handling by TRY…CATCH Like  C#, SQL Server also has an exception model to handle exceptions and errors that occur...

SQL Server Exception Handling by TRY…CATCH

SQL Server Exception Handling by TRY…CATCH

Like  C#, SQL Server also has an exception model to handle exceptions and errors that occurs in T-SQL statements. To handle exception in Sql Server we have TRY..CATCH blocks. We put T-SQL statements in TRY block and to handle exception we write code in CATCH block. If there is an error in code within TRY block then the control will automatically jump to the corresponding CATCH 
blocks. In Sql Server, against a Try block we can have only one CATCH block.

TRY..CATCH Syntax

BEGIN TRY

--SQL statements

--or SQL statement blocks

END TRY

BEGIN CATCH

--SQL statements

--or SQL statement blocks

END CATCH

Error Functions used within CATCH block

1.    ERROR_NUMBER()

This returns the error number and its value is same as for @@ERROR function.

2.    ERROR_LINE()

This returns the line number of T-SQL statement that caused error.

3.    ERROR_SEVERITY()

This returns the severity level of the error.

4.    ERROR_STATE()

This returns the state number of the error.

5.    ERROR_PROCEDURE()

This returns the name of the stored procedure or trigger where the error occurred.

6.    ERROR_MESSAGE()

This returns the full text of error message. The text includes the values supplied for any substitutable parameters, such as lengths, object names, or times.

Exception handling example

BEGIN TRY

DECLARE @num INT, @msg varchar(200)

---- Divide by zero to generate Error

SET @num = 5/0

PRINT 'This will not execute'

END TRY

BEGIN CATCH

PRINT 'Error occured that is'

set @msg=(SELECT ERROR_MESSAGE())

print @msg;

END CATCH

GO

 Note

1. A TRY..CATCH block combination catches all the errors that have a severity between 11 and 19.

2. The CATCH block is executed only if there is an error occurs in T-SQL statements within TRY block otherwise the CATCH block is ignored.

3. Each TRY block is associated with only one CATCH block and vice versa

4. TRY and CATCH blocks can’t be separated with the GO statement. We need to put both TRY and CATCH blocks within the same batch.

5. TRY..CATCH blocks can be used with transactions. We check the number of open transactions by using @@TRANCOUNT function in Sql Server.

6. XACT_STATE function within the TRY..CATCH block can be used to check whether a open transaction is committed or not. It will return -1 if transaction is not committed else returns 1.

SQL Server Exception Handling

SQL Server Exception Handling by TRY…CATCH

Like  C#, SQL Server also has an exception model to handle exceptions and errors that occurs in T-SQL statements. To handle exception in Sql Server we have TRY..CATCH blocks. We put T-SQL statements in TRY block and to handle exception we write code in CATCH block. If there is an error in code within TRY block then the control will automatically jump to the corresponding CATCH blocks. In Sql Server, against a Try block we can have only one CATCH block.

TRY..CATCH Syntax

BEGIN TRY

--SQL statements

--or SQL statement blocks

END TRY

BEGIN CATCH

--SQL statements

--or SQL statement blocks

END CATCH

Error Functions used within CATCH block

1.    ERROR_NUMBER()

This returns the error number and its value is same as for @@ERROR function.

2.    ERROR_LINE()

This returns the line number of T-SQL statement that caused error.

3.    ERROR_SEVERITY()

This returns the severity level of the error.

4.    ERROR_STATE()

This returns the state number of the error.

5.    ERROR_PROCEDURE()

This returns the name of the stored procedure or trigger where the error occurred.

6.    ERROR_MESSAGE()

This returns the full text of error message. The text includes the values supplied for any substitutable parameters, such as lengths, object names, or times.

Exception handling example

BEGIN TRY

DECLARE @num INT, @msg varchar(200)

---- Divide by zero to generate Error

SET @num = 5/0

PRINT 'This will not execute'

END TRY

BEGIN CATCH

PRINT 'Error occured that is'

set @msg=(SELECT ERROR_MESSAGE())

print @msg;

END CATCH

GO

 Note

1. A TRY..CATCH block combination catches all the errors that have a severity between 11 and 19.

2. The CATCH block is executed only if there is an error occurs in T-SQL statements within TRY block otherwise the CATCH block is ignored.

3. Each TRY block is associated with only one CATCH block and vice versa

4. TRY and CATCH blocks can’t be separated with the GO statement. We need to put both TRY and CATCH blocks within the same batch.

5. TRY..CATCH blocks can be used with transactions. We check the number of open transactions by using @@TRANCOUNT function in Sql Server.

6. XACT_STATE function within the TRY..CATCH block can be used to check whether a open transaction is committed or not. It will return -1 if transaction is not committed else returns 1.