SQL Server: TOP…WITH TIES a Beauty of TSQL

TOP clause is commonly used to get top requiredrows from a result set. Beauty of this clause is that it can be used with WITHTIES clause, to retrieve all similar rows to base result set.

According to BOL “WITH TIES Specifiesthat additional rows be returned from the base result set with the same valuein the ORDER BY columns appearing as the last of the TOP n(PERCENT) rows. TOP…WITH TIES can be specified only in SELECT statements, andonly if an ORDER BY clause is specified. “

For example from following simple table I need toget records which have minimum purchase date value. In first method we will usecommon IN clause.

–Create temporary table

CREATE TABLE #MyTable (Purchase_Date DATETIME, Amount INT)

–Insert few rows to hold

INSERT INTO #MyTable

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)

SELECT * FROM #MyTable

WHERE Purchase_Date IN

       (SELECT MIN(Purchase_Date) FROM #MyTable)

We can also get ourdesired results by using TOP…WITH TIES.

SELECT TOP(1) WITH TIES * FROM #MyTable

ORDER BY Purchase_Date

By executing above query,you can find TOP WITH TIES worked amazingly but does this short code is reallya smart code. Let’s compare their performance.

Though TOP…WITH TIESclause really shortened our code but you can see that it performed poorly ascompare to our traditional code. This happened just because of ORDER BY clause.

This poor performance canbe controlled by placing a well defined index.