TOP Clause Enhancements - SQL 2005

SQL 2005 TOP Clause Enhancements

If anyone asks you, how often do you display say all records in table on a User Interface ? The answer would be not always, as we show only say 20 records or max 100 records in 1 page and then if user wants to see more pages then he can always move to next page to see the next set of records.

And when you want to see only say only 20 documents of 100 documents which are huge how do you display only those 20 documents sorted by size, TOP clause in SQL Server
becomes handy.

However this TOP Clause was present in SQL 2000 for serving the basic need and the same has been enhanced in SQL 2005 which makes the programmer life still easier.

Drawbacks of TOP in SQL 2000:
1) TOP clause is not dynamic and so cannot change based on user input.
Ex: For ex say user defines in UI say return me top 30 rows and later he needs top 40 rows , this change of parameter is not available with TOP clause in turn it means TOP is
not parameterized
P.S:- Same can be achieved by SET ROWCOUNT , but we need to remember to set it back to 0 otherwise all queries will return same rows as its at session level.

2) TOP Clause can be used only in SELECT statements and cannot be used in INSERT/UPDATE/DELETE statements.

Performance Drawback:­
TOP is faster when compared to SET NOCOUNT as Query optimizer uses the value of expression in the TOP clause for generating an execution plan for a query. But SET ROWCOUNT is
used outside a statement that executes a query, its value cannot be used to generate a query plan for a query.

IF EXISTS ( SELECT 'X' FROM sysobjects where TYPE = 'U' and name = 'DOCS_HISTORY')
DROP TABLE DOCS_HISTORY
GO

-- Create table named DOCS_HISTORY
CREATE TABLE [dbo].[DOCS_HISTORY](
[DOC_ID] [int] IDENTITY(1,1) NOT NULL,
[DOC_NAME] [varchar](100) NOT NULL,
[DOC_SIZE] [bigint] NULL,
[DOC_UPD_DATE] [datetime] NULL,
CONSTRAINT [PK_DOCS_HISTORY] PRIMARY KEY CLUSTERED([DOC_ID] ASC))

-- Insert itno DOCS_History
INSERT INTO [DOCS_HISTORY](DOC_NAME,DOC_SIZE,DOC_UPD_DATE)
SELECT 'TESTDOC1',100,getdate()-10

INSERT INTO [DOCS_HISTORY](DOC_NAME,DOC_SIZE,DOC_UPD_DATE)
SELECT 'TESTDOC2',200,getdate()-9

INSERT INTO [DOCS_HISTORY](DOC_NAME,DOC_SIZE,DOC_UPD_DATE)
SELECT 'TESTDOC3',300,getdate()-8

INSERT INTO [DOCS_HISTORY](DOC_NAME,DOC_SIZE,DOC_UPD_DATE)
SELECT 'TESTDOC4',400,getdate()-7

INSERT INTO [DOCS_HISTORY](DOC_NAME,DOC_SIZE,DOC_UPD_DATE)
SELECT 'TESTDOC5',500,getdate()-6

INSERT INTO [DOCS_HISTORY](DOC_NAME,DOC_SIZE,DOC_UPD_DATE)
SELECT 'TESTDOC6',600,getdate()-5

INSERT INTO [DOCS_HISTORY](DOC_NAME,DOC_SIZE,DOC_UPD_DATE)
SELECT 'TESTDOC7',700,getdate()-4

INSERT INTO [DOCS_HISTORY](DOC_NAME,DOC_SIZE,DOC_UPD_DATE)
SELECT 'TESTDOC8',800,getdate()-3

INSERT INTO [DOCS_HISTORY](DOC_NAME,DOC_SIZE,DOC_UPD_DATE)
SELECT 'TESTDOC9',900,getdate()-2

INSERT INTO [DOCS_HISTORY](DOC_NAME,DOC_SIZE,DOC_UPD_DATE)
SELECT 'TESTDOC10',1000,getdate()-1

-- User decides on how many rows to see in UI which is given by parameter @UI_Records
DECLARE @UI_Records INT
SELECT @UI_Records = 5

SELECT TOP (@UI_Records) * FROM [DOCS_HISTORY]
ORDER BY DOC_UPD_DATE DESC

Paging Logic
Let suppose you have returned 5 rows in a UI and want to return next 5 rows which is from 6 to 10 when user clicks on Next button on screen one can use the below code to achieve the same to return rows from 6 to 10 which works faster rather than having recordset in .Net and implementing paging from the same.

SELECT * FROM ( SELECT TOP 5 * FROM (SELECT TOP 10 * FROM [DOCS_HISTORY] ORDER BY DOC_UPD_DATE ASC
) AS DOCS_HISTORY1 ORDER BY DOC_UPD_DATE DESC
) AS DOCS_HISTORY2 ORDER BY DOC_UPD_DATE DESC


--Update only top 5 records whose size is > 300 with today date
UPDATE TOP (@UI_Records) [DOCS_HISTORY]
SET DOC_UPD_DATE = GETDATE()
WHERE DOC_SIZE > 300

--Delete top 5 records ordered by Doc_Upd_Date Descending
DELETE FROM [DOCS_HISTORY]
WHERE [DOC_ID] IN ( SELECT TOP (@UI_Records) [DOC_ID] FROM [DOCS_HISTORY]
ORDER BY DOC_UPD_DATE ASC)