Tuesday, November 15, 2011

SQL Server: TOP…WITH TIES a Beauty of TSQL

TOP clause is commonly used to get top required rows from a result set. Beauty of this clause is that it can be used with WITH TIES clause, to retrieve all similar rows to base result set.
According to BOL “WITH TIES Specifies that additional rows be returned from the base result set with the same value in the ORDER BY columns appearing as the last of the TOP n (PERCENT) rows. TOP...WITH TIES can be specified only in SELECT statements, and only if an ORDER BY clause is specified.
For example from following simple table I need to get records which have minimum purchase date value. In first method we will use common IN clause.

--Create temporary table
CREATE TABLE #MyTable (Purchase_Date DATETIME, Amount INT)
--Insert few rows to hold
SELECT '11/11/2011', 100 UNION ALL
SELECT '11/12/2011', 110 UNION ALL
SELECT '11/13/2011', 120 UNION ALL
SELECT '11/14/2011', 130 UNION ALL
SELECT '11/11/2011', 150
--Get all records which has minimum purchase date (i.e. 11/11/2011)
WHERE Purchase_Date IN
       (SELECT MIN(Purchase_Date) FROM #MyTable)

We can also get our desired results by using TOP…WITH TIES.
ORDER BY Purchase_Date
By executing above query, you can find TOP WITH TIES worked amazingly but does this short code is really a smart code. Let’s compare their performance.

Though TOP…WITH TIES clause really shortened our code but you can see that it performed poorly as compare to our traditional code. This happened just because of ORDER BY clause.
This poor performance can be controlled by placing a well defined index.

1 comment:

All suggestions are welcome