0

I am a newbie to SQL server. keeping this question as reference.My doubt is
why Microsoft Sql server doesn't have something like limit in Mysql and now they are forcing to write either SP or inner query for pagination.I think creating a temporary view/table or using a inner query will be slower than a simple query.And i believe that there will be a strong reason for deprecating this. I like to know the reason.

If anyone know it please share it.

Community
  • 1
  • 1
RSK
  • 17,210
  • 13
  • 54
  • 74
  • 2
    Although this has bothered me for a long time, I wasn't even aware TOP could ever be used to set the start row. Can you say where you saw this and where you saw is was deprecated? – Jonathan Wood Dec 11 '10 at 08:08
  • Thankz for the correction. As i told i am a newbie to SQL server that syntax is given by one of my colleague.And i posted it with out enough googling.sorry for that.So i changed the Question to "why Microsoft Sql server doesn't have something like limit in Mysql" from why "Microsoft deprecated top 10,20 syntax". Hope now the question is fine. – RSK Dec 11 '10 at 10:05

3 Answers3

3

I never knew SQL Server supported something like TOP 10,20 - are you really totally sure?? Wasn't that some other system maybe??

Anyway: SQL Server 2011 (code-named "Denali") will be adding more support for this when it comes out by the end of 2011 or so.

The ORDER BY clause will get new additional keywords OFFSET and FETCH - read more about them here on MSDN.

You'll be able to write statements like:

-- Specifying variables for OFFSET and FETCH values  
DECLARE @StartingRowNumber INT = 150, @FetchRows INT = 50;

SELECT 
    DepartmentID, Name, GroupName
FROM 
    HumanResources.Department
ORDER BY 
    DepartmentID ASC 
    OFFSET @StartingRowNumber ROWS 
    FETCH NEXT @FetchRows ROWS ONLY;
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • Most excellent news! I'm really getting sick of writing overly complex queries just because I don't want all the results. (BTW, I'm not a SQL expert, but I was also surprised to see TOP 10,20 syntax mentioned.) – Jonathan Wood Dec 11 '10 at 15:47
1

SQL Server 2005 Paging – The Holy Grail (requires free registration).

(Although it says SQL Server 2005 it is still applicable to SQL Server 2008)

Mitch Wheat
  • 295,962
  • 43
  • 465
  • 541
  • Might be nice if you provided a clue as to what the article said. Not everyone wants to give every site they read an article on their email address and yet another password. – Jonathan Wood Dec 11 '10 at 15:45
1

I agree 100%! MySQL has the LIMIT clause that makes a very easy syntax to return a range of rows.

I don't know for sure that temporary table syntax is slower because SQL Server may be able to make some optimizations. However, a LIMIT clause would be far easier to type. And I would expect there would be more opportunities for optimization too.

I brought this once before, and the group I was talking to just didn't seem to agree.

As far as I'm concerned, there is no reason not to have a LIMIT clause (or equivalent), and I strongly suspect SQL Server eventually will!

Jonathan Wood
  • 65,341
  • 71
  • 269
  • 466