Developer Tip #Paging With SQL

ใครๆก็จะมีปัญหาเกี่ยวกับการ Query จำนวนแถวมากๆ มักเป็นปัญหาเสมอๆเพราะข้อมูลมันเยอะถ้าแค่ 5-10 แถวมันยังไม่มากแต่ล่อไปสัก 10000 แถวชักเริ่มปัญหาเกิดขึ้น เวลา Query ทั้งหมด
วันนี้ผมไปเจอมาเกี่ยวกับการทำ Paging สำหรับ SQL Server 2008 และ 2012 ครับ
ลองไปดูกันนะครับ
--VIEWING THE PAGE "2" WITH 5 ROWS
DECLARE @PageNumber AS INT, @RowspPage AS INTSET @PageNumber = 2SET @RowspPage = 5

SELECT * FROM (             SELECT ROW_NUMBER() OVER(ORDER BY ID_EXAMPLE) AS NUMBER,                    ID_EXAMPLE, NM_EXAMPLE, DT_CREATE FROM TB_EXAMPLE               ) AS TBLWHERE NUMBER BETWEEN ((@PageNumber - 1) * @RowspPage + 1) AND (@PageNumber * @RowspPage)ORDER BY ID_EXAMPLE

Query ข้างต้นสำหรับ SQL Server ที่ไม่ใช่ 2012 นะครับ (แต่ 2012 ก็ใช้ได้นะครับ)
แต่สำหรับเวอร์ชั่น 2012 จะมีเพิ่มขึ้นมาทำให้ง่ายขึ้นนิดหน่อยครับ
--CREATING A PAGING WITH OFFSET and FETCH clauses IN "SQL SERVER 2012"
DECLARE @PageNumber AS INT, @RowspPage AS INTSET @PageNumber = 2SET @RowspPage = 10 

SELECT ID_EXAMPLE, NM_EXAMPLE, DT_CREATEFROM TB_EXAMPLEORDER BY ID_EXAMPLEOFFSET ((@PageNumber - 1) * @RowspPage) ROWSFETCH NEXT @RowspPage ROWS ONLY;

สำหรับ Version 2012 ได้มี Offset เข้ามาทำให้การทำงานง่ายขึ้นอีกขั้นนึง และยังได้ประสิทธิภาพที่ดีกว่าการใช้ ROW_NUMBER อีกด้วย สามารถดูเพิ่มเติมได้ที่นี่ครับ
http://social.technet.microsoft.com/wiki/contents/articles/23811.paging-a-query-with-sql-server.aspx

ขอบคุณ
http://social.technet.microsoft.com/wiki/contents/articles/23811.paging-a-query-with-sql-server.aspx
ครับ

Developer TIP ตอน Split ใน Store procedure

สำหรับการพัฒนาโปรแกรม ติดต่อฐานข้อมูล Store Procedure เป็นสิ่งที่ขาดไม่ได้ สิ่งหนึ่งที่ปัญหาง่ายๆ แต่เป็นปัญหาคือการทำ IN ใน Store Procedure ที่มักจะเกิดปัญหาว่า มันทำไม่ได้ตามต้องการ ทีนี้จะทำยังไง วิธีการนึงที่ผมใช้คือการสร้างฟังก์ชัน Split เข้ามาใน User - Define Function โดยมีโค้ดดังนี้
CREATE FUNCTION [dbo].[fn_Split](@text varchar(8000), @delimiter varchar(20) = ' ')
RETURNS @Strings TABLE
(   
  position int IDENTITY PRIMARY KEY,
  value varchar(8000)  
)
AS
BEGIN

DECLARE @index int
SET @index = -1

WHILE (LEN(@text) > 0)
  BEGIN 
    SET @index = CHARINDEX(@delimiter , @text) 
    IF (@index = 0) AND (LEN(@text) > 0) 
      BEGIN  
        INSERT INTO @Strings VALUES (@text)
          BREAK 
      END 
    IF (@index > 1) 
      BEGIN  
        INSERT INTO @Strings VALUES (LEFT(@text, @index - 1))  
        SET @text = RIGHT(@text, (LEN(@text) - @index)) 
      END 
    ELSE
      SET @text = RIGHT(@text, (LEN(@text) - @index))
    END
  RETURN
END

โค้ดข้างต้นคือการสร้าง function สำหรับ Split ค่า String เพื่อให้สามารถใช้ใน IN Operation ในการ Query สำหรับ Stored Procedure ได้ 
การใช้งานกก็คือนำไปใส่ใน IN เท่านั้น ตัวอย่าง

CREATE PROCEDURE [dbo].[sp_TRANS]
@p_DateStart AS datetime,
@p_DateFinish As datetime,
@p_clstype as varchar(255)
AS
BEGIN
SELECT 
item_name,
transaction_date

 
FROM tb_buy_item

WHERE transaction_date BETWEEN @p_DateStart and @p_DateFinish
AND item_type IN (select value from fn_Split(@p_clstype, ',')) 
ORDER BY T.TRANS_ID ASC



END

หวังว่าจะเป็นประโยชน์กับ Developer ทุกท่านครับ