MSSQL 2005 Tip: Using TOP with Subquery

SQL Server 2005 introduced new TOP behavior - like TOP function supports variables and expressions.

As a result it is possible to use a subquery as input to TOP. Subquery should return scalar. Like so:

SELECT TOP(SELECT COUNT(*)
             FROM Table2) Col1, Col2
  FROM Table1
 ORDER BY Col1

Which seems kinda strange for people with MSSQL 2000 experience. In addition it is possible to use TOP with INSERT, UPDATE and DELETE like:

DELETE TOP(1000)
  FROM Table1
 WHERE Id < 10000

Just wanted to share these small tips :)

Related Posts:

• T-SQL Query Processing Order
• How to Insert DBCC Output into MSSQL Table
• MSSQL: sp_xml_preparedocument is not supported in fibers mode
• MSSQL: Got arithmetic overflow for INT ?
• MSSQL 2005 Express Edition - unbeatable
• T-SQL: The Rozenshtein Method
• Always Use Stored Procedures in MSSQL
• MSSQL 2005 Service Pack 1 is available
• Enterprise Library: Bug with decimal in DataAccess block
• Microsoft Query Optimization Team is Blogging