Trong MS SQL Server, function là tính năng giúp ta gom các câu lệnh SQL thành một nhóm và có thể sử dụng lại nhiều lần, tương tự như việc viết hàm trong ngôn ngữ lập trình, tuy nhiên có sự khác biệt cơ bản về cú pháp.

Khái niệm Function trong MS SQL Server

Nếu nhóm các câu lệnh lại và sử dụng thì chúng ta hoàn toàn có thể sử dụng store procedure vẫn có thể làm được điều này, tuy nhiên sự khác biệt rõ ràng nhất là function có giá trị trả về, còn store procedure chỉ thực thi và không trả về dược giá trị (return) giống hàm (function)

Các hàm sẽ giúp đơn giản hóa chương trình và có thể sử dụng nhiều lần. Hãy hình dung chúng ta cần làm một nghiệp vụ phức tạp, cần truyền tham số (paremeter) từ ngoài vào, và xử lý hàng loạt câu lệnh SQL nối tiếp nhau, sau đó trả về một giá trị nào đó để xác định việc kết thúc, và sử dụng giá trị return để làm công việc tiếp theo, thì hàm là giải pháp phù hợp nhất đề giải quyết vấn đề này.

Các function trong SQL Server thường được sử dụng như sau:

Deterministic và non-deterministic functions ( hàm xác định và không xác định):

Các hàm xác định trả về cùng một truy vấn kết quả mỗi khi chúng được gọi với một tập giá trị đầu vào xác định và chỉ định cùng một trạng thái của cơ sở dữ liệu. Các hàm không xác định trả về các kết quả khác nhau mỗi khi chúng được gọi với tập giá trị đầu vào được chỉ định mặc dù cơ sở dữ liệu được truy cập vẫn giữ nguyên.

Ví dụ, nếu người dùng gọi hàm DAY() trong một cột cụ thể, nó luôn trả về số ngày (int) cho tham số kiểu dữ liệu date truyền vào. Tuy nhiên, nếu người dùng gọi hàm DATENAME(), đauà ra không thể được dự đoán vì mỗi lần thực thi giá trị trả ra khác nhau, dựa vào phần định dạng của ngày đươc truyền vào. Vì vậy, hàm DAY() là hàm xác định, trong khi DAYNAME() là hàm không xác định.

Người dùng không thể tác động vào tính xác định của các built-in functions, mỗi built-in function xác định hoặc không xác định dựa vào cách triển khai của nó trên SQL Server

Một số các deterministic functions và non-deterministic functions

Deterministic built-in functions	Non-Deterministic Built-in functions
POWER	@@TOTAL_WRITE
ROUND	CURRENT_TIMESTAMP
RADIANS	GETDATE
EXP	GETUTCDATE
FLOOR	GET_TRANSMISSION_STATUS
SQUARE	NEWID
SQRT	NEWSEQUENTIALID
LOG	@@CONNECTIONS
YEAR	@@CPU_BUSY
ABS	@@DBTS
ASIN	@@IDLE
ACOS	@@IOBUSY
SIGN	@@PACK_RECEIVED
SIN	@@PACK_SENT

Có một số functions không phải sẽ luôn luôn không xác định , bạn có thể dùng chúng trong indexed views nếu chúng được được đưa ra một cách xác định:

Function	Description
CONVERT	Chỉ xác định khi một trong các điều kiện sau tồn tại: Có một sql_variant souce type. Có một sql_variant target type hoặc source type không xác định. Có sourrce và tartget là smalldatetime hoặc dateime, hoặc source type khác như chuỗi. Phong cách tham số phải là hằng số xác định.
CAST	Chỉ xác dịnh khi sửu dụng smalldatetime, sql_variant, datetime
ISDATE	Xác định trừ khi sử dụng cùng với hàm CONVERT
CHECKSUM	Xác định, với ngoại lệ là CHECKSUM(*)

Gọi các extend stored procedure từ các function

Các hàm (function) gọi các thủ tục được lưu trữ mở rộng (extended stored procedure) là không xác định vì các thủ tục được lưu trữ mở rộng có thể dẫn đến các tác dụng phụ trên cơ sở dữ liệu.

Những thay đổi được thực hiện đối với trạng thái chung của cơ sở dữ liệu, chẳng hạn như thay đổi đối với tài nguyên bên ngoài hoặc cập nhật cho bảng, tệp hoặc mạng được gọi là tác dụng phụ. Ví dụ: gửi e-mail hoặc xóa tệp có thể gây ra tác dụng phụ. Trong khi thực hiện một thủ tục được lưu trữ mở rộng từ một hàm do người dùng xác định, người dùng không thể đảm bảo rằng nó sẽ trả về một tập kết quả nhất quán. Do đó, các hàm do người dùng xác định tạo hiệu ứng phụ trên cơ sở dữ liệu không được khuyến khích.

Hàm vô hướng (Scalar-valued Functions)

Một hàm vô hướng nội tuyến có một câu lệnh duy nhất và không có phần thân hàm. Hàm vô hướng nhiều câu lệnh bao quanh thân hàm trong khối BEGIN … END.

Hàm trả về giá trị bảng/hàng cột (Table-valued Functions)

Các hàm có giá trị bảng là các hàm do người dùng định nghĩa trả về một bảng. Tương tự như hàm vô hướng nội tuyến, hàm có giá trị bảng nội tuyến có một câu lệnh đơn và không có thân hàm.

Hàm người dùng tự định nghĩa (User defined function)

Định nghĩa scalar valued function trong SQL Server

Cú pháp:

CREATE FUNCTION function_name (param_list)
RETURN data_type AS
BEGIN
//statements
RETURN value
END

Trong đó:

function_name: tên function
param_list: danh sách các tham số truyền vào function
RETURN data_type: xác định kiểu dữ liệu trả về
AS: mệnh đề bắt đầu quá trình mô tả các lệnh của hàm
//statements: danh sách các lệnh SQL
RETURN value: giá trị trả về.

Ví dụ:

CREATE FUNCTION tinhphepcong(@x INT, @y INT) RETURNS INT
AS
BEGIN
DECLARE @kq int
set @kq = @x + @y
RETURN @kq;
END;

Ví dụ 2:

CREATE FUNCTION sales.udfuncNetSale(
@quantity INT,
@list_price DEC(10,2),
@discount DEC(10,2)
)
RETURNS DEC(10,2)
AS
BEGIN
RETURN @quantity * @list_price * (1 - @discount);
END;

Hàm này sẽ tính giá của một sản phẩm sau khi được giảm.

Sau khi chạy khởi tạo hàm này thì bạn sẽ thấy nó trong SSMS bằng cách click vào Programmability > Functions > Scalar-valued Functions.

Định nghĩa table valued function trong SQL Server

use AdventureWorks2019
go
if OBJECT_ID(N'Sales.udf_CustDates',N'IF') is not null drop function
Sales.udf_CustDates;
go
create function Sales.udf_CustDates () returns table
as return(
select A.customerID,B.DueDate,B.ShipDate from
Sales.Customer as A
left outer join Sales.SalesOrderHeader as B
on A.CustomerID = B.CustomerID
)
go
-- call function
select * from Sales.udf_CustDates();

Cách gọi function trong SQL Server

Chúng ta sử dụng cú pháp functionname(param) , để có thể gọi hàm trong SQL Server

SELECT
dbo.tinhphepcong(10, 20) as kequa

SELECT sales.udfuncNetSale(10,100,0.1) ketqua

ALTER function trong SQL Server

Tương tự như Stored Procedure, để chỉnh sửa function thì bạn sử dụng cú pháp sau:

ALTER FUNCTION [schema_name.]function_name (parameter_list)
RETURN data_type AS
BEGIN
statements
RETURN value
END

Hạn chế

ALTER function phải tuân thủ một số hạn chế và quy tắc, không cho phép người dùng thực hiện các thao tác sau:

• Sửa hàm có giá trị vô hướng thành hàm có giá trị theo bảng.
• Sửa đổi một hàm nội tuyến thành một hàm đa câu lệnh.
• Sửa đổi một Transact-SQL thành một hàm CLR.

Quyền

Cần có quyền ALTER trên lược đồ hoặc hàm. Nếu hàm này do người dùng xác định, thì hàm đó yêu cầu quyền EXECUTE đối với loại đó.

DROP function trong SQL Server

Để xóa một function nào đó thì bạn sử dụng lệnh DROP FUNCTION với cấu trúc như sau:

DROP FUNCTION [schema_name.]function_name;
// schema_name trong quá trình học thường là dbo

Ví dụ mình muốn xóa function đã tạo ở trên thì chạy lệnh sau:

DROP FUNCTION sales.udfNetSale;

Tương tự, bạn chỉ có thể xóa function do bạn tự tạo.

Một vài lưu ý:

• Mỗi function có thể sử dụng ở bất cứ đâu trong câu lệnh T-SQL và nằm trong phạm vi database.
• Có thể có nhiều tham số, tuy nhiên chỉ trả về được một giá trị duy nhất, bắt buộc phải return. ( tương tự với khái niệm hàm trong các lập trình)
• Có thể sử dụng mọi câu lệnh T-SQL bên trong function.
• Trong function này có thể gọi tới function khác đã được định nghĩa.

Mệnh đề OVER

Hàm cửa sổ là một hàm áp dụng cho một tập hợp các hàng. Từ ‘windows’ được dùng để chỉ tập hợp các hàng mà hàm hoạt động.

Trong Transact-SQL, mệnh đề OVER được sử dụng để xác định một cửa sổ trong tập kết quả truy vấn. Sử dụng cửa sổ và mệnh đề OVER với các chức năng cung cấp một số lợi thế. Ví dụ, chúng giúp tính toán
các giá trị tổng hợp. Chúng cũng cho phép tạo số hàng trong tập kết quả một cách dễ dàng.

Thành phần cửa sổ (Windowing Components)

Ba thành phần cốt lõi của việc tạo cửa sổ với mệnh đề OVER như sau:

Phân vùng (Partitioning)

Phân vùng là một tính năng giới hạn cửa sổ tính toán gần đây chỉ cho những hàng từ đó. tập kết quả chứa các giá trị giống nhau trong các cột phân vùng như trong hàng hiện có. Nó sử dụng mệnh đề PARTITION BY.

Ví dụ

use AdventureWorks2019
go
select salesOrderID,ProductID,OrderQty
,SUM(OrderQty) OVER(PARTITION BY SalesOrderID) as Total
,MAX(OrderQty) OVER(PARTITION BY SalesOrderID) as MaxOrderQty
FROM Sales.SalesOrderDetail
where ProductID IN(776,774);
go

Output:

Sắp xếp (Ordering)

Phần tử sắp xế xác định thứ tự để tính toán trong phân vùng. Trong một phần tử sắp xếp SQL tiêu chuẩn, tất cả các hàm đều được hỗ trợ. Trước đó, SQL Server không hỗ trợ sắp xếp các phần tử với các hàm tổng hợp vì nó chỉ hỗ trợ phân vùng. Trong SQL Server 2019, có hỗ trợ cho sắp xếp phần tử với các hàm tổng hợp. Phần tử sắp xếp có ý nghĩa khác nhau ở một mức độ nào đó đối với các loại hàm khác nhau. Với các hàm xếp hạng, việc sắp xếp là tự phát (tự nhiên).

Ví dụ 1:

use AdventureWorks2019;
go
select customerID , StoreID,
RANK() OVER (ORDER BY StoreID DESC)
AS RankAll, RANK() 
OVER (partition by PersonID order by CustomerID DESC )
as RankCustomer
from Sales.Customer;

Output:

Ví dụ 2:

use AdventureWorks2019;
go
select TerritoryID, Name,
SalesYTD, RANK() OVER (order by SalesYTD DESC)
as RankFirst , RANK() Over (PARTITION BY TerritoryID 
order by SalesYTD DESC) as RankSecond
from Sales.SalesTerritory;

Output:

Tạo khung (framing)

Tạo khung là một tính năng cho phép bạn chỉ định phân chia thêm các hàng trong một phân vùng cửa sổ. Điều này được thực hiện bằng cách gán ranh giới trên và dưới cho khung cửa sổ trình bày các hàng cho chức năng cửa sổ. Nói một cách đơn giản, một khung tương tự như một cửa sổ di chuyển trên dữ liệu bắt đầu và kết thúc tại các vị trí được chỉ định. Các khung cửa sổ có thể được xác định bằng cách sử dụng các mệnh đề ROW hoặc RANGE và cung cấp các ranh giới bắt đầu và kết thúc.

Ví dụ:

use AdventureWorks2019;
go
select ProductID,Shelf,Quantity,
SUM(Quantity) OVER 
(PARTITION BY ProductID ORDER BY LocationID
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) as RunQty
FROM Production.ProductInventory;

Output:

Hàm cửa sổ (Windows Function)

Một số hàm cửa sổ khác nhau được liệt kê phía dưới như sau:

Ranking functions

Các hàm này trả về một giá trị xếp hạng cho mỗi hàng trong một phân vùng. Dựa trên chức năng được sử dụng, nhiều hàng sẽ trả về cùng một giá trị như các hàng khác. Hàm ranking là không xác định.

Danh sách các kiểu hàm Ranking khác nhau và mô tả:

Ranking functions	Mô tả
NTITLE	Trải các hàng trong một phân vùng có thứ tự thành một số nhóm nhất định, bắt đầu từ 1. Đối với mỗi hàng, hàm trả về số của nhóm mà hàng đó thuộc về.
ROW NUMBER	Truy xuất số thứ tự của một hàng trong một phân vùng của tập kết quả, bắt đầu từ 1 cho hàng đầu tiên trong mỗi phân vùng
DENSE RANK	Trả về thứ hạng của các hàng trong phân vùng của tập kết quả mà không có bất kỳ khoảng trống nào trong thứ hạng. Thứ hạng của một hàng là một cộng với số thứ hạng riêng biệt xuất hiện trước hàng được đề cập.

Ví dụ:

use AdventureWorks2019;
go
select p.FirstName,p.LastName,
ROW_NUMBER() OVER (Order by a.PostalCode) as 'Row Number',
NTILE(4) OVER (ORDER BY a.PostalCode) as 'NTILE',
s.SalesYTD,a.PostalCode FROM Sales.SalesPerson AS s
inner join Person.Person as p
on s.BusinessEntityID = p.BusinessEntityID
 inner join Person.Address as a
 on a.AddressID = p.BusinessEntityID
 where TerritoryID is not null and SalesYTD <> 0;

Output:

OFFSET Functions

Có các kiểu hàm OFFSET khác nhau như sau:

SWITCHOFFSET

Hàm này trả về giá trị DATETIMEOFFSET được sửa đổi từ phần bù múi giờ được lưu trữ thành phần bù múi giờ mới cụ thể.

Cú pháp:

SWITCHOFFSET (DATETIMEOFFSET, time_zone)

Ví dụ:

use AdventureWorks2019;
go
create table Test(
	ColDatetimeoffset datetimeoffset
)
go
insert into Test
values ('1998-09-20 7:45:45.71000 -5:00');
go 
select SWITCHOFFSET(ColDatetimeoffset, '-08:00')
from Test;
go
select ColDatetimeoffset from Test;

DATETIMEOFFSETFROMPARTS

Hàm này trả về giá trị datetimeoffset cho ngày và giờ đã chỉ định với độ chính xác và độ lệch được chỉ định.

Cú pháp:

DATETINEOFFSETFROMPARTS (year, month, day, hour, minute, seconds, fractions, hour offset, minute offset, precision)

Trong đó:

• year: chỉ định biểu thức số nguyên cho một năm.
• month: chỉ định biểu thức số nguyên cho một tháng.
• day: chỉ định biểu thức số nguyên cho một ngày.
• hour chỉ định biểu thức số nguyên cho một giờ.
• minute: chỉ định biểu thức số nguyên trong một phút,
• second: chỉ định biểu thức số nguyên cho một ngày.
• fractions: chỉ định biểu thức số nguyên cho phân số.
• hour_offset: chỉ định biểu thức số nguyên cho phần giờ của phần bù múi giờ.
• minute_offset: chỉ định biểu thức số nguyên cho phần phút của phần bù múi giờ.
• precision: chỉ định độ chính xác theo nghĩa đen của giá trị datetimeofiset sẽ được trả về

Ví dụ:

select DATETIMEOFFSETFROMPARTS(2010,12,31,14,23,23,0,12,0,7) as Result;

SYSDATETIMEOFFSET

Các hàm này trả về giá trị datetimeoffset(7) có chứa ngày và giờ của máy tính mà phiên bản SQL Server đang chạy trên đó.

Cú pháp:

SYSDATETIMEOFFSET();

Ví dụ:

select SYSDATETIME() as SYSDATETIME,
SYSDATETIMEOFFSET() AS SYSDATETIMEOFFSET,
SYSUTCDATETIME() AS SYSUTCDATETIME

Output:

Analytic Functions

SQL Server 2019 hỗ trợ một số chức năng phân tích. Các hàm này tính toán giá trị tổng hợp dựa trên một nhóm hàng. Các hàm phân tích tính toán tổng số đang chạy, trung bình động hoặc kết quả N hàng đầu trong một nhóm.

Liệt kê một số hàm phân tích:

Hàm	Mô tả
LEAD	Cung cấp quyền truy cập vào dữ liệu từ một hàng tiếp theo trong cùng một tập kết quả mà không cần sử dụng self join
LAST_VALUE	Truy xuất giá trị cuối cùng trong một bộ giá trị được sắp xếp theo thứ tự.
LAG	Cung cấp quyền truy cập vào dữ liệu từ một hàng trước đó trong cùng một tập kết quả mà không cần sử dụng self join
FIRST_VALUE	Truy xuất giá trị đầu tiên trong một bộ giá trị được sắp xếp theo thứ tự.
CUME_DIST	Tính phân phối tích lũy của một giá trị trong một nhóm giá trị.
PERCENTILE_CONT	Tính toán phần trăm dựa trên phân phối liên tục của giá trị cột trong SQL.
PERCENTILE_DISC	Tính toán một phần trăm cụ thể cho các giá trị được sắp xếp trong toàn bộ tập hợp hàng hoặc trong các phân vùng riêng biệt của tập hợp hàng.

Ví dụ hàm LEAD():

USE AdventureWorks2019;
GO
SELECT BusinessEntityID, YEAR(QuotaDate) as QuotaYear,
SalesQuota as NewQuota, LEAD(SalesQuota,1,0) 
OVER (ORDER BY YEAR(QuotaDate))  AS FutureQuota FROM
Sales.SalesPersonQuotaHistory
WHERE BusinessEntityID = 275 and YEAR(QuotaDate) in ('2011','2014');

Output:

Ví dụ hàm FIRST_VALUE():

USE AdventureWorks2019;
GO
select Name,ListPrice,
FIRST_VALUE(Name) OVER (ORDER BY ListPrice ASC) as LessExpensive FROM
Production.Product
WHERE ProductSubcategoryID = 37;

Output: