SELECT TOP (100) - syntax not working on SQL 2000 - by Perels

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 325703 Comments
Status Closed Workarounds
Type Bug Repros 0
Opened 1/31/2008 9:06:30 AM
Access Restriction Public


We are using RedGate SQL Compare to compare to databases - a SQL 2005 against a SQL 2000 database.

This syntax is not valid in SQL 2000
"SELECT TOP (100)"
you must use
"SELECT TOP 100"  <- with out the paranthesis.
Sign in to post a comment.
Posted by Umachandar [MSFT] on 1/31/2008 at 6:20 PM
The new TOP clause syntax introduced in SQL Server 2005 has richer functionality and can be used in INSERT/UPDATE/DELETE statements also. From SQL Server 2005, we also recommend that you move away from the older syntax to the new syntax. This is mentioned in the SELECT statement topic in SQL Server Books Online documentation.
Please see the examples below for the new TOP clause features:

-- Use a variable to specify row count
DECLARE @n int;
SET @n = 10;
SELECT TOP(@n) * FROM pubs.dbo.authors ORDER BY au_id;

-- Use arbitrary expression
SELECT TOP(cast(RAND()*10 as int)+1) * FROM pubs.dbo.authors;

-- Use a query expression to specify the row count
DECLARE @n int;
                 FROM pubs.dbo.sales
                 GROUP BY ord_date
                 ORDER BY count(*) DESC) *
FROM pubs.dbo.sales
ORDER BY ord_date desc;

-- Delete any 10 rows from a table

Adding this functionality in SQL Server 2005, included new query plans to support the TOP clause apart from the syntax itself. It is not possible to add such functionality in a service pack for an older product.
For your problem, in the short-term until you migrate all of your database code entirely to SQL Server 2005 you may want to use the older syntax in SQL Server 2005 also. This will allow you to compare code between SQL Server 2000 and SQL Server 2005. This also means you can use any of the newer capabilities though.

SQL Server Engine Team