Thursday, April 30, 2009

COMPUTE (Transact-SQL) - SQL Server 2005

Generates totals that appear as additional summary columns at the end of the result set. When used with BY, the COMPUTE clause generates control-breaks and subtotals in the result set.

USE Database;
SELECT CustomerID, OrderDate, SubTotal, TotalDue
FROM Sales.SalesOrderHeader
ORDER BY OrderDate
COMPUTE SUM(SubTotal), SUM(TotalDue);

MERGE statement - SQL Server 2008 New Feature

The idea behind the MERGE statement is that the developer can construct TSQL data-manipulation language (DML) statements in which INSERT, UPDATE, or DELETE can occur in the same statement, based on different search conditions. I think this idea is very cool. The ability to complete multiple statements within one statement could potentially lead to less coding and increased performance.
In addition to this statement, another great feature has been added to the INSERT statement. In SQL Server 2008, the developer can issue multiple rows to be inserted without using a SELECT statement as the INSERT statement source. Instead, the VALUE clause of the INSERT statement can be used to specify sets of values separated by parentheses and commas.