New Features SQL Server 2008 Part 1

In Microsoft SQL Server 2008, Microsoft introduces some new features and enhanced some of the existing ones. These changes can be classified into several groups via the database engine, t-sql, administration etc. One of the most important changes you can find in the new version of the SQL Server management studio is the introduction of the intelli-sense feature. Eventhough there are many changes, this article mainly features the T-SQL enhancements in SQL Server 2008.

Inline initialization of the variables

SQL Server 2008 enables you to initialize variables inline as part of the variable declaration statement instead of using separate DECLARE and SET statements. This enhancement helps you abbreviate your code. The following code example demonstrates inline initializations using a literal and a function:

DECLARE @i int = 100
DECLARE @now datetime = GETDATE()
SELECT @i, @now

Compound Assignment Operators

Compound assignment operators help abbreviate code that assigns a value to a column or a variable. The new operators are:

+= (plus equals)
-= (minus equals)
*= (multiplication equals)
/= (division equals)
%= (modulo equals)

You can use these operators wherever assignment is normally allowed—for example, in the SET clause of an UPDATE statement or in a SET statement that assigns values to variables. The following code example demonstrates the use of the += operator:

DECLARE @i int =100
SELECT @i+=10
SELECT @i

This code sets the variable @i to its current value, 100, plus 10, resulting in 110.

Table Value Constructor Support through the VALUES Clause

SQL Server 2008 introduces support for table value constructors through the VALUES clause. You can now use a single VALUES clause to construct a set of rows. One use of this feature is to insert multiple rows based on values in a single INSERT statement, as follows:

Use tempdb
go
CREATE TABLE dbo.Employees (EmpID int identity, Name varchar(32), age tinyint )
INSERT INTO dbo.Employees (Name, Age )
VALUES ('Anoop',32),('Arun',26),('Ram',26),('Mohan',50),('Shinosh',25)
SELECT *
FROM dbo.Employees

Note that even though no explicit transaction is defined here, this INSERT statement is considered an atomic operation. So if any row fails to enter the table, the entire INSERT operation fails.

SELECT * 
FROM (
VALUES ('Anoop',32),('Arun',26),('Ram',26),('Mohan',50),('Shinosh',25)
) AS Emp (Name, age )

The outer query can operate on this table expression like any other table expression, including joins, filtering, grouping, and so on.

Will continue posting the next part soon.......

Luke 1:30-33“But the angel said to her, "Do not be afraid, Mary, you have found favor with God. You will be with child and give birth to a son, and you are to give him the name Jesus. He will be great and will be called the Son of the Most High. The Lord God will give him the throne of his father David, and he will reign over the house of Jacob forever; his kingdom will never end.”