SQL with Manoj

Compatibility Level:

ALTER DATABASE SET COMPATIBILITY_LEVEL replaces sp_dbcmptlevel for setting the database compatibility level.

ALTER DATABASE database_name
SET COMPATIBILITY_LEVEL = { 80 90 100 }

COMPATIBILITY_LEVEL Is the version of SQL Server with which the database is to be made compatible. The value must be one of the following:
80 = SQL Server 2000
90 = SQL Server 2005
100 = SQL Server 2008

Compound Operators:
Operators that perform an operation and set a variable to the result, for example SET @x += 2, are available.

DECLARE @x1 int = 27;
SET @x1 += 2;
SELECT @x1 AS Added_2;

CONVERT Function:
The CONVERT function is enhanced to allow conversions between binary and character hexadecimal values.
CAST ( expression AS data_type [ (length ) ])
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

Date and Time Functionality:
SQL Server 2008 introduces four new date and time data types:
1. DATE
2. TIME
3. DATETIME2
4. DATETIMEOFFSET

It also includes support for the ISO week-date system.

DATEPART ( datepart , date )

SELECT DATEPART (TZoffset, 2007-05-10 00:00:01.1234567 +05:10);
SELECT DATEPART(millisecond, '00:00:01.1234567'); -- Returns 123
SELECT DATEPART(microsecond, '00:00:01.1234567'); -- Returns 123456
SELECT DATEPART(nanosecond, '00:00:01.1234567'); -- Returns 123456700

GROUPING SETS:

The GROUPING SETS, ROLLUP, and CUBE operators are added to the GROUP BY clause. There is a new function, GROUPING_ID(), that returns more grouping-level information than the existing GROUPING() function. The non-ISO compliant WITH ROLLUP, WITH CUBE, and ALL syntax is deprecated.

For more info: http://msdn.microsoft.com/en-us/library/bb522495.aspx

MERGE Statement:

This new Transact-SQL statement performs INSERT, UPDATE, or DELETE operations on a target table based on the results of a join with a source table. The syntax allows you to join a data source with a target table or view, and then perform multiple actions based on the results of that join.

For moe info: http://msdn.microsoft.com/en-us/library/bb510625.aspx

SQL Dependency Reporting:

SQL Server 2008 introduces a new catalog view and system functions to provide consistent and reliable SQL dependency reporting. You can use sys.sql_expression_dependencies, sys.dm_sql_referencing_entities, and sys.dm_sql_referenced_entities to report on cross-server, cross-database, and database SQL dependencies for both schema-bound and non-schema-bound objects.

Table-Valued Parameters:

The Database Engine introduces a new parameter type that can reference user-defined table types. Table-valued parameters can send multiple rows of data to a SQL Server statement or routine (such as a stored procedure or function) without creating a temporary table.

For more info: http://msdn.microsoft.com/en-us/library/bb510489.aspx

Transact-SQL Row Constructors:

Transact-SQL is enhanced to allow multiple value inserts within a single INSERT statement.

INSERT INTO dbo.Customers(custid, companyname, phone, address)
VALUES
(1, 'cust 1', '(111) 111-1111', 'address 1'),
(2, 'cust 2', '(222) 222-2222', 'address 2'),
(3, 'cust 3', '(333) 333-3333', 'address 3'),
(4, 'cust 4', '(444) 444-4444', 'address 4'),
(5, 'cust 5', '(555) 555-5555', 'address 5');

--- AND ---

SELECT * FROM (VALUES
(1, 'cust 1', '(111) 111-1111', 'address 1'),
(2, 'cust 2', '(222) 222-2222', 'address 2'),
(3, 'cust 3', '(333) 333-3333', 'address 3'),
(4, 'cust 4', '(444) 444-4444', 'address 4'),
(5, 'cust 5', '(555) 555-5555', 'address 5')) AS C(custid, companyname, phone, address);

Source: MSDN