Hot File

SQL Server 2005 Paging Results

View: 684    Dowload: 0   Comment: 0   Post by: hanhga  
Author: none   Category: SQL Server   Fields: Computers - Technology

0 point/3 review File has been tested

With SQL Server 2005, it is now a lot easier to use paged queries than in previous versions. I will be using the NorthWind database (mostly), so you can also use the examples I have provided. I will keep the examples simple; anything complex will only cause confusion. I will start with "traditional" methods such as SELECT, TOP, and then move on to the specific SQL Server 2005 paging examples.

Introduction

Background

I was asked a question or a series of questions, "How would you do paging in a SQL? How would you do this with a lot of records, say, 10,000 or more?"

I thought about answers. To be more precise, I thought of more questions and this got me thinking, "This must be a common problem, every developer must have done or solved this. What about paging sizes and working with very large data sets? What about getting results from multiple tables?"

So, I decided to look into these questions with specific reference to SQL Server 2005. The following is by far the easiest way and should be used, but it is rarely this easy.

select * from mytable
Where ID between 20 and 30

SQL Top

SQL Top (returns records from the TOP of the result set) is very good at returning a set number of records from each end of a results set. The example below gets the top 10 customers by order qty. This is a very common question on forums. TOP can also pull a percentage of records, although this isn't discussed here.

select top 10 * from customers -- This is a very basic example.
select TOP 10 Customers.CustomerID, Customers.CompanyName, count(*) OrderCount
from Customers inner join Orders on Orders.CustomerID = Customers.CustomerID
GROUP BY Customers.CustomerID, Customers.CompanyName
ORDER BY OrderCount DESC

This is really useful. When you want to pull records 11 to 20 you could use temp tables.

-- SELECT First 30 records in to Temp table
SELECT TOP 30 * INTO
#TEMP
from Customers
ORDER BY CompanyName ASC

--Select Bottom 10 records in another temp table

SELECT TOP 10 * 
INTO #TEMP2
from #Temp
ORDER BY CompanyName DESC

-- GET THE RECORDS

SELECT * FROM #TEMP2

This is fine for the first few pages or the first few users. If you have users that want to return page after page after page, you end up getting 1000 records to return 10, which is not very efficient. You could also have placed an identity on the first temp table and used a SELECT statement as an alternative.

Alternative to TOP

There is an alternative to TOP, which is to use rowcount. Use rowcount with care, as it can lead you into all sorts of problems if it's not turned off.

SET rowcount 10
SELECT * from Customers
ORDER BY CompanyName

WITH, ROW_NUMBER and OVER

This is new to SQL Server 2005 and looks really useful. Below is an example to get records 20 to 29 from a results set. It might a bit strange at first, but I will go through the query so you'll see how simple it is.

With Cust AS
    ( SELECT CustomerID, CompanyName,
    ROW_NUMBER() OVER (order by CompanyName) as RowNumber 
    FROM Customers )
select *
from Cust
Where RowNumber Between 20 and 30

The WITH in SQL Server 2005 specifies a temporary named result, much like a temporary table in previous versions of SQL Server. However, the import parts are the ROW_NUMBER and the OVER statement, which create a row number on each row based on the Company name. This is like adding an identity seed to a temp table with an order by clause.

I hope you are still with me. If not, run the code and view the results. This is really very quick for large tables; I have been impressed with the speed on tables with over 250,000 records.

Putting it All Together in a Stored Procedure

Now we will put it all together in a Stored Procedure that can be used by your application. I won't show a .NET datagrid or similar control, as that is outside the scope of this article. The stored procedure below uses flexible page sizes and page numbers, so you can select any page at random. This is quite useful if you wish to jump ahead 10 pages to find a particular record. The paging for this example starts at page 1 rather than at page 0, but this can be easily changed.

CREATE PROC GetCustomersByPage

@PageSize int, @PageNumber int 

AS 

Declare @RowStart int 
Declare @RowEnd int 

if @PageNumber > 0 
Begin 

SET @PageNumber = @PageNumber -1 

SET @RowStart = @PageSize * @PageNumber + 1; 
SET @RowEnd = @RowStart + @PageSize - 1 ; 

With Cust AS 
     ( SELECT CustomerID, CompanyName, 
       ROW_NUMBER() OVER (order by CompanyName) as RowNumber 
       FROM Customers ) 

select * 
from Cust 
Where RowNumber >= @RowStart and RowNumber <= @RowEnd end

END

To run this, simply specify the page size and page number (GetCustomersByPage, @PageSize and @PageNumber) as shown below.

exec GetCustomersByPage 10, 1

SQL Server 2005 Paging Results

SQL Server 2005 Paging Results Posted on 09-04-2014  With SQL Server 2005, it is now a lot easier to use paged queries than in previous versions. I will be using the NorthWind database (mostly), so you can also use the examples I have provided. I will keep the examples simple; anything complex will only cause confusion. I will start with "traditional" methods such as SELECT, TOP, and then move on to the specific SQL Server 2005 paging examples. 5/10 684

Comment:

To comment you must be logged in members.

Files with category

  • Insufficient access rights error when doing backup in SQL Server

    View: 421    Download: 0   Comment: 0   Author: none  

    Insufficient access rights error when doing backup in SQL Server

    Category: SQL Server
    Fields: none

    2.25/2 review
    All of sudden SQL Server Management studio gives me the following error message when I'm trying to browse for the backup file location.

  • Migrate MySQL to Microsoft SQL Server

    View: 2493    Download: 0   Comment: 0   Author: none  

    Migrate MySQL to Microsoft SQL Server

    Category: SQL Server
    Fields: none

    0/2 review
    This article describes a few simple steps in order to migrate MySQL into Microsoft SQL Server 2005. The technique is very easy, but useful if you plan to move your data from MySQL and upgrade it finally to a Microsoft SQL Server environment.

  • COUNT of DISTINCT Rows in SQL Server

    View: 1335    Download: 0   Comment: 0   Author: none  

    COUNT of DISTINCT Rows in SQL Server

    Category: SQL Server
    Fields: Other

    0/4 review
    SQL Server does not support COUNT(DISTINCT *). For example, the below query fails.

  • Finding SQL Servers on the Network

    View: 675    Download: 0   Comment: 0   Author: none  

    Finding SQL Servers on the Network

    Category: SQL Server
    Fields: Other

    0/2 review
    I am a TSQL fanatic. The programs I code are highly dependent upon MS SQL stored procedures. I pay for this love of TSQL when a major overhaul of the system is necessary. Sometimes my code needs to be updated heavily in two places; client and...

  • SQL Server and Database Enumerator

    View: 654    Download: 0   Comment: 0   Author: none  

    SQL Server and Database Enumerator

    Category: SQL Server
    Fields: Other

    0/1 review
    This sample helps you to enumerate the list of SQL Servers and the databases and languages that a particular SQL server has. The class CSQLInfoEnumerator encapsulates this functionality into it.

  • Dynamic Management Views [DMV] – A SQL Server 2005 Feature

    View: 669    Download: 0   Comment: 0   Author: none  

    Dynamic Management Views [DMV] – A SQL Server 2005 Feature

    Category: SQL Server
    Fields: Other

    0/2 review
    The DMVs; newly introduced in SQL Server 2005 gives the database administrator information about the current state of the SQL Server machine. These values will help the administrator to diagnose problems and tune the server for optimal performance....

  • Table Value Parameter in SQL Server 2008

    View: 588    Download: 0   Comment: 0   Author: none  

    Table Value Parameter in SQL Server 2008

    Category: SQL Server
    Fields: Computers - Technology

    0/1 review
    One of the fantastic new features of SQL Server 2008 is the Table value parameter. In previous versions of SQL Server, there wasn’t a native way to pass a table to a Stored Procedure or functions. The usual workaround was to pass a large varchar or...

  • SQL Server DO's and DONT's

    View: 574    Download: 0   Comment: 0   Author: none  

    SQL Server DO's and DONT's

    Category: SQL Server
    Fields: Computers - Technology

    0/0 review
    So, you are now the leader of a SQL Server based project and this is your first one, perhaps migrating from Access. Or maybe you have performance problems with your SQL Server and don't know what to do next. Or maybe you simply want to know of some...

 
Newsletter Email

File suggestion for you

File top downloads

logo codetitle
Codetitle.com - library source code to share, download the file to the community
Copyright © 2015. All rights reserved. codetitle.com Develope by Vinagon .Ltd