Tag Archives: Sql Server 2008 Features

SET Options with their setting values required while working with filtered index

While dealing with filtered index following Six SET option’s value should be set to ON

SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON

And the one SET option whose setting value should be off is:

SET NUMERIC_ROUNDABORT OFF

If these settings are not set properly then filtered index creation may fail, query execution may not use the index or DML operation on the table may fail. The DML operation may fail with a error message like below:

INSERT/Update failed because the following SET options have incorrect settings: ‘QUOTED_IDENTIFIER’. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.

Click on the error message link to see a detailed example demonstrating when we get this error and how to resolve it.

To know more on the difference between SET ANSI_NULLS ON and SET ANSI_NULLS OFF Setting please visit the article: SET ANSI_NULLS ON/OFF Setting in Sql Server

To know more on the difference between SET QUOTED_IDENTIFIER ON and SET QUOTED_IDENTIFIER OFF Setting please visit the article: Difference Between SET QUOTED_IDENTIFIER ON and OFF setting in SQL Server

Please correct me, if my understanding is wrong. Comments are always welcome.

New Features in Sql Server 2008

Following are the some of the new features of the Sql Server 2008 which are very helpful to the Sql Developers

1) Variable declaration allows initialization:

Prior to Sql Server 2008 to initialize a variable, we needed to first declare the variable and then we can initialize it by using SET/SELECT statement as shown below:

DECLARE @COUNT INT
SET @COUNT =100

Now in Sql Server 2008 Variable declaration allows initialization similar to the one we do in C#. Now instead of writing two statements, we can write a single statement as below:

DECLARE @COUNT INT =100

[ALSO READ] New Features in Sql Server 2012

2) Insert multiple rows using single INSERT Statement

To understand this feature first create an Employee Table by using the below script:

CREATE TABLE DBO.Employee 
( Id INT,  Name VARCHAR(50) )

Prior to Sql Server 2008, to insert multiple records we use to write statements like below:

INSERT INTO dbo.Employee VALUES(1,'Basavaraj')
INSERT INTO dbo.Employee VALUES(2,'Shashank')
INSERT INTO dbo.Employee VALUES(3,'Monty')

Now in Sql Server 2008 we can accomplish the same by writing script like below:

INSERT INTO dbo.Employee 
VALUES(1,'Basavaraj') ,
       (2,'Shashank') ,
       (3,'Monty')

3) Arithematic Assignment Operators
Now Sql Server 2008 also supports the Arithematic Assignment Operators like the below ones:

Operator Usage            Description
+=       SET @x+=@y       Same as: SET @x = @x + @y
-=       SET @x-=@y       Same as: SET @x = @x - @y
*=       SET @x*=@y       Same as: SET @x = @x * @y
/=       SET @x/=@y       Same as: SET @x = @x / @y
%=       SET @x%=@y       Same as: SET @x = @x % @y

Example:

DEClARE @x INT =2 ,@y INT = 2
SET @x+=@y 
SELECT @x as x,@y as y
Result:
x           y
----------- -----------
4           2

4) Table-Valued Parameters in Sql Server:
It provides option for the Client Applications to pass multiple rows of Data to Sql Server.

Prior to this, if we were needed to pass multiple rows of Data from client application to Sql Server, then we use to model the input data as xml /comma separated values and pass it to the stored procedure and in Stored Procedure convert this xml/comma separated values to a table variable/temporary table.

You can find detailed information on the Table-Valued Parameters and also on calling Stored Procedure with Table-Valued Parameter from Sql Server and C# .Net Code @ http://sqlhints.com/2011/09/09/table-valued-parameters-in-sql-server/

5) MERGE Statement

Merge statement is one of the interesting T-Sql enhancements of Sql Server 2008. With Merge statement we can very efficiently perform multiple DML operations like INSERT, UPDATE and DELETE on the target table data based on Source table data and the join condition specified between them.

You can find detailed information on MERGE Statement @ http://sqlhints.com/2011/09/24/merge-statement-in-sql-server-2008/

6) Sparse Column

Sparse Column is one more new feature introduced in SQL SERVER 2008. Storing a null value in a sparse column doesn’t take any space, but storing a non-null value in sparse column takes 4 bytes extra space than the non-sparse columns of the same data type.

You can find detailed information on Sparse Column @ http://sqlhints.com/2011/11/12/sparse-columns-in-sql-server-2008/

7) Date and Time Data Types

Date, Time, DateTime2 etc are the new date and time data type introduced in SQL SERVER 2008. You can find detailed information on DateTime2 datatype in the article Difference between DateTime and DateTime2 DataType.

8) As SP_Depends results were not always reliable/accurate/correct. To resolve this in SQL SERVER 2008 following two DMV’s are introduced
sys.dm_sql_referencing_entities
sys.dm_sql_referenced_entities

9) Filtered Indexes

Filtered Index (i.e. Index with where clause) is one of the new feature introduced in Sql Server 2008. It is a non-clustered index, which can be used to index only subset of the records of a table. As it will have only the subset of the records, so the storage size will be less and hence they perform better from performance perspective compared to the classic non-clustered indexes.

For detailed information on filtered index you can go through the article A-Z of Filtered Indexes with examples in Sql Server
Below is an example Filtered Index Creation Script:

SET QUOTED_IDENTIFIER ON
GO
CREATE NONCLUSTERED INDEX IX_Emplyoee_EmployeeId
 ON Employee(EmployeeId) WHERE EmployeeId > 500
GO

Before using filtered index please go through the below article, which explains the issue which we may face due to filtered index.

INSERT/UPDATE failed because the following SET options have incorrect settings: ‘QUOTED_IDENTIFIER’ …

[ALSO READ] New Features in Sql Server 2012
New features in Sql Server 2014