Bảng Tạm và Biến Kiểu Bảng

Vũ Huy Tâm

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.




Tags: , ,

19 Comments
Posted on 9/10/2013 | Categories: SQL Server Programming

Các bài viết tương tự

Comments
  • ecompc (14/10/2013 6:49 am)

    Và khi sử dụng bảng tạm sẽ kéo theo performance sụt giảm trầm trọng :D , nên cân nhắc trc khi sử dụng

    • PhaoThu (15/10/2013 5:00 am)

      Bảng tạm làm giảm performance trong trường hợp nào vậy bạn ?

  • Vũ Huy Tâm (15/10/2013 9:58 am)

    Bảng tạm không ảnh hưởng gì tới performance, thậm chí còn là cách giữ performance vì có thể tạo khóa và index trên đó và bộ optimizer có thể sử dụng các thông tin này. Tất nhiên bạn chỉ nên dùng bảng tạm khi cần thiết nếu không sẽ bắt hệ thống làm thêm các việc thừa.

    • danglong (23/09/2015 4:28 am)

      Chào anh Tâm!
      anh có thể cho em hỏi performance là gì không, em tìm trên google không thấy khái niệm về nó, anh có thể cho em xin một ví dụ không !

  • Quang Vinh (23/10/2013 10:05 pm)

    Cảm ơn anh Tâm, bài viết rất hữu ích.

    Mình chỉ muốn góp ý rằng có nhiều bài viết (bằng tiếng Anh) của các tác giả SQL Server đã chứng minh rằng biến kiểu bảng không hề được lưu trong bộ nhớ, chỉ lưu trong Tempdb. Anh Tâm có thể kiểm chứng điều này bằng đoạn script sau:

    --tạo 1 biến kiểu bảng và insert vào 1 giá trị
    DECLARE @TempTable TABLE ( ID INT ) ;
    INSERT INTO @TempTable ( ID )
    VALUES ( 1 ) ;
    GO
     
    --dùng đoạn script sau để kiểm tra, ta thấy nó được lưu trong Tempdb
    SELECT session_id,
    database_id,
    user_objects_alloc_page_count
    FROM SYS.dm_db_session_space_usage
    WHERE session_id > 50 ;

    Thân,
    Vinh

    • Vũ Huy Tâm (24/10/2013 9:23 am)

      Chào Vinh,
      Cả bảng tạm và biến kiểu bảng đều được quản lý bởi tempdb, nếu đủ chỗ tempdb sẽ để chúng trong bộ nhớ (vì temdb cũng có bộ nhớ cho mình). Đến khi hết chỗ sẽ đẩy chúng ra đĩa.
      Trường user_objects_alloc_page_count trong query bạn dùng là số trang được cấp phát chứ không chỉ rõ trang trong bộ nhớ hay ở đĩa. Bạn xem bài này, trong đó có đoạn …”If memory is available, both table variables and temporary tables are created and processed while in memory (data cache)”:
      http://support.microsoft.com/kb/305977/en-us
      Các tranh luận phổ biến hơn là, biến kiểu bảng luôn ở trong bộ nhớ, vì nó chỉ là 1 biến giống như biến kiểu int hay kiểu datetime. Ý của bạn lại là ở trường hợp ngược lại. Cả hai đều không đúng hoàn toàn.
      Nói thêm là biến kiểu MAX như VARCHAR(MAX) cũng ở tình trạng như vậy.

      • Huy Lv (26/03/2014 6:08 am)

        Hi anh Tâm,
        Em không nghĩ là trong cả 2 trường hợp (đủ và không đủ bộ nhớ) cả bảng tạm và biến bảng đều quản lý bởi tempdb.
        Bảng tạm luôn được quản lý bởi tempdb là đúng nhưng trong trường hợp đủ bộ nhớ, biến bảng sẽ luôn ở trong bộ nhớ và sẽ KHÔNG QUẢN LÝ bởi temdb, bởi nếu quản lý bởi tempdb, khi insert dữ liệu vào biến bảng thì dữ liệu của biến bảng sẽ ăn theo collation của tempdb, nếu theo bộ nhớ thì sẽ theo collation của DB hiện tại. Anh có thể kiểm tra lại trường hợp này bằng cách
        1. Đổi collation của DB hiện tại và tempdb cho khác nhau
        2. Tạo 1 bảng thực trong DB hiện tại Table A (name nvarchar, EmpCode nvarchar)
        3. Tạo 1 biến bảng @VarTable(empCode nvarchar, salary decimal) và 1 bảng tạm #TmpTable(empcode nvarchar, salary decimal)
        4. Select dữ liệu:
        – Join từ bảng TableA với @VarTable on EmpCode: Sẽ không lỗi collation, chứng tỏ @VarTable ở trong bộ nhớ và không quản lý bởi tempdb
        – Join từ bảng TableA với #TmpTable on EmpCode: Sẽ có lỗi collation, chứng tỏ bảng tạm được quản lý bởi tempdb

        • Vũ Huy Tâm (26/03/2014 10:31 am)

          Phát hiện của bạn rất hay. Tuy nhiên mình nghĩ là chưa đủ để kết luận biến bảng nằm trong database (mặc dù rất có lý). Bạn đọc link mình gửi ở comment trước, http://support.microsoft.com/kb/305977/en-us, có đoạn “…Table variables are created in the tempdb database similar to temporary tables…”
          Đấy là Microsoft nói, ko phải mình tự nghĩ thế :)

  • Quang Vinh (24/10/2013 10:19 am)

    Thanks anh Tâm vì dẫn chứng của anh.

  • Quốc Bảo (27/10/2013 6:27 am)

    Chào anh Tâm,

    Các bài viết của anh thật hay và tôi nghĩ rằng, anh đã đóng góp một giá trị lớn cho cộng đồng.

    Tôi mới học về quản trị SQL, kiến thức còn ít. Nếu có thể, anh cho tôi hỏi:
    Tôi có 1 Database, số bảng và dữ liệu đã có sẵn và khá nhiều, tôi muốn phân partition để quản lý tốt hơn. Tôi có đọc các bài trước của anh nói về phân partition cho 1 database khi bắt đầu tạo lập nó, nhưng tôi chưa hiểu cách làm đối với 1 database đã có sẵn các bảng và dữ liệu. Rất mong anh chỉ giáo.

    Trân trọng
    Quốc Bảo

    • Vũ Huy Tâm (28/10/2013 2:05 pm)

      Để partition bảng đã có sẵn thì bạn tạo lại clustered index trên bảng đó: CREATE CLUSTERED INDEX IXNAME ON dbo.YourTable ON YourPartitionScheme(ColName) WITH DROP_EXISTING
      Vì clustered index chính là dữ liệu nên khi tạo lại clustered index trên partition scheme thì index đó được phân đoạn kéo theo bảng được phân đoạn theo.
      Lúc nào tôi sẽ viết 1 bài kỹ hơn về kỹ thuật này.

  • Quốc Bảo (31/10/2013 2:54 am)

    Cảm ơn anh Tâm rất nhiều.

    Tôi sẽ chờ bài viết đó của anh.

  • Hoàng (19/12/2013 9:18 pm)

    Chào Mr Tâm,

    cho tôi hỏi cái này nhé:

    tôi dùng phân trang như sau:

    WITH EmployeePage AS
    (
    SELECT FirstName ,MiddleInitial ,LastName
    ,ROW_NUMBER() OVER (ORDER BY LastName, FirstName, MiddleInitial) AS RowNumber
    FROM Person
    )
    SELECT * FROM EmployeePage
    WHERE RowNumber > @FirstID AND RowNumber <= @FirstID + 4;

    vậy bảng EmployeePage so với 2 loại trên thì tốc độ sử lý có nhanh và nhẹ hơn không?

    • Vũ Huy Tâm (20/12/2013 3:20 pm)

      Nếu ý bạn muốn nói so sánh với bảng tạm và biến bảng, thì phân trang nên dùng hàm row_number thay vì dùng bảng tạm và biến bảng (trừ khi bạn dùng SQL đời cũ quá).

  • TIEN TRAN (23/09/2014 5:06 am)

    HI ANH TÂM, ANH CHO EM HỎI, CÓ CÁCH NÀO ĐỂ XUẤT THÔNG TIN TABLE VÀO FILE WORD KHÔNG ANH? VD NHƯ
    TABLE CUSTOMER (ID, NAME, MOBILE…) THÌ VÀO FILE WORD SẼ HIỂN THỊ THÔNG TIN NHƯ:

    CUSTOMER
    (HIỂN THỊ THÔNG TIN BẢNG CUSTOMER)
    ID
    NAME
    MOBILE

    KHÔNG PHẢI DẠNG DỮ LIỆU MÀ LÀ THÔNG TIN COLUMN NAME ẤY Ạ? CHỈ CHO EM VỚI

    • Vũ Huy Tâm (23/09/2014 9:11 am)

      Xuất ra file dạng word thì mình không biết, nhưng bạn có thể lấy thông tin các cột bằng lệnh này:

      SELECT COLUMN_NAME
      FROM INFORMATION_SCHEMA.COLUMNS
      WHERE TABLE_NAME = 'CUSTOMER'
  • Quoc Tran (07/05/2016 2:54 am)

    Hi all,
    Có thể giải thích cho mình hiểu rõ tại sao phải dùng bảng tạm ? Lợi ích của việc dùng bảng tạm ? Và những trường hợp nào thì mình phải dùng bảng tạm ?

    Thanks all !

  • Đỗ Tấn Phúc (18/01/2017 11:04 pm)

    Anh tâm cho em hỏi tí. Em mới học sql nhưng không hiểu sao sql server lại chiếm rất nhiều bộ nhớ ram. Em xem trong task manager thì sql server chiếm tới 7ghz ram. Dẫn đến truy xuất database chậm. Anh có cách nào có thể làm giảm bộ nhớ tạm Ram cho sql server tự động không ạ. Chứ hiện tại mỗi ngày thì bộ nhớ ram lại tăng lên rất cao.

Leave a Reply to ecompc

Click here to cancel reply.
Hướng dẫn: Để nhập mã T-SQL bạn dùng thẻ <pre lang="tsql"> và </pre>.
Ví dụ: <pre lang="tsql">SELECT * FROM MyTable</pre>