Soci (Soczó Zsolt) szakmai blogja

2012.04.18.

SQL Server 2012 újdonságok – 3. Paging

Filed under: Adatbázisok,SQL Server,SQL Server 2012,Szakmai élet — Soczó Zsolt @ 09:14

A legtöbb alkalmazás tartalmaz olyan funkciót, amelyekben rendezett, nagy eredményhalmazokat kell kisebb darabokban, lapozva megjeleníteni. Gondoljunk pl. a keresés funkcióra, amikben ha sok eredmény jönne vissza, nem zúdítjuk azt az alkalmazás nyakába, csak megmutatjuk az első például 10 eredményt, majd lehetőséget biztosítunk a felhasználóknak, hogy megnézhessék a 11-20. eredményt, stb.
Általában ezt nagy eredményhalmaz egy kis részének megjelenítését hívják lapozási feladatnak. Korábbi SQL Server verziókban erre többféle megoldást is használtunk. Lehetett alkalmazni szerveroldali kurzorokat, trükkös egymásba ágyazott ORDER BY-os és TOP-os lekérdezéseket, SQL Server 2005-től a ROW_NUMBER sorszámgeneráló függvényt WHERE megállási feltétellel kombinálva, ínyenceknek CTE-be csomagolva. Ahogy haladtunk előre az újabb SQL Server verziók felé, úgy lett egyre egyszerűbb ilyen lekérdezéseket írni.
Az SQL Server 2012-re kialakult az a formátum, aminél már valószínűleg nem lehet tovább egyszerűsíteni a feladat megoldását.
Az ORDER BY kiegészült a lapozást lehetővé tevő új elemekkel: ORDER BY OFFSET n ROWS és FETCH NEXT n ROWS ONLY. A n érték lehet változó vagy paraméter is, így paraméterezett tárolt eljárásokban is kényelmesen használható a lapozás.
Például az AdventureWorks2012 adatbázis Person táblájából ha leválogatjuk az A betűvel kezdődő keresztnevű személyeket, akkor 2014 sort kapunk vissza:

SELECT FirstName, LastName, BusinessEntityID
FROM [AdventureWorks2012].[Person].[Person]
where FirstName like 'A%'
order by FirstName, LastName;

Az első 33 sor:

Ennyit nem biztos, hogy a felhasználók nyakába szeretnénk önteni, de könnyedén visszaadhatjuk lapozva is az eredményeket:

SELECT FirstName, LastName, BusinessEntityID
FROM [AdventureWorks2012].[Person].[Person]
where FirstName like 'A%'
order by FirstName, LastName
offset 20 rows
fetch next 10 rows only;

A lapozott eredményhalmaz:

Látható, hogy Aaron Foster található az első sorban, ő volt a 21. az eredeti eredményhalmazban.
A lekérdezés végrehajtási terve közel azonos egy TOP operátoros lekérdezés végrehajtási tervével (ami természetes, hisz egy top művelet van a háttérben, csak előtte van egy skip is), így a már ismert elvek alapján indexekkel könnyen optimalizálható a SELECT végrehajtása.

4 Comments

  1. Vajon miért ilyen furán oldották meg a szintaxist? (Nem csak ennél, hanem más új, SQL 2012-es funkcióknál is.)
    Miért kell a “rows”, mikor az eddigi “top n” is “rows”-t jelentett? Miért “fetch next”, mikor az eddig a FOR ciklusé volt? Miért “only” – ha nem “only”, akkor mi?
    Nekem sokkal szimpatikusabb lett volna egy olyan, hogy “select offset n top m”, vagy valami efféle.

    Comment by rlaci — 2012.04.22. @ 14:15

  2. A válasz elég egyszerű: mert így van a szabványban (asszem SQL 2009, sajnos a szabványok pénzesek, ezért nincs másolatom belőlük.)
    De látsz majd még sokkal cifrábbakat is. :)

    Comment by Soczó Zsolt — 2012.04.23. @ 07:44

  3. Egyébként a Windowing Functions részben majd kiderül, hogy a ROWS-nak nagy jelentősége van, a TIE-ok miatt (amikor nem egyértelmű az order by).

    Comment by Soczó Zsolt — 2012.04.23. @ 19:27

  4. Mary…

    Soci blog » Blog Archive » SQL Server 2012 újdonságok – 3. Paging…

    Trackback by Mary — 2012.10.17. @ 19:39

RSS feed for comments on this post.

Sorry, the comment form is closed at this time.

Powered by WordPress