It drives me up the wall that I can't use LIMIT. The only reason why I use MSSQL is because it's more widespread in corporate environment =\

in express you can easily achieve this with row_number function.

row_number is not near as easy to use as limit. Either from an orm or generated script perspective.

The ranking functions combined that top now can take a dynamic parameter helps a lot.

Some of the read/shared locks can also be eliminated from SQL Server by doing interesting things.

I think for a small web app and smaller databases, MySQL might be a good choice. If you want to grow this into anything bigger and use any MS add-ons ur going to be sorry for making that decision down the line.

Could you not accomplish the same thing as LIMIT 10 with TOP 10 (presuming you use the same order clause)?

http://authors.aspalliance.com/stevesmith/articles/sprowcount.asp

The usage of LIMIT has always been a big attraction for me as well.It makes everything so easy in doing paging, no need to define a page size or anything like that, just pass two numbers at the end of the query and you are ready to go. Speed (if you just need read) is as well a very good argument.
LIMIT It's not the same as using TOP 10 That gives you just the first ten results but them you will need the results from 11 to 20 and 21 to 30 etc. There is a way of using top and subqueries to filter the results and achieve a similar result but in that case use pages, it's better.

I agree, that LIMIT takes the cake, but it has issues namely it's crippled use inside of stored procedures. In addition MySQL has a lot of other issues, documented here: http://www.angryhacker.com/blog/archive/2007/09/16/mysql-eye-for-an-microsoft-sql-server-guy.aspx