New features of SQL Server 2008

 Here I am going to tell you some new transact-SQL enhancement.

1.      Now you can declare and assigne the variables in  a single stagtement. In the old version if you want to declare the variable and assigne the variable then you have to write in the following manner

DECLARE @A BIGINT

      ,@B NVARCHAR(10)

     

SET @A = 10

SET @B = 'test'

            Now you can use the following manner

DECLARE @A BIGINT = 10

       ,@B NVARCHAR(10) = 'test'

      If you are using the reference of a variable in the declaration then it will show the error

DECLARE

       @A BIGINT = 10

       ,@B BIGINT = @A + 1

      

SELECT @B

            You have to change the above statements in the following manner

DECLARE

       @A BIGINT = 10

       ,@B BIGINT

      

SET @B = @A + 1

            You can also assingne the multipal statements in the single line

DECLARE

       @A BIGINT = 10

       ,@B BIGINT

      

SELECT @B = @A + 1, @B +=2

SELECT @B 

The output will be 13

Here you have to use the SELECT statement in the SELECT @B = @A + 1, @B +=2

You can not use SET statement here.

2. Insert multipal statement

     If want want to insert multipal records  then what have to use the  following queries

INSERT INTO Employee (EmpId, Name, Salary)

VALUES ('Emp01' , 'A', 20000)

INSERT INTO Employee (EmpId, Name, Salary)

VALUES ('Emp02' , 'B', 30000)

INSERT INTO Employee (EmpId, Name, Salary)

VALUES ('Emp03' , 'C', 40000)

OR

INSERT INTO Employee (EmpId, Name, Salary)

SELECT 'Emp01' , 'A', 20000

UNION ALL

SELECT 'Emp02' , 'B', 30000

UNION ALL

SELECT 'Emp03' , 'C', 40000

But now we can insert multipal records in a single query

INSERT INTO Employee (EmpId, Name, Salary)

VALUES

('Emp01' , 'A', 20000),

('Emp02' , 'B', 30000),

('Emp03' , 'C', 40000)

If you want apply some conditions or joins on some default values of collection then you have to insert those values into a temp table and apply those joins or conditions. We can achieve this through the new features of SQL Server 2008. Here I am using the  CTE (Common Table Expression ) to insert  default values instead of the temp table and applying the conditions on that

WITH ConditionalDataCTE(EmpId, Name, Salary)

AS

(

       SELECT * FROM

       (VALUES

              ('Emp01' , 'A', 20000),

              ('Emp02' , 'B', 30000),

              ('Emp03' , 'C', 40000)

    ) AS EMP (EmpId, Name, Salary)

)

SELECT * FROM ConditionalDataCTE

Now we can apply different conditions or JOINS on the ConditionalDataCTE. 

SELECT Employee.* FROM ConditionalDataCTE

INNER JOIN Employee ON Employee.EmpId = ConditionalDataCTE.EmpId

3. New Merge statement

     If we have a collection of records and want to update or insert through a single query then we can achieve this through the Merge statement. The syntax of the Merge statement is

MERGE [INTO] <target table>

USING table or table expression>

ON merge predicate> (semantics similar to outer join)

WHEN MATCHED <statement to run when match found in target>

WHEN [TARGET] NOT MATCHED <statement to run when no match found in target>

Here I am using an Employee  table and I have to update and insert the multipal records in the Employee table then I will use the following query

WITH NewEmployee(EmpId, Name, Salary)

AS

(

       SELECT * FROM

       (VALUES

              ('Emp01' , 'A', 20000),

              ('Emp02' , 'B', 70000),

              ('Emp03' , 'C', 80000),

              ('Emp04' , 'D', 80000)

    ) AS EMP (EmpId, Name, Salary)

)

MERGE INTO EMPLOYEE

USING NewEmployee

ON EMPLOYEE.EmpId = NewEmployee.EmpId

WHEN  NOT MATCHED THEN

 INSERT (EmpId, Name, Salary)

 VALUES (NewEmployee.EmpId, NewEmployee.Name, NewEmployee.Salary)

WHEN MATCHED THEN

 UPDATE SET

 Name = NewEmployee.Name

 ,Salary = NewEmployee.Salary;

Here I have NewEmployee table and appling the Merge statement with Employee table. If the NewEmployee's EmpId are exist in the Employee table then those records will be updated otherwise those will be inserted.

4. Use Table type paramenter in stored procedure.

   Now we can also pass the table type parameter in the SQL Server Stored Procedure. I am using the following commend to create the table type variable

CREATE TYPE EmployeeTable AS TABLE

(

       [EmpId] [nvarchar](20) NULL,

       [Name] [nvarchar](50) NULL,

       [Salary] [decimal](18, 0) NULL

)

GO

Now need passing this table type variable in the parameter of the stored procedure

CREATE PROCEDURE useTableType

       @NewEmployee AS EmployeeTable READONLY

AS

BEGIN

       SET NOCOUNT ON;

   

MERGE INTO EMPLOYEE

USING @NewEmployee

ON EMPLOYEE.EmpId = [@NewEmployee].[EmpId]

WHEN  NOT MATCHED THEN

 INSERT (EmpId, Name, Salary)

 VALUES ([@NewEmployee].[EmpId], [@NewEmployee].[Name], [@NewEmployee].[Salary])

WHEN MATCHED THEN

 UPDATE SET

 Name = [@NewEmployee].[Name]

 ,Salary = [@NewEmployee].[Salary];

END

GO