1. Using the OVER clause with aggregate functionsThe following sample is from BOL:-------------------------------------USE AdventureWorks2008R2;GOSELECT SalesOrderID, ProductID, OrderQty ,SUM(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Total' ,AVG(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Avg' ,COUNT(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Count' ,MIN(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Min' ,MAX(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Max'FROM Sales.SalesOrderDetail WHERE SalesOrderID IN(43659,43664);-------------------------------------

If using subqueries, the script will be more complicated:-------------------------------------SELECT A.SalesOrderID, B.ProductID, B.OrderQty, A.Total, A.Avg, A.Count, A.Min, A.MaxFROM ( SELECT SalesOrderID ,SUM(OrderQty) AS 'Total' ,AVG(OrderQty) AS 'Avg' ,COUNT(OrderQty) AS 'Count' ,MIN(OrderQty) AS 'Min' ,MAX(OrderQty) AS 'Max' FROM Sales.SalesOrderDetail WHERE SalesOrderID IN(43659,43664) GROUP BY SalesOrderID ) A INNER JOIN Sales.SalesOrderDetail B ON A.SalesOrderID=B.SalesOrderIDGO-------------------------------------

However, if the table size is big, you'd better verify the performance of over clause comparing with subqueries first. Based on my testing, the performance of over clause is not as good as subqueries. Here is the execution plan and statistics of both 2 queries:

a) OVER Clause query

The execution plan uses the table spool to cache the temporary data, and generate aggregation data.

the io statistics reveals there are 3 physical reads and 55 locical reads

b) subqueries

here the execution plan seek the table 2 times.

Physical reads is the same 3, but there is only 12 logical reads. the sql server elapsed time is only 27ms.

looks like subquery is faster than OVER clause :) , But OVER Clause with Aggregation function makes your script concise and easy to understand.

2. CTE - Common Table ExpressionsCTE is another way to make you code concise and efficient. a) Recursive Queries Using CTEReturning hierarchical data is a common use of recursive queries, for instance, displaying employees in an organizational chart. If you are familiar with oracle sqlplus, you can get the hierarchical data by the "Connect by prior" command just like code below:

However, with CTE there is very nice way to implement it. Here is the script:-------------------------------------CREATE TABLE dup(a int, b int)GOinsert into dup(a,b) values(1,1),(1,1),(1,1),(2,2),(2,2),(3,3),(4,4),(4,4),(4,4),(5,5),(5,5)GO;WITH dupcte (a,b, row_count)AS( SELECT a,b, ROW_NUMBER() OVER(PARTITION BY a,b ORDER BY a,b) AS row_count FROM dup ) DELETE FROM dupcte WHERE row_count > 1 GO GO select * from dup GO ------------------------------------- The Final output is like

3. Merge StatementThe T-SQL Merge Statement is simliar with Oracle sqlplus "Merge Into"(study from Oracle? :) ). It provide you an easy way to impletement:If find the same data in the destination table then update, or else insert a new row.