www.dntips.ir
مطالب ۳۶۹۴
نویسندگان ۲۷۶
گروه‌های مطالب ۱۰۲۴
نقشه‌های راه ۱۱۹
دوره‌ها ۱۴
اشتراک‌ها ۱۷۹۱۴
عنوان:

‫اعمال صفحه بندی به کمک OFFSET و FETCH در SQL Server 2012


نویسنده: فرهاد فرهمندخواه
تاریخ: ۱۳۹۱/۰۹/۱۵ ۱۶:۱۵
آدرس: www.dntips.ir
در T-SQL 2012 قابلیت صفحه بندی، نمایش خروجی یک Query فراهم گردیده است، که برای نرم افزارهای تحت وب بسیار پرکاربرد میباشد، به عنوان مثال، از جمله کاربردهای بارز آن، می‌توان به نمایش نتیجه یک جستجو بصورت صفحه بندی با تعداد رکورد محدود،اشاره نمود.
 مایکروسافت برای ایجاد قابلیت صفحه بندی و محدود نمودن نمایش خروجی یک Query، تغییراتی را در Syntax مربوط به Order by ایجاد نموده است، که در ذیل مشاهده می‌نمایید:
ORDER BY order_by_expression
    [ COLLATE collation_name ] 
    [ ASC | DESC ] 
    [ ,...n ] 
[ <offset_fetch> ]


<offset_fetch> ::=
{ 
    OFFSET { integer_constant | offset_row_count_expression } { ROW | ROWS }
    [
      FETCH { FIRST | NEXT } {integer_constant | fetch_row_count_expression } { ROW | ROWS } ONLY
    ]
}
OFFSET (نقطه شروع) :  شامل یک پارامتر است،بطوریکه،پارامتر فوق می‌تواند یک عدد (integer_constant) یا یک عبارت (offset_row_count_expression) بپذیرد. در اینجا منظور از عبارت می‌تواند یک Subquery باشد، که خروجی آن فقط یک مقدار عددی است. یا یک متغیر و غیرو... 
در مورد ROW یا ROWS باید بگویم باهم فرقی ندارند.
FETCH : همانند OFFSET شامل یک پارامتر است، و پارامتر آن می‌تواند یک عدد یا عبارت بپذیرد.
Next یا First نیز با هم تفاوتی ندارند و جهت سازگاری با ANSI می‌باشند.
OFFSET : در وافع تعداد سطر قابل حذف، پیش از نمایش اولین سطر در خروجی را بیان می‌کند.
FETCH : بیانگر تعداد رکورد قابل نمایش در یک صفحه می‌باشد.
برای درک بیشتر مثالی می‌زنیم:
ابتدا بوسیله Script زیر یک جدول ایجاد می‌نماییم، سپس چند رکورد درون آن درج می‌کنیم:
Create Table Testoffset
(BusinessEntityID int,
FirstName varchar(100) ,
LastName varchar(100)
);

Insert into Testoffset (BusinessEntityID,FirstName,LastName)
            Values(1,'Ken','Sánchez')
    ,(2,'Terri','Duffy')
,(3,'Roberto','Tamburello')
,(4,'Rob','Walters')
,(5,'Gail','Erickson')
,(6,'Jossef','Goldberg')
,(7,'Dylan','Miller')
,(8,'Diane','Margheim')
,(9,'Gigi','Matthew')
,(10,'Michael','Raheem')
در ادامه Script زیر را اجرا نمایید، تا تعداد رکورد‌های درج شده را مشاهده کنید:

در شکل، سه سطر (منظور رکورد 4و5و6) در کادر قرمز رنگ دیده می‌شود، می‌خواهیم Script ی ایجاد نماییم، که فقط سه سطر فوق را نمایش دهد. بنابراین خواهیم داشت:
SELECT BusinessEntityID, FirstName, LastName
FROM Testoffset
ORDER BY BusinessEntityID
OFFSET 3 ROWS
FETCH First 3 ROWS only
خروجی:

اگر به Query اجرا شده دقت کنیم. در قسمت Order By جلوی Offset مقدار 3 اختصاص داده شده بود، یعنی نقطه شروع از سطر چهارم میباشد، به عبارت دیگر مقداری که به Offset اختصاص داده می‌شود، به SQL Server می‌فهماند،چه تعداد رکورد را نمایش ندهد. اگر شکل اول و دوم را با هم مقایسه نمایید، براحتی متوجه می‌شوید که OFFSET نقطه شروع را مشخص کرده است.
مقداریکه برای Fetch در نظر گرفته شده بود برابر 3 است، که بیانگر تعداد سطر نمایش داده شده در خروجی از نقطه آغازین (offset) می‌باشد.
امیدوارم مفید واقع شده باشد.
مطالب مشابه

نظرات

  • Ara در ۱۳۹۱/۰۹/۲۸ ۲۲:۳۴
    بعضی وقتها مایکروسافت یکسری کارها رو اونقدر سخت می‌کنه !

    paging  هم یکی از همون‌ها بود تازه داره پخته می‌شه

    این syntax من رو یاد mysql  انداخت 

    SELECT column FROM table
LIMIT 10 OFFSET 10

  • سعید شیرزادیان در ۱۳۹۱/۰۹/۲۹ ۰:۱۰
    سلام
    می‌خواستم بدونم امکان هست مرتب سازی را به صورت پارامتری ارسال کنیم. یعنی می‌خواهم به نوعی صفحه بندی را در وب همراه با مرتب سازی دلخواه کاربر انجام بدهم. متشکرم
    • فرهاد فرهمندخواه در ۱۳۹۱/۰۹/۲۹ ۸:۵
      سلام  
      من منظور سئوال شما رو،بدرستی متوجه نشدم، به هرحال اگر بخواهید با کد نویسی سمت سرور، Script خود را Generate نمایید، اینکار، بستگی به نگرش کدنویسی تان و Interface ی که در اختیار کاربر قرار می‌دهید، دارد.
      اگر بخواهید در SQL Server اینکار را انجام دهید، با استفاده از Case ، در قسمت Order By می‌توانید اینکار را انجام دهید. به عنوان مثال:
      DECLARE @Varsort varchar(50)
DECLARE @Varsort1 varchar(50)

SET  @Varsort=''
SET  @Varsort1='BusinessEntityID'

SELECT BusinessEntityID, FirstName, LastName
FROM Testoffset
ORDER BY case when  @Varsort='Firstname'then  Firstname End ASC,
         case when  @Varsort1= 'BusinessEntityID'then  BusinessEntityID End ASC
OFFSET 3 ROWS
FETCH First 3 ROWS only
      امیدوارم پاسخ تان را گرفته باشید.
      • سعید شیرزادیان در ۱۳۹۱/۰۹/۲۹ ۲۰:۴۸
        سلام
        ممنون از جواب شما. فقط اگر تعداد ستونها بیشتر بود و یا اینکه کاربر درخواست مرتب سازی بصورت صعودی و نزولی را هم داشت (در این مثال شما به صورت پیش فرض مرتب سازی صعودی را اعمال کردید) راهکاری دارید؟
        با تشکر
        • مهمان در ۱۳۹۱/۰۹/۳۰ ۶:۵۶
          سلام  
          دوست عزیز، مثالی که برای شما زده شد، امکان سورت دلخواه را فراهم می‌کند، شما می‌توانید، هر کدام را به دلخواه نزولی یا صعودی نمایید، محدودیتی ندارد، برای مطالعه بیشتر می‌توانید به آدرس زیر مراجعه نمایید:
          یادآور شوم،چنانچه Performance برای شما اهمیت دارد، بهتر است از Case در Order by استفاده ننمایید، و بهتر است در زمان Run Time از طریق کد نویسی سمت سرور، Script خود را Generate نمایید، به عنوان مثال
          SELECT BusinessEntityID, FirstName, LastName
FROM Testoffset
ORDER BY BusinessEntityID Desc,Firstname ASC
OFFSET 3 ROWS
FETCH First 3 ROWS only
          موفق باشید.