Biến Table trong SQL Server

Trong bài này bạn sẽ được học một loại biến rất hay đó là biến table trong SQL Server, biến này có thể hiểu là một bảng tạm thời trong một chương trình SQL dùng để lưu trữ các row dữ liệu có các column đã định sẵn.

1. Biến table là gì?

Biến table là một loại biến đặc biệt dùng để lưu trữ nhiều dòng dữ liệu, nó có chức năng gần giống như bảng tạm Temporary Tables, nghĩa là bạn có thể insert và select dữ liệu của nó.

Vì nó cũng là một biến nên chỉ tồn tại trong phạm vi khai báo của nó. Ví dụ bạn khai báo trong procedure, function hoặc trigger thì biến chỉ sử dụng được trong đó mà thôi, sau khi chạy xong nó sẽ biến mất.

Để khai báo biến table thì bạn sử dụng từ khóa DECLARE với cú pháp sau:

Bài viết này được đăng tại [free tuts .net]

DECLARE @table_variable_name TABLE (
    column_list
);

Trong đó:

• column_list chính là danh sách các column của table, cấu trúc giống như việc bạn khai báo một bảng thông thường.
• @table_variable_name là tên của table, luôn bắt đầu bằng kí tự @

Hãy xem ví dụ dưới đây:

DECLARE @product_table TABLE (
    product_name VARCHAR(MAX) NOT NULL,
    brand_id INT NOT NULL,
    list_price DEC(11,2) NOT NULL
);

Như bạn thấy cách khai báo cũng tương tự như lệnh Create Table, bởi vì bản chất nó cũng là một table, chỉ khác là nó chỉ tồn tại trong một phạm vi nhất định.

2. Một số cách sử dụng biến table

Chúng ta có những thao tác chính như insert, select.

Insert

Để thêm data thì ta sử dụng lệnh INSERT nếu thêm một row dữ liệu, sử dụng INSERT INTO nếu muôn thêm từ một câu truy vấn khác. Như ví dụ dưới dây mình sẽ thêm từ một câu truy vấn.

INSERT INTO @product_table
SELECT
    product_name,
    brand_id,
    list_price
FROM
    production.products
WHERE
    category_id = 1;

Select

Giống như temporary table, bạn có thể thực hiện câu lệnh select trên biến table.

SELECT
    *
FROM
    @product_table;

Kế quả sẽ trả về danh sách sản phẩm như hình dưới đây.

3. Ưu điểm và nhược điểm của biến table

Vì chỉ là một biến thông thường nên sẽ có rất nhiều hạn chế.

Nhược điểm

Thứ nhất, bạn sẽ phải khai báo cấu trúc column ngay câu lệnh tạo biến, bạn không thể sử dụng ALTER TABLE để thay đổi cấu trúc của bảng.

Thứ hai, nó chỉ phù hợp với việc lưu trữ dữ liệu nhỏ, nếu lưu trữ dữ liệu quá lớn thì sẽ truy vấn rất chậm.

Thứ ba, không nên sử dụng nó là một biến đầu vào hoặc biến đầu ra ở procedure, tuy nhiên bạn có thể return nó trong function.

Thứ tư, bạn không thể tạo chỉ mục trên bảng tạm.

Thứ 5, nếu bạn muốn thực hiện phép JOIN trên bảng tạm thì bạn phải đặt lại bí danh cho nó bằng từ khóa AS. Hãy xem ví dụ dưới đây.

SELECT
    brand_name,
    product_name,
    list_price
FROM
    brands b
INNER JOIN @product_table pt ON p.brand_id = pt.brand_id;

Ưu điểm

Sử dụng biến table trong procedure giúp chương trình hoạt động tốt hơn bởi SQL Server sẽ ít phải biên dịch hơn so với sử dụng bảng tạm.

Biến table sử dụng ít tài nguyên hơn.

Tương tự như bảng tạm thời, các biến bảng sẽ sống trong cơ sở dữ liệu tempdb chứ không phải trong bộ nhớ.

4. Sử dụng biến table trong function

Hãy xem ví dụ dưới đây, đây là một function có tên là ufnSplit và nó sẽ trả về một variable table.

CREATE OR ALTER FUNCTION udfSplit(
    @string VARCHAR(MAX), 
    @delimiter VARCHAR(50) = ' ')
RETURNS @parts TABLE
(    
idx INT IDENTITY PRIMARY KEY,
val VARCHAR(MAX)   
)
AS
BEGIN
 
DECLARE @index INT = -1;
 
WHILE (LEN(@string) > 0) 
BEGIN 
    SET @index = CHARINDEX(@delimiter , @string)  ;
    
    IF (@index = 0) AND (LEN(@string) > 0)  
    BEGIN  
        INSERT INTO @parts 
        VALUES (@string);
        BREAK  
    END 
 
    IF (@index > 1)  
    BEGIN  
        INSERT INTO @parts 
        VALUES (LEFT(@string, @index - 1));
        
        SET @string = RIGHT(@string, (LEN(@string) - @index));  
    END 
    ELSE
    SET @string = RIGHT(@string, (LEN(@string) - @index)); 
    END
RETURN
END

Và đây là cách gọi đến hàm này.

SELECT 
    * 
FROM 
    udfSplit('foo,bar,baz',',');

Kế quả sẽ nhưu hình sau:

Như vậy là mình đã giới thiệu xong cách sử dụng biến table (tvariable able), hy vọng qua bài này bạn sẽ hiểu được những ưu điểm và nhược điểm của nó, đồng thời phân biệt được temporary table và variable table.