Could you hire me? Contact me if you like what I’ve done in this article and think I can create value for your company with my skills.

April 18, 2012 / by Zsolt Soczó

SQL Server 2012 újdonságok – 3. Paging

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.

Could you hire me? Contact me if you like what I’ve done in this article and think I can create value for your company with my skills.

LEAVE A COMMENT

4 COMMENTS

  • rlaci April 22, 2012

    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.

  • Soczó Zsolt April 23, 2012

    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. :)

  • Soczó Zsolt April 23, 2012

    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).

  • Pingback: Mary