Home Dashboard Directory Help
Search

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


Status: 

Closed
 as Won't Fix Help for as Won't Fix


0
0
Sign in
to vote
Type: Bug
ID: 325703
Opened: 1/31/2008 9:06:30 AM
Access Restriction: Public
0
Workaround(s)
view
0
User(s) can reproduce this bug

Description

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.
Details
Sign in to post a comment.
Posted by Microsoft on 1/31/2008 at 6:20 PM
Hi,
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;
SELECT TOP( SELECT TOP(1) count(*)
                 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
DELETE TOP(10) FROM tbl;

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.

Thanks
SQL Server Engine Team
Sign in to post a workaround.