MS SQL and MySQL : Use of LIMIT

LIMIT is not a standard. As a matter of fact, SQL:2008 has three ways of limiting results, FETCH FIRST, a Window function (ROW_NUMBER, for example), and a cursor. The OFFSET keyword is standardized in SQL:2008, however SQL Server 2005, and 2008 were both released before said standard was made. Actually, speaking of SQL standards, MySQL is terrible at following said standards. MS SQL, and Oracle for that matter, both implement standard SQL, and their own (T-SQL, and PL/SQL respectively).
There’s a reason why MS SQL is so commonly used in the corporate world. It’s stable, and very efficient. It doesn’t have LIMIT? Who cares, as pointed out, it’s fairly easy to get around. And LIMIT in MySQL would still have to go over every record anyways.
Hundreds of professional developers, and while they agree that MySQL is a nice DB for home use, it’s terrible for any corporate uses.

Quick example:
SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (ORDER BY name) as row FROM sys.databases ) a WHERE row > 5 and row <= 10

Advertisements

~ by UTS on April 27, 2009.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: