Nice to have feature

If you ask ANY developer that has worked with both MySQL and MS SQL, every single one of them will say why hasn't microsoft given MS SQL the capability to do LIMITS as such in MySQL. I'm not impressed by any of the stupid work arounds either... they run heavy and crapout under a large record load.

Of course there are several other attributes that MS SQL has that MySQL can't offer, but again, MySQL is FREE and PAID tools should have all that a free competitor has plus some.

Is there any planned action to remedy this absurd oversight?

Could you please post a suggestion for this on http://connect.microsoft.com/sqlserver? Suggestions (and defect reports) filed on the Connect site go directly into our internal issue tracking system so we don't lose them. Suggestions from customers carry extra weight with the development team when we are prioritizing future work.

I'm afraid I haven't used MySQL, so I have no idea what "LIMIT" does. Is "LIMIT" like the ANSI syntax "SELECT TOP n ..."? If you could explain what you're looking for in terms of functionality, I might be able to help you out.

Thanks,
Steve

|||

Thank you for the url to post this fix suggestion, i will do that.

A limit is also known as a 'Range Result'. A 'must have' for pagination in any sort of program/database development to decrease the need to process, handle and bandwidth requirements to handle unnecessary large datasets. (As produced by 'TOP n' queries) Especially when dealing with hundreds of thousands of records.

Definition: Limit is used to limit your MySQL query results to those that fall within a specified range. You can use it to show the first X number of results, or to show a range from X - Y results. It is phrased as Limit X, Y and included at the end of your query. X is the starting point (remember the first record is 0) and Y is the duration (how many records to display).

SELECT * FROM `your_table` LIMIT 0, 10

This will display the first 10 results from the database.

SELECT * FROM `your_table` LIMIT 5, 5

This will show records 6, 7, 8, 9, and 10

I highly recommend that everyone trying out other SQL servers to not get stuck in the MS paradigm.

Let me know if you have any other questions regarding LIMITS.

|||

you have to roll your own sproc logic to do that in SQL Server.

That would be a very nice to have feature for future releases.

MySQL definately has some nice advantages.

Cheers,

|||

Have you looked at the new ROW_NUMBER syntax in SQL Server 2005?

USE AdventureWorks;
GO
WITH OrderedOrders AS
(
SELECT SalesOrderID, OrderDate,
ROW_NUMBER() OVER (ORDER BY OrderDate) AS 'RowNumber'
FROM Sales.SalesOrderHeader
)
SELECT *
FROM OrderedOrders
WHERE RowNumber BETWEEN 50 AND 60;

http://msdn2.microsoft.com/en-us/library/ms186734.aspx

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/

|||true... that is a work around for a STORED PROC. however, i'm not super interested in stored procs and again, it's a work around.|||Its a workaround until you get a straight solution |||

Actually, no... this isn't a stored proc. All of that code can be performed inline as ad-hoc SQL. I believe the term for this is a "Common Table Expression". You can do other cool stuff with it like recursive queries.

WITH common_table_expression (Transact-SQL):
http://msdn2.microsoft.com/en-us/library/ms175972.aspx

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/