SQL Server: October 2012

Sparse Column in SQL Server

What is sparse column?

It is a method to store the data for a column in most efficient way.
It is useful for those column which comparatively having more values as “NULL”.
So in other words, we can say, it is a optimized storage method for NULL values.
In the case of sparse column, when the column value is NULL for any row in the table, the values require no space.
A column can be defined as SPARSE, by using the “CREATE TABLE” or “ALTER TABLE” statements, for example :-

CREATE TABLE tbl ( col VARCHAR(100) SPARSE NULL

)

SPARSE column can take advantage of filtered Indexes, where data are filled in the row.

What is the need to set a column as sparse column or why can we not set all columns as sparse column as it is the optimized way to store the NULL values?

So here is the answer, Sparse Columns require 4 extra bytes of storage for each non NULL fixed-length data type value in the table and zero bytes to store a NULL value. So it is important to justify the need of making any column as sparse column else we will end up with using of more spaces.

In normal scenario, Consider using sparse columns when the space saved is at least 20 percent to 40 percent.

Limitation of SPARSE column:-

SPARSE column cannot be applied on text, ntext, image, timestamp, geometry, geography or user defined data types.
SPARSE column does not have IDENTITY or ROWGUIDCOL property.
SPARSE column cannot have default value or rule or computed column.
Clustered index or a unique primary key index cannot be applied SPARSE column. SPARSE column cannot be part of clustered index key.

How does SQL Server really store NULL

Each row has a null bitmap for columns. If the row in that column is null then a bit in the bitmap is 1 else it's 0.

For variable size data types the actual size is 0 bytes.

For fixed size data type the actual size is the default data type size in bytes set to default value (0 for numbers, '' for chars).

GETDATE () and GETUTCDATE () in SQL Server

GETDATE ()

· It will return the date with your regional time or we can say, it returns the current system date and time.

· If you are connected to the SQL server remotely then the timestamp displayed will be the timestamp of the remote machine and not your local machine.

· It is a nondeterministic function.

· Views and expressions that reference this column cannot be indexed.

GETUTCDATE ()

· It will return the date and GMT time (Greenwich Mean Time).

· GETUTCDATE () can be used to store the timestamp that is independent of Time Zones.

· The current UTC time is derived from the current local time and the time zone setting in the operating system of the computer on which the instance of Microsoft SQL Server is running.

· GETUTCDATE is a nondeterministic function.

· Views and expressions that reference this column cannot be indexed.

facebook page

SET XACT_ABORT in SQL Server

SET XACT_ABORT ON instructs SQL Server to roll back the entire transaction and abort the batch when a run-time error occurs.

When SET XACT_ABORT is OFF, in some cases only the Transact-SQL statement that raised the error is rolled back and the transaction continues processing. Depending upon the severity of the error, the entire transaction may be rolled back even when SET XACT_ABORT is OFF.

OFF is the default setting.

Compile errors, such as syntax errors, are not affected by SET XACT_ABORT.

SET XACT_ABORT ON is still needed with structured error handling. A client attention event (timeout or query cancel) will stop the executing code and the CATCH block will not be invoked. This could leave an open transaction unless the application code performs a rollback or closes the connection.

Its recommended to specify SET XACT_ABORT ON in stored procedures that contain explicit transactions to ensure that transactions are rolled back even if the application code doesn’t clean up properly.

When you use XACT_ABORT with structured error handling, you should check XACT_STATE() in the CATCH block to determine whether or not COMMIT/ROLLBACK is possible.

SET NOEXEC ON in SQL SERVER

It compiles each query but does not execute it.

Suppose we have a big SQL script with a ton of code and we want to make sure it runs but you don't want to execute it because it updates tables, deletes data etc.

The execution of statements in SQL Server has two phases: compilation and execution.

This setting is useful for having SQL Server validate the syntax and object names in Transact-SQL code when executing. It is also useful for debugging statements that would generally be part of a larger batch of statements.

The setting of SET NOEXEC is set at execution or run time and not at parse time.

E.g.

SET NOEXEC ON;

SELECT GETDATE()

++++++++++++++++++++++

SET NOEXEC OFF;