การสร้าง User Defined Function ใน Microsoft SQL Server
การสร้าง User Defined Function ใน Microsoft SQL Server
ผู้เขียนค่อนข้างแปลกใจเมื่องาน SQL Server Community Thailand ครั้งที่ 12 ซึ่งเป็นครั้งแรก ที่จัดแบบ Live ตามสถานการณ์ COVID-19)ผู้เขียนแสดงตัวอย่างเกี่ยวกับการสร้าง User Defined Function ให้กับผู้เข้างานดู มีคน CHAT มาบอกว่าไม่เคยรู้มาก่อนเลยว่า Microsoft SQL Server สามารถสร้าง Function ได้ รู้แต่ว่าสามารถสร้าง Procedure ได้เพียงเท่านั้น
และเนื่องจากผู้เขียนได้เขียนถึง Stored Procedure ไปในครั้งก่อน เลยเป็นโอกาสต่อเนื่องที่จะเขียนถึง Function กันบ้างเพื่อไม่ให้น้อยหน้ากัน
ก่อนอื่นทั้ง Procedure และ Function ก็เป็นโปรแกรมย่อยกันทั้งคู่ และจะถูกสร้างเป็น Database Object ในฐานข้อมูล
ผู้ใช้ที่ได้รับอนุญาตจะสามารถใช้งานได้ เช่นเดียวกับบทความ Stored Procedure ที่ไม่ลงรายละเอียดการสร้าง Step-by-Step เป็นบทความเชิงวิจารณ์เสียมากกว่า
หากผู้อ่านสนใจสามารถอ่าน Book Online จากทาง Microsoft หรือสะดวกฝึกอบรมหลักสูตร Microsoft SQL Server Database Development ผู้เขียนก็ยินดีต้อนรับครับ
ความต่างสำคัญระหว่าง Stored Procedure และ Function
- Function ไม่สามารถใช้ในการปรับปรุงข้อมูลได้ ดังนั้นจึงไม่สามารถมีคำสั่ง INSERT, UPDATE, DELETE หรือ MERGE ได้ แต่บน Stored Procedure ทำได้สบายมาก
- ไม่สามารถทำ Error Handling ใน Function ได้ หากเกิด Error ก็คือหยุดทำงาน แต่กับ Procedure สามารถดัก Error และเขียนโค้ดตอบสนองต่อข้อผิดพลาดว่าจะให้ทำอะไรได้
- Function ถูกออกแบบมาให้ RETURN ค่าผ่านตัวเองออกมาอยู่แล้ว ดังนั้นจึงสามารถเขียนร่วมใน Queryได้ผ่านฉลุย แต่กับ Procedure ก่อน RETURN ค่าออกถึงแม้สามารถสร้าง Output Parameter ได้ แต่การจะเอาผลจาก Output Parameter ไปใช้ ต้องสร้างตัวแปรมารับค่า แล้วถึงเอาตัวแปรนั้นไปใช้ร่วมกับคิวรี่ต่อ หลายซับหลายซ้อนจริง
Function มีกี่ชนิดกัน
หากมองสิ่งที่ใส่เข้าไปแล้ว RETURN กลับออกมาคงแยกได้เป็น 3 แบบดังนี้- แต่ละ Parameter รับค่าเดียว ตัว Function ก็ RETURN เพียงค่าเดียว (ชนิดข้อมูลแทบทุกชนิด ที่ไม่ใช่ชนิด Table) เป็น Scalar Function
- รับค่าแบบหลายค่า แต่ตัว Function ก็ RETURN เพียงค่าเดียว (ชนิดข้อมูลแทบทุกชนิด ที่ไม่ใช่ชนิด Table) เป็น Aggregate Function
- Table Valued Function คือ Function ที่ RETURN ค่าออกมาเป็นชนิด Table
ส่วน Aggregate Function นั้นต้องใช้ SQL CLR (.NET Framework) ในการพัฒนา
หากผู้อ่านอยากทราบว่าในฐานข้อมูลของเรามี Function ที่พัฒนาขึ้นเองอยู่หรือไม่
สามารถดูได้ผ่านทางโฟลเดอร์ Programmability ในฐานข้อมูล ดังแสดง
ทดลองสร้าง Scalar Function
ผู้เขียนทดลองเขียนบนฐานข้อมูล Adventureworks ผู้อ่านสามารถดาวน์โหลดฐานข้อมูลตัวอย่าง ได้จาก github ตามลิ้งก์ต่อไปนี้ https://github.com/microsoft/sql-server-samples/releases/tag/adventureworksผู้เขียนต้องการ Function ที่ RETURN ค่าเป็นเลขที่ใบสั่งซื้อล่าสุดตามเลขปีที่ป้อนเข้าไป เขียนโค้ดได้ดังแสดง
CREATE FUNCTION Sales.udfGetLastOrderIDbyYear(@year int)RETURNS int
AS
BEGIN
DECLARE @result int
SELECT
@result=MAX(O.SalesOrderID)
FROM Sales.SalesOrderHeader as O
WHERE O.OrderDate>='Jan 1,'+CONVERT(char(4),@year)
AND O.OrderDate<'Jan 1,'+CONVERT(char(4),@year+1)
RETURN @result
END
โค้ดด้านบนถือเป็น Inline-Scalar Function ซึ่ง Microsoft SQL Server 2019 ในเรื่อง Intelligent Query Processing นั้นมีคุณสมบัติใหม่ชื่อ Scalar UDF Inlining ทำให้ Function ที่เขียนแบบ Inline-Scalar Function ควบรวมกับคิวรี่ตัวนอกเป็น Execution Plan เดียวกัน ไม่ได้มองเป็นกล่องดำอีกต่อไป
ผู้เขียนจะทำการทดสอบให้เห็นโดยตั้งให้ฐานข้อมูล Adventureworks มี Compatibility Level เป็น 140 หรือ Microsoft SQL Server เวอร์ชัน 2017 นั่นเอง
USE [master]
GO
ALTER DATABASE [AdventureWorks] SET COMPATIBILITY_LEVEL = 140
GO
จากนั้นผู้เขียนเขียนคิวรี่เพื่อเรียกใช้ Inline-Scalar Function บนฐานข้อมูล Adventureworks ที่สร้างไว้ก่อนหน้า ดังแสดง
SELECT
*
FROM Sales.SalesOrderDetail as OD
WHERE OD.SalesOrderID=Sales.udfGetLastOrderIDbyYear(2014);
GO
จากนั้นผู้เขียนเลือกที่จะแสดง Compiled Plan ผ่านการกดปุ่ม Estimated Execution Plan ที่ Toolbar หรือไฮไลท์คำสั่ง SELECT แล้วกด Ctrl-L จะปรากฏ Graphic Plan ดังแสดง
จะเป็นว่าแยกออกเป็น 2 Plan ทำให้แยกวิเคราะห์ Cost ทำให้ไม่ได้ Cost ที่แท้จริง
ยิ่งไปกว่านั้นในส่วนของจำนวนแถวข้อมูลที่เข้าประมวลผลยังใช้วิธีมั่วตัวเลขเอาเลยว่ามีค่าเท่ากับ 1 แถวข้อมูล
แต่หากเปลี่ยน Compatibility Level เป็น 150 หรือ Microsoft SQL Server เวอร์ชัน 2019 ดังแสดง
USE [master]
GO
ALTER DATABASE [AdventureWorks] SET COMPATIBILITY_LEVEL = 150
GO
จากนั้นเลือกแสดง Compiled Plan อีกครั้ง ผลที่ได้ดังแสดง
ยิ่งไปกว่านั้นในส่วนของจำนวนแถวข้อมูลที่เข้าประมวลผลยังใช้วิธีมั่วตัวเลขเอาเลยว่ามีค่าเท่ากับ 1 แถวข้อมูล
แต่หากเปลี่ยน Compatibility Level เป็น 150 หรือ Microsoft SQL Server เวอร์ชัน 2019 ดังแสดง
USE [master]
GO
ALTER DATABASE [AdventureWorks] SET COMPATIBILITY_LEVEL = 150
GO
จากนั้นเลือกแสดง Compiled Plan อีกครั้ง ผลที่ได้ดังแสดง
จะเห็นว่ามีการผนวกตัวดำเนินการที่อยู่ใน Function เข้ากับ SELECT ตัวนอก แล้วพิจารณาเป็น Plan เดียวกัน การคำนวณ Cost ย่อยคาดเคลื่อนน้อยกว่ามาก
เขียนแบบ Inline-Table Valued Function
CREATE FUNCTION Production.udfGetProductByCatID(@catid int,@rowno int)RETURNS TABLE
AS
RETURN
SELECT TOP(@rowno)
P.ProductID
, SC.Name as SubCategoryName
, P.Name as ProductName
, P.ListPrice
FROM Production.Product as P
INNER JOIN Production.ProductSubcategory as SC
ON P.ProductSubcategoryID=SC.ProductSubcategoryID
INNER JOIN Production.ProductCategory as C
ON C.ProductCategoryID=SC.ProductCategoryID
WHERE C.ProductCategoryID=@catid
ORDER BY P.ListPrice DESC
จะเห็นว่า ภายใน Function ชื่อ udfGetProductByCatID มีเพียงคำสั่ง RETURN ( SELECT) อยู่เพียงคำสั่งเดียว
ผู้เขียนจะแปลง Inline-Table Valued Function ให้อยู่ในรูปของ Multi-Statement Table Valued Function
แล้วตั้งชื่อว่า udfGetProductByCatID2 ดังแสดง
CREATE FUNCTION Production.udfGetProductByCatID2(@catid int,@rowno int)
RETURNS @Result TABLE
(
ProductID int
, SubCategoryName nvarchar(50)
, ProductName nvarchar(50)
, ListPrice money
)
AS
BEGIN
INSERT INTO @Result
SELECT TOP(@rowno)
P.ProductID
, SC.Name as SubCategoryName
, P.Name as ProductName
, P.ListPrice
FROM Production.Product as P
INNER JOIN Production.ProductSubcategory as SC
ON P.ProductSubcategoryID=SC.ProductSubcategoryID
INNER JOIN Production.ProductCategory as C
ON C.ProductCategoryID=SC.ProductCategoryID
WHERE C.ProductCategoryID=@catid
ORDER BY P.ListPrice DESC
RETURN
END
จากนั้นเปลี่ยน Compatibility Level เป็น 110 หรือ Microsoft SQL Server เวอร์ชัน 2012 ดังแสดง
USE [master]
GO
ALTER DATABASE [AdventureWorks] SET COMPATIBILITY_LEVEL = 110
GO
ผู้เขียนขอทดสอบ Table Valued Function แบบ Multi-Statement ก่อนด้วย Query ต่อไปนี้
SELECT *
FROM Production.udfGetProductByCatID2(4,5) as P
INNER JOIN Sales.SalesOrderDetail as OD
ON OD.ProductID=P.ProductID;
เลือกแสดง Compiled Plan อีกครั้ง ผลที่ได้ดังแสดง
ทดลองสร้าง Table Valued Function
เราสามารถสร้าง Table Valued Function ได้สองแบบคือ- Inline-Table Valued Function เป็นฟังก์ชันที่สร้างขึ้นจากคำสั่งเพียงคำสั่งเดียว
- Multi-Statement Table Valued Function เป็นฟังก์ชันที่สร้างขึ้นจากหลายคำสั่ง
เขียนแบบ Inline-Table Valued Function
CREATE FUNCTION Production.udfGetProductByCatID(@catid int,@rowno int)RETURNS TABLE
AS
RETURN
SELECT TOP(@rowno)
P.ProductID
, SC.Name as SubCategoryName
, P.Name as ProductName
, P.ListPrice
FROM Production.Product as P
INNER JOIN Production.ProductSubcategory as SC
ON P.ProductSubcategoryID=SC.ProductSubcategoryID
INNER JOIN Production.ProductCategory as C
ON C.ProductCategoryID=SC.ProductCategoryID
WHERE C.ProductCategoryID=@catid
ORDER BY P.ListPrice DESC
จะเห็นว่า ภายใน Function ชื่อ udfGetProductByCatID มีเพียงคำสั่ง RETURN ( SELECT) อยู่เพียงคำสั่งเดียว
ผู้เขียนจะแปลง Inline-Table Valued Function ให้อยู่ในรูปของ Multi-Statement Table Valued Function
แล้วตั้งชื่อว่า udfGetProductByCatID2 ดังแสดง
CREATE FUNCTION Production.udfGetProductByCatID2(@catid int,@rowno int)
RETURNS @Result TABLE
(
ProductID int
, SubCategoryName nvarchar(50)
, ProductName nvarchar(50)
, ListPrice money
)
AS
BEGIN
INSERT INTO @Result
SELECT TOP(@rowno)
P.ProductID
, SC.Name as SubCategoryName
, P.Name as ProductName
, P.ListPrice
FROM Production.Product as P
INNER JOIN Production.ProductSubcategory as SC
ON P.ProductSubcategoryID=SC.ProductSubcategoryID
INNER JOIN Production.ProductCategory as C
ON C.ProductCategoryID=SC.ProductCategoryID
WHERE C.ProductCategoryID=@catid
ORDER BY P.ListPrice DESC
RETURN
END
จากนั้นเปลี่ยน Compatibility Level เป็น 110 หรือ Microsoft SQL Server เวอร์ชัน 2012 ดังแสดง
USE [master]
GO
ALTER DATABASE [AdventureWorks] SET COMPATIBILITY_LEVEL = 110
GO
ผู้เขียนขอทดสอบ Table Valued Function แบบ Multi-Statement ก่อนด้วย Query ต่อไปนี้
SELECT *
FROM Production.udfGetProductByCatID2(4,5) as P
INNER JOIN Sales.SalesOrderDetail as OD
ON OD.ProductID=P.ProductID;
เลือกแสดง Compiled Plan อีกครั้ง ผลที่ได้ดังแสดง
จากนั้นเปลี่ยน Compatibility Level เป็น 120 หรือ Microsoft SQL Server เวอร์ชัน 2014 ดังแสดง
USE [master]
GO
ALTER DATABASE [AdventureWorks] SET COMPATIBILITY_LEVEL = 120
GO
เลือกแสดง Compiled Plan อีกครั้ง ผลที่ได้ดังแสดง
USE [master]
GO
ALTER DATABASE [AdventureWorks] SET COMPATIBILITY_LEVEL = 120
GO
เลือกแสดง Compiled Plan อีกครั้ง ผลที่ได้ดังแสดง
จากนั้นเปลี่ยน Compatibility Level เป็น 140 หรือ Microsoft SQL Server เวอร์ชัน 2017 ดังแสดง
USE [master]
GO
ALTER DATABASE [AdventureWorks] SET COMPATIBILITY_LEVEL = 140
GO
เลือกแสดง Compiled Plan อีกครั้ง ผลที่ได้ดังแสดง
USE [master]
GO
ALTER DATABASE [AdventureWorks] SET COMPATIBILITY_LEVEL = 140
GO
เลือกแสดง Compiled Plan อีกครั้ง ผลที่ได้ดังแสดง
สรุปกรณี Multi-Statement Table Valued Function
- หาก Compatibility Level เป็น Version 2012 ลงไป Estimated Number of Rows Per Execute จะมีค่าเป็น 1 เป็นการมั่วตัวเลขขึ้นมาเลย
- หาก Compatibility Level อยู่ระหว่าง Version 2014-2016 ค่า Estimated Number of Rows Per Execute จะมีค่าเป็น 100 เป็นการมั่วตัวเลขขึ้นมาเช่นกัน
- หาก Compatibility Level อยู่ระหว่าง Version 2017-2019 ค่า Estimated Number of Rows Per Execute จะมีค่าคะเนจากการคำนวณ ซึ่งให้ผลแม่นยำขึ้นจากคุณสมบัติ Interleaved Execution
SELECT *
FROM Production.udfGetProductByCatID(4,5) as P
INNER JOIN Sales.SalesOrderDetail as OD
ON OD.ProductID=P.ProductID;
สังเกตว่าชื่อ Function ไม่ได้ลงท้ายด้วยเลข 2 จากนั้นเปลี่ยน Compatibility Level เป็น 110 หรือ Microsoft SQL Server เวอร์ชัน 2012 ดังแสดง
USE [master]
GO
ALTER DATABASE [AdventureWorks] SET COMPATIBILITY_LEVEL = 110
GO
แล้วเลือกแสดง Compiled Plan ของ Query ดังกล่าว ผลที่ได้ดังแสดง
จะเห็นว่ามีการผนวกตัวดำเนินการที่อยู่ใน Function เข้ากับ SELECT ตัวนอก แล้วพิจารณาเป็น Plan เดียวกัน
การคำนวณ Cost ย่อยคาดเคลื่อนน้อยกว่ามาก ซึ่งทำได้มาตั้งแต่ SQL Server เวอร์ชั่นเก่า ๆ แล้ว
กรณี Scalar Valued Function
การคำนวณ Cost ย่อยคาดเคลื่อนน้อยกว่ามาก ซึ่งทำได้มาตั้งแต่ SQL Server เวอร์ชั่นเก่า ๆ แล้ว
สรุปข้อควรคำนึง
เมื่อเห็นผลจากการทดสอบ ผู้เขียนสรุปข้อควรคำนึงให้ต่อไปนี้กรณี Scalar Valued Function
- หากใช้ใน SQL Server ต่อกว่าเวอร์ชัน 2019 ให้พิจารณาใช้กับจำนวนแถวข้อมูลน้อย ๆ พยายามอย่าใช้ในประโยค WHERE
- หากใช้ใน SQL Server 2019 พยายามเขียนให้เป็นแบบ Inline-Scalar Function เพราะเปิดโอกาสให้กลไก Query Optimization ทำการผนวกตัวดำเนินการที่อยู่ใน Function เข้ากับ SELECT ตัวนอก แล้วพิจารณาเป็น Plan เดียวกัน การคำนวณ Cost ย่อยคาดเคลื่อนน้อยกว่ามาก
- หากใช้ Multi-Statement Table Valued Function ควรใช้ SQL Server ตั้งแต่เวอร์ชั่น 2017 เป็นต้นไป
- พยายามเขียนแบบ Inline-Table Valued Function เพราะ Query Optimization ทำการผนวกตัวดำเนินการที่อยู่ใน Function เข้ากับ SELECT ตัวนอก แล้วพิจารณาเป็น Plan เดียวกัน การคำนวณ Cost ย่อยคาดเคลื่อนน้อยกว่ามาก ไม่มีเงื่อนไขเรื่องเวอร์ชั่นเข้ามาเกี่ยวข้องในเรื่องนี้