• Table-Valued Parameters in Sql Server 2008
• MERGE Statement
• Sparse Column
• Difference between DateTime and DateTime2
  DataType
• As SP_Depends results are not always reliable/accurate/correct. To resolve this in Sql Server 2008 following two DMV’s ae introduced:
  sys.dm_sql_referencing_entities
  sys.dm_sql_referenced_entities
• A-Z of Filtered Indexes with examples in Sql Server
• New Features in Sql Server 2008

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

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')

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

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’ …