CODEWITHSUNDEEP
sql-serversql-server-2008pagination
RSK
RSK

Reputation: 17516

pagination in SQL server 2008

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.

Upvotes: 0

Views: 1842

Answers (3)

marc_s
marc_s

Reputation: 755451

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;

Upvotes: 3

Jonathan Wood
Jonathan Wood

Reputation: 67345

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!

Upvotes: 1

Mitch Wheat
Mitch Wheat

Reputation: 300797

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

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

Upvotes: 1

Related Questions