Bảng tạm và biến kiểu bảng

Chú ý: Bài viết này dùng cú pháp cho Microsoft SQL Server

Bảng tạm (temporary table) và biến kiểu bảng (table variable) là 2 phương tiện để lưu dữ liệu tạm thời khi đang xử lý. Bảng tạm có tên bắt đầu bằng dấu “#”, bạn có thể tạo bảng tạm bằng lệnh CREATE TABLE và khai báo các cột của nó; hoặc bạn có thể vừa tạo và thêm dữ liệu vào bằng SELECT INTO. Với biến kiểu bảng thì bạn phải khai báo trước mới có thể dùng được, và khi khai báo bạn phải định nghĩa các cột của nó:

--tạo bảng trực tiếp
CREATE TABLE #t1 (C VARCHAR(50))
 
--vừa tạo bảng và thêm dữ liệu vào
SELECT C
INTO #t2
FROM dbo.SomeTable
 
--khai báo biến kiểu bảng
DECLARE @t TABLE(C VARCHAR(50))
INSERT INTO @t SELECT 1
SELECT * FROM @t

Bảng tạm khai báo ở trên gọi là bảng tạm cục bộ (local temp table), SQL Server còn cung cấp bảng tạm toàn cục (global temp table) với tên bắt đầu bằng hai dấu “##”. Trong bài viết này khi nhắc đến bảng tạm là tôi chỉ nói về bảng tạm cục bộ, vì bạn sẽ chủ yếu dùng đến loại này.

Cả bảng tạm và biến kiểu bảng chỉ tồn tại trong phạm vi (scope) mà chúng được tạo. Khi bạn tạo chúng trong thủ tục, chúng sẽ tự động bị xóa sau khi thủ tục thực hiện xong. Nếu bạn tạo chúng trong đoạn SQL động bên trong thủ tục, chúng cũng được xóa sau khi đoạn SQL động thực hiện xong.

CREATE PROC dbo.p1
AS
BEGIN
   CREATE TABLE #t1(C VARCHAR(10))
   INSERT INTO #t1 SELECT 'abc'
END
 
EXEC dbo.p1
SELECT * FROM #t1 --lỗi, #t1 không tồn tại nữa

CREATE PROC dbo.p2
AS
BEGIN
   DECLARE @SQL VARCHAR(1000)
   SELECT @SQL = 'DECLARE @t TABLE(C VARCHAR(10))
   INSERT INTO @t SELECT 1
'
   EXEC(@SQL)
   SELECT * FROM @t --lỗi, biến @t không tồn tại nữa
END

Cả bảng tạm và biến kiểu bảng đều được lưu trong tempdb, chúng được lưu trong bộ nhớ khi có đủ chỗ và bị đẩy ra đĩa cứng khi cần thiết. Vì thế việc truy xuất biến kiểu bảng không có gì nhanh hơn so với bảng tạm.

Vì cả bảng tạm và biến kiểu bảng chỉ tồn tại trong phạm vi chúng được khai báo, nên tên của chúng cũng chỉ có ý nghĩa trong phạm vi đó. Nếu bạn mở hai cửa sổ trong Management Studio và ở cả hai cửa sổ bạn tạo bảng tạm có tên #t, SQL Server sẽ không phàn nàn gì vì chúng thuộc hai phạm vi khác nhau (vì thế bảng tạm kiểu này gọi là bảng tạm cục bộ). Tương tự cùng một thủ tục được gọi đồng thời nhiều lần từ ứng dụng, dù thủ tục tạo cùng một tên bảng tạm nhưng mỗi lần gọi đều thuộc một phạm vi riêng nên không có xung đột gì về tên bảng.

Về thực chất, SQL Server khi tạo bảng tạm trong tempdb sẽ thêm một chuỗi định danh vào tên bảng để đảm bảo nó duy nhất và chỉ phạm vi tạo bảng đó có thể truy xuất được bảng này. Bạn có thể xem các bảng tạm hiện có trong hệ thống bằng lệnh sau:

SELECT * FROM tempdb.INFORMATION_SCHEMA.tables
ORDER BY TABLE_NAME

Ngoài các bảng tạm hệ thống, bạn sẽ thấy các bảng tạm do người dùng tạo với tên kiểu như “#t______A145G”.

Bạn có thể truy vấn bảng tạm và biến kiểu bảng giống như đối với bảng thông thường, bạn có thể JOIN với chúng, dùng subquery… Bạn cũng có thể INSERT, UPDATE, hoặc DELETE các bản ghi trong bảng tạm và biến kiểu bảng.

Đi vào chi tiết hơn, bảng tạm và biến kiểu bảng có những đặc điểm khác biệt, và những đặc điểm này giúp bạn quyết định dùng cái nào thích hợp hơn.

• Bạn có thể xóa (DROP TABLE) bảng tạm và tạo lại ngay trong cùng một thủ tục, nhưng bạn không thể làm như vậy với biến kiểu bảng, vì nó là một biến nó sẽ tồn tại đến khi thủ tục thực hiện xong.

• Bạn có thể tạo index cho các cột của bảng tạm và tạo khóa primary key, unique key cho nó, nhưng bạn không thể làm vậy với biến kiểu bảng. Đây là ưu thế vượt trội của bảng tạm khi cần xử lý lượng dữ liệu lớn và chạy các câu lệnh phức tạp. Chỉ có khóa ngoại là không được tạo trên bảng tạm (ở bản SQL 2008).

• Bạn có thể TRUNCATE bảng tạm nhưng chỉ có thể DELETE biến kiểu bảng. Khi cần xóa một lượng dữ liệu lớn, bảng tạm sẽ có ưu thế hơn.

• Khi bạn có thủ tục cha gọi một thủ tục con, bảng tạm tạo trong thủ tục cha sẽ được nhìn thấy trong thủ tục con. Thủ tục con có thể truy vấn và cập nhật dữ liệu trong bảng này, và khi quay về thủ tục cha nó vẫn nhìn thấy dữ liệu đã cập nhật. Đây là một cách hữu hiệu để trao đổi dữ liệu giữa các thủ tục. Ví dụ, tôi tạo hai thủ tục p1 và p2, và trong p1 tôi có lời gọi đến p2:

CREATE PROC dbo.p1
AS
BEGIN
          IF OBJECT_ID('tempdb..#t1') IS NOT NULL DROP TABLE #t1
          CREATE TABLE #t1(i INT)
          INSERT INTO #t1 SELECT 1
          SELECT * FROM #t1 -- chỉ có 1 bản ghi insert bởi p1
          EXEC p2
          SELECT * FROM #t1 --có thêm bản ghi insert bởi p2
END
go
ALTER PROC dbo.p2
AS
BEGIN
          INSERT INTO #t1 SELECT 2     
END
 
EXEC dbo.p1 -- nhận được 2 bản ghi

Kết quả của đoạn lệnh trên là 2 bản ghi insert bởi p1 và p2. Đối với biến kiểu bảng thì không làm được như vậy. Nếu không thấy biến được khai báo, thủ tục sẽ báo lỗi ngay từ khi biên dịch.

• Bảng tạm luôn kèm theo statistics là các thông tin trợ giúp việc tối ưu hóa thực hiện. Đây là một overhead khi dùng bảng tạm. Vì thế khi phương án thực thi tối ưu không phụ thuộc nhiều vào lượng dữ liệu tạm, nói cách khác statistics của bảng tạm không có tác dụng gì, bạn nên dùng biến kiểu bảng.

• Khi viết hàm, vì hàm không cho phép dùng bảng tạm nên bạn chỉ có một lựa chọn là biến kiểu bảng.

Nguồn: http://www.sqlviet.com/blog/bang-tam-va-bien-kieu-bang