در توسینسو تدریس کنید

و

با دانش خود درآمد کسب کنید

روش های Pagination در SQL Server

دوستان عزیزی که کار برنامه نویسی، چه تحت وب و چه تحت ویندوز را انجام می دهند، به طور حتم با مفهوم صفحه بندی اطلاعات آشنایی دارند. فرض کنید جدولی دارید با نام Orders که حدود 10000 رکورد در آن ثبت شده است. حال شما می بایست اطلاعات این جدول را در خروجی نمایش دهید. اما آیا هر 10 هزار رکورد را به صورت یکجا در خروجی می بایست نمایش داد؟ به طور حتم اینگونه نیست و باید از مکانیز صفحه بندی یا Pagination استفاده کرد. در SQL Server به دو روش می توان کار صفحه بندی را انجام داد. یکی استفاده از تابع ROW_NUMBER که جزو Ranking Function ها قرار میگیرند و دیگری استفاده از مکانیزم OFFSET و FETCH است که در این مطلب به نحوه استفاده از این دو روش می پردازیم.


استفاده از تابع ROW_NUMBER


بوسیله این تابع می توان به هر سطر یک عدد اختصاص داد و بعد با یک Query مجدد و بر اساس شماره سطر کار صفحه بندی را انجام داد. در این روش، در حقیقت باید از Query های تو در تو استفاده کنیم. ساختار تابع ROW_NUMBER به صورت زیر است:

ROW_NUMBER() OVER (ORDER BY {column-name} [ASC/DESC])

نحوه اجرای دستور ROW_NUMBER به این صورت است که ابتدا عملیات مرتبط سازی بر روی ستونی که نام آن را در قسمت column-name مشخص کردیم و به صورت صعودی یا نزولی (کلمات کلیدی ASC یا DESC) انجام شده و به هر ستون یک شماره اختصاص داده می شود. حال می توان بر اساس این شما کار فیلتر اطلاعات را انجام داد. فرض کنیم که هر صفحه شامل 30 رکورد بوده و می خواهیم اطلاعات را در صفحه 35 ام را بدست آوریم. مثال را بر روی جدولی فرضی با نام Orders میزنیم:

declare @page int;
set @page = 35;
SELECT * FROM
	(select
		ROW_NUMBER() OVER (ORDER BY OrderDate DESC) [Rank],
		*
	FROM Orders) tbl
WHERE tbl.[Rank] BETWEEN ((@page-1)*30)+1 AND ((((@page-1)*30)) + 30)

در مثال بالا، اطلاعات بر اساس ستون OrderDate مرتب شده و سپس به هر سطر یک شماره اختصاص داده می شود. سپس با یک Query ای دیگر اطلاعات بر اساس شماره صفحه فیلتر می شوند. کافیست مقدار متغیر page بر اساس شماره صفحه تغییر کند.


استفاده از روش OFFSET و FETCH


این روش کار را نسبت به ROW_NUMBER ساده تر می کند. صفحه بندی بالا، بر اساس روش OFFSET و FETCH به صورت زیر نوشته می شود:

declare @page int;
set @page = 2;
select * from Orders
order by OrderDate DESC
offset (@page-1) * 30 ROWS fetch next 30 ROWS ONLY

همانطور که مشاهده می کنید، Query نوشته شده بسیار ساده تر از روش ROW_NUMBER می باشد. شما ابتدا Query را با عبارت ORDER BY مرتب سازی کرده، سپس با عبارت OFFSET مشخص می کنید که در نتیجه Query از چند سطر باید صرفنظر شود، سپس بوسیله عبارت fetch تعداد سطر های مورد نظر را برای دریافت مشخص می کنید. این قابلیت از نسخه 2012 به SQL Server اضافه شده و کار صفحه بندی نتایج Query ها را بسیار ساده تر می کند. ITPRO باشید

نویسنده: حسین احمدی

انجمن تخصصی فناوری اطلاعات ایران

#offset_و_fetch_در_sql_server #pagination_در_sql_server #تابع_row_number_در_sql_server #صفحه_بندی_query_ها_در_sql_server
6 نظر
prober

در MySQL روش خیلی ساده تری وجود داره گفتم اینم بگم واسه MySQL ای ها:

SELECT * FROM tbl LIMIT 5,10;  # Retrieve rows 6-15

در مثال بالا رکورد های 6 تا 15 به سادگی با دستور Limit انتخاب می شوند. LIMIT 5,10 یعنی از رکورد اندیس 5 (اندیس ها از صفر شروع می شوند) تا 10 تا بعد از آن

حسن ضرابی

با سلام

البته روش اولی که آقای مهندس حسین احمدی به آن اشاره داشتند خیلی خوب هست اما بیشتر برای sql server 2000 تا 2008 استفاده دارد اما روش دوم خیلی جدیدتر و بهتر هست

من یک سئوالی داشتم آیا اگر از روش اول که شما در مقاله بالا به آن اشاره کرده اید استفاده کنیم مثلا در sql server 2014 سرعت این select تفاوتی دارد با روش دوم؟

ممنون می شوم راهنمایی کنید.

ممنونم از مقاله بسیار خوبتون

با تشکر

حسین احمدی

بر اساس Execution Plan و statistics io استفاده از offset fetch نتایج بهتری داره. البته تفاوت در اون حدی نیست که بخواد زیاد به چشم بیاد، اما تو استخراج نتایج برای صفحه اول گزینه offset fetch خیلی بهینه تر هست که بیشتر کوئری ها هم برای نتایج صفحه اول تو برنامه ها استفاده میشه و مراجعه به سایر صفحات خیلی کمتر از صفحات اول هست.

حسن ضرابی

با سلام خدمت آقای مهندس حسین احمدی

آقای مهندس ببخشید یک سئوال داشتم چرا توی صفحات اول offset fetch خیلی بهینه تر عمل می کند دلیلش چیست؟

ممنونم که پاسخ می دهید

با تشکر از شما

حسین احمدی

کاری که ROW NUMBER انجام میده بر اساس ستونی که در order by مشخص شده مرتب سازی انجام میشه و به هر رکورد یک شماره تخصیص داده میشه و بعد با دستور where عملیات فیلتر بر اساس شماره رکورد انجام میشه، اما در fetch offset روند شماره گذاری رکورد ها رو نداریم. البته یک نکته ای رو بهتون بگم، خیلی مهمه که در بخش order by در حالت fetch offset حتماً بر روی ستونی که مرتب سازی بر روی اون انجام میشه ایندکس داشته باشید و البته ستون هایی هم که در خروجی کوئری شما نمایش داده می شوند include شده باشن داخل ایندکس.

حسن ضرابی

با سلام

با تشکر از جواب شما

نظر شما
برای ارسال نظر باید وارد شوید.
از سرتاسر توسینسو
تنظیمات حریم خصوصی
تائید صرفنظر
×

تو می تونی بهترین نتیجه رو تضمینی با بهترین های ایران بدست بیاری ، پس مقایسه کن و بعد خرید کن : فقط توی جشنواره تابستانه می تونی امروز ارزونتر از فردا خرید کنی ....