MySQL “LIMIT” Equivalent in SQL Server 2005
First of all, there is no direct replacement of MySQL “LIMIT” in SQL Server. Let’s have a look at syntax for “LIMIT” in MySQL Server.
SELECT * FROM [TABLE NAME] ORDER BY [ORDER BY COLUMN NAME] LIMIT 10 OFFSET 100
This query will select 101 to 110 th rows of data in MySQL. In SQL Server "TOP" can be used to select fixed number of rows from a Table or View.
SELECT TOP 10 * FROM [TABLE NAME] ORDER BY [ORDER BY COLUMN NAME] SELECT TOP 10 PERCENT * FROM [TABLE NAME] ORDER BY [ORDER BY COLUMN NAME]
TOP 10 PERCENT will dynamically calculate number of rows to be selected.
Again, there is no direct replacement of "OFFSET" in SQL Server. However, you can have something like "LIMIT" and "OFFSET" by querying.
SELECT [COLUMN NAME 1], [COLUMN NAME 2], [COLUMN NAME 3] FROM ( SELECT *, RANK() OVER (ORDER BY [ORDER BY COLUMN NAME]) As "Serial" FROM [TABLE NAME] ) WHERE Serial BETWEEN [MINIMUM VALUE] AND [MAXIMUM VALUE]
RANK() or ROW_NUMBER() is used to generate row number of a selected record set in SQL Server. If you are not familiar with this you can have look at my posting on Generating Row Number in SQL Server 2005.
A Rahim Khan