Paging In SQL Server 2005

Πολλές φορές, όταν εκτελούμε SQL queries, θέλουμε να πάρουμε ένα ορισμένο υποσύνολο από τα δεδομένα που θα παίρναμε κανονικά. Για παράδειγμα, το query

SELECT * FROM products ORDER BY ProductID ASC

στην Northwind database (πρόκειται για μία test database της Microsoft, μπορείτε να την κατεβάσετε από εδώ) φέρνει 77 rows. Πολύ εύκολα μπορούμε να πάρουμε τα πρώτα X rows από ένα table, χρησιμοποιώντας την έκφραση TOP. Δηλαδή, το query

SELECT TOP 10 * FROM products ORDER BY ProductID ASC

θα μας φέρει τα πρώτα 10 rows. Τι κάνουμε, όμως, όταν θέλουμε να φέρουμε συγκεκριμένα rows (π.χ. από το 5ο ως το 10ο); Σε εκδόσεις του sql server πριν την 2005, μπορούσαμε να χρησιμοποιήσουμε την έφραση set rowcount = X, όπου περιορίζαμε στο maximum τα rows που μπορεί να μας φέρει ένα query. Στη έκδοση 2005 όμως, έχουμε την έκφραση ROW_NUMBER() OVER (ORDER BY FieldName ASC/DESC). Το query

SELECT ROW_NUMBER() OVER (ORDER BY ProductID ASC), products.* FROM products ORDER BY ProductID ASC

θα μας φέρει όλα τα rows από το table Products, με το κάθε ένα row να έχει σαν πρώτο πεδίο ένα rownumber, όπου αυτό είναι ένας ακέραιος που μας δείχνει έναν αύξοντα αριθμό για το row. Το ROW_NUMBER παίρνει μετά το OVER μια έκφραση που του υποδεικνύει με βάση πιο πεδίο να κάνει το sorting και συνεπώς να βάλει τα rownumbers. Με τη χρήση του ROW_NUMBER μπορούμε λοιπόν πολύ εύκολα να φτιάξουμε την κάτωθι stored procedure που θα μας φέρνει συγκεκριμένα subsets δεδομένων

CREATE PROCEDURE usp_GetpagedProducts

@PageSize int,

@TargetPage int

AS

BEGIN

WITH tmp_cte AS

(

SELECT ROW_NUMBER() OVER (ORDER BY ProductID ASC) As Row, Products.* FROM Products

)

SELECT * FROM tmp_cte

WHERE Row BETWEEN (@TargetPage 1) * @PageSize + 1 AND @TargetPage*@PageSize

END

Χρησιμοποιώντας common table expressions (CTE – περισσότερα για αυτά σε επόμενο blog postWink) κρατάμε ένα resultset στη μνήμη με όλα τα products μαζί με το rownumber που τους αντιστοιχεί, και στο τέλος πετυχαίνουμε το paging με την χρήση των παραμέτρων της stored procedure (@PageSize είναι ο αριθμός των rows που επιθυμούμε να μας φέρει και @TargetPage είναι το συγκεκριμένο subset που θέλουμε). Για παράδειγμα, κλήση της stored procedure ως εξής

usp_GetPagedProducts 6,1

θα μας φέρει τα πρώτα 6 rows (rownumber από 1 ως 6), ενώ κλήση της stored procedure ως εξής

usp_GetPagedProducts 6,3

θα μας φέρει τα ‘τρίτα’ 6 rows (rownumber από 13 ως 18).

 

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