Sự khác biệt giữa bảng tạm thời và biến bảng trong SQL Server là gì?


404

Đây dường như là một khu vực có khá nhiều huyền thoại và quan điểm trái ngược nhau.

Vì vậy, sự khác biệt giữa một biến bảng và bảng tạm thời cục bộ trong SQL Server là gì?

+4

Câu hỏi liên quan về Stack Overflow: [Khi nào tôi nên sử dụng biến bảng so với bảng tạm thời trong SQL Server?] (Http://stackoverflow.com/q/11857789/73226) 07 jul. 162016-07-07 04:30:51

618

Nội dung

Contents

Hãy cẩn thận

Câu trả lời này thảo luận về các biến bảng "cổ điển" được giới thiệu trong SQL Server 2000. SQL Server 2014 trong bộ nhớ OLTP giới thiệu các loại bảng được tối ưu hóa bộ nhớ.Các trường hợp biến của bảng là khác nhau ở nhiều khía cạnh so với các trường hợp được thảo luận dưới đây!(more details).

Khu vực lưu trữ

Không khác nhau.Cả hai đều được lưu trữ trong tempdb .

Tôi đã thấy nó gợi ý rằng đối với các biến của bảng, điều này không phải lúc nào cũng đúng nhưng điều này có thể được xác minh từ bên dưới

DECLARE @T TABLE(X INT)

INSERT INTO @T VALUES(1),(2)

SELECT sys.fn_PhysLocFormatter(%%physloc%%) AS [File:Page:Slot]
FROM @T

Kết quả ví dụ (hiển thị vị trí trong tempdb 2 hàng được lưu trữ)

File:Page:Slot
----------------
(1:148:0)
(1:148:1)

Vị trí hợp lý

@table_variables hành xử nhiều hơn như thể chúng là một phần của cơ sở dữ liệu hiện tại hơn #temp bảng làm.Đối với các biến đối số của bảng biến (kể từ năm 2005) nếu không được chỉ định rõ ràng sẽ là cơ sở dữ liệu hiện tại trong khi đối với các bảng #temp nó sẽ sử dụng đối chiếu mặc định là tempdb (More details).Ngoài ra, các kiểu dữ liệu do người dùng định nghĩa và các bộ sưu tập XML phải có trong tempdb để sử dụng cho các bảng #temp nhưng các biến bảng có thể sử dụng chúng từ cơ sở dữ liệu hiện tại (Source).

SQL Server 2012 giới thiệu cơ sở dữ liệu chứa.the behavior of temporary tables in these differs (h/t Aaron)

Trong cơ sở dữ liệu chứa dữ liệu bảng tạm thời được đối chiếu trong đối chiếu của cơ sở dữ liệu chứa.

  • Tất cả siêu dữ liệu được liên kết với các bảng tạm thời (ví dụ: tên bảng và cột, chỉ mục, v.v.) sẽ nằm trong đối chiếu danh mục.
  • Các ràng buộc được đặt tên có thể không được sử dụng trong các bảng tạm thời.
  • Các bảng tạm thời không được đề cập đến các loại do người dùng định nghĩa, các bộ sưu tập lược đồ XML hoặc các hàm do người dùng định nghĩa.

Tầm nhìn đến các phạm vi khác nhau

@table_variables chỉ có thể được truy cập trong lô và phạm vi mà chúng được khai báo.#temp_tables có thể truy cập được trong các lô con (kích hoạt lồng nhau, thủ tục, exec cuộc gọi).#temp_tables được tạo ở phạm vi bên ngoài (@@NESTLEVEL=0) có thể kéo dài các lô quá khi chúng tồn tại cho đến khi phiên kết thúc.Không có loại đối tượng có thể được tạo ra trong một lô trẻ em và truy cập trong phạm vi gọi tuy nhiên như đã thảo luận tiếp theo (toàn cầu ##temp bảng thể mặc dù).

Cả đời

@table_variables được tạo hoàn toàn khi một lô chứa câu lệnh DECLARE @.. TABLE được thực thi (trước khi bất kỳ mã người dùng nào trong đợt đó chạy) và bị bỏ mặc định ở cuối.

Mặc dù trình phân tích cú pháp sẽ không cho phép bạn thử và sử dụng biến bảng trước câu lệnh DECLARE , việc tạo ngầm có thể được nhìn thấy bên dưới.

IF (1 = 0)
BEGIN
DECLARE @T TABLE(X INT)
END

--Works fine
SELECT *
FROM @T

#temp_tables được tạo một cách rõ ràng khi câu lệnh TSQL CREATE TABLE gặp phải và có thể bị loại bỏ một cách rõ ràng với DROP TABLE hoặc sẽ bị loại bỏ hoàn toàn khi đợt kết thúc (nếu được tạo trong một đợt con với @@NESTLEVEL > 0

NB: Trong các thói quen được lưu trữ cả hai loại đối tượng can be cached thay vì liên tục tạo và can be cached các bảng mới.Tuy nhiên, có những hạn chế khi bộ nhớ đệm này có thể xảy ra tuy nhiên có thể vi phạm đối với #temp_tables nhưng những hạn chế đối với @table_variables ngăn chặn.Chi phí bảo trì cho các bảng #temp lưu trong bộ nhớ cache lớn hơn một chút so với các biến của bảng as illustrated here .

Siêu dữ liệu đối tượng

Điều này về cơ bản là giống nhau cho cả hai loại đối tượng.Nó được lưu trữ trong các bảng cơ sở hệ thống trong tempdb .Điều đơn giản hơn để xem bảng #temp tuy nhiên có thể sử dụng OBJECT_ID('tempdb..#T') để nhập vào các bảng hệ thống và tên được tạo bên trong có tương quan chặt chẽ hơn với tên được xác định trong câu lệnh CREATE TABLE .Đối với các biến bảng, hàm object_id không hoạt động và tên nội bộ hoàn toàn là hệ thống được tạo không có mối quan hệ với tên biến.Dưới đây cho thấy siêu dữ liệu vẫn còn đó tuy nhiên bằng cách nhập vào tên cột (hy vọng là duy nhất).Đối với các bảng không có tên cột duy nhất, object_id có thể được xác định bằng DBCC PAGE miễn là chúng không trống.

/*Declare a table variable with some unusual options.*/
DECLARE @T TABLE
(
[dba.se] INT IDENTITY PRIMARY KEY NONCLUSTERED,
A INT CHECK (A > 0),
B INT DEFAULT 1,
InRowFiller char(1000) DEFAULT REPLICATE('A',1000),
OffRowFiller varchar(8000) DEFAULT REPLICATE('B',8000),
LOBFiller varchar(max) DEFAULT REPLICATE(cast('C' as varchar(max)),10000),
UNIQUE CLUSTERED (A,B) 
     WITH (FILLFACTOR = 80, 
        IGNORE_DUP_KEY = ON, 
        DATA_COMPRESSION = PAGE, 
        ALLOW_ROW_LOCKS=ON, 
        ALLOW_PAGE_LOCKS=ON)
)

INSERT INTO @T (A)
VALUES (1),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13)

SELECT t.object_id,
       t.name,
       p.rows,
       a.type_desc,
       a.total_pages,
       a.used_pages,
       a.data_pages,
       p.data_compression_desc
FROM  tempdb.sys.partitions AS p
       INNER JOIN tempdb.sys.system_internals_allocation_units AS a
        ON p.hobt_id = a.container_id
       INNER JOIN tempdb.sys.tables AS t
        ON t.object_id = p.object_id
       INNER JOIN tempdb.sys.columns AS c
        ON c.object_id = p.object_id
WHERE c.name = 'dba.se'

Đầu ra

Duplicate key was ignored.

    +-----------+-----------+------+-------------------+-------------+------------+------------+-----------------------+
| object_id |  name  | rows |   type_desc   | total_pages | used_pages | data_pages | data_compression_desc |
+-----------+-----------+------+-------------------+-------------+------------+------------+-----------------------+
| 574625090 | #22401542 |  13 | IN_ROW_DATA    |      2 |     2 |     1 | PAGE         |
| 574625090 | #22401542 |  13 | LOB_DATA     |     24 |     19 |     0 | PAGE         |
| 574625090 | #22401542 |  13 | ROW_OVERFLOW_DATA |     16 |     14 |     0 | PAGE         |
| 574625090 | #22401542 |  13 | IN_ROW_DATA    |      2 |     2 |     1 | NONE         |
+-----------+-----------+------+-------------------+-------------+------------+------------+-----------------------+

Giao dịch

Hoạt động trên @table_variables được thực hiện dưới dạng giao dịch hệ thống, không phụ thuộc vào bất kỳ giao dịch người dùng bên ngoài nào, trong khi hoạt động bảng tương đương #temp sẽ được thực hiện như một phần của chính giao dịch người dùng.Vì lý do này, lệnh ROLLBACK sẽ ảnh hưởng đến bảng #temp nhưng để nguyên @table_variable .

DECLARE @T TABLE(X INT)
CREATE TABLE #T(X INT)

BEGIN TRAN

INSERT #T
OUTPUT INSERTED.X INTO @T
VALUES(1),(2),(3)

/*Both have 3 rows*/
SELECT * FROM #T
SELECT * FROM @T

ROLLBACK

/*Only table variable now has rows*/
SELECT * FROM #T
SELECT * FROM @T
DROP TABLE #T

Ghi nhật ký

Cả hai đều tạo bản ghi nhật ký cho nhật ký giao dịch tempdb .Một quan niệm sai lầm phổ biến là đây không phải là trường hợp của các biến bảng nên tập lệnh thể hiện điều này ở bên dưới, nó khai báo một biến bảng, thêm một vài hàng sau đó cập nhật chúng và xóa chúng.

Bởi vì biến bảng được tạo và loại bỏ hoàn toàn khi bắt đầu và kết thúc lô, nên cần sử dụng nhiều lô để xem nhật ký đầy đủ.

USE tempdb;

/*
Don't run this on a busy server.
Ideally should be no concurrent activity at all
*/
CHECKPOINT;

GO

/*
The 2nd column is binary to allow easier correlation with log output shown later*/
DECLARE @T TABLE ([C71ACF0B-47E9-4CAD-9A1E-0C687A8F9CF3] INT, B BINARY(10))

INSERT INTO @T
VALUES (1, 0x41414141414141414141), 
       (2, 0x41414141414141414141)

UPDATE @T
SET  B = 0x42424242424242424242

DELETE FROM @T

/*Put allocation_unit_id into CONTEXT_INFO to access in next batch*/
DECLARE @allocId BIGINT, @Context_Info VARBINARY(128)

SELECT @Context_Info = allocation_unit_id,
       @allocId = a.allocation_unit_id 
FROM  sys.system_internals_allocation_units a
       INNER JOIN sys.partitions p
        ON p.hobt_id = a.container_id
       INNER JOIN sys.columns c
        ON c.object_id = p.object_id
WHERE ( c.name = 'C71ACF0B-47E9-4CAD-9A1E-0C687A8F9CF3' )

SET CONTEXT_INFO @Context_Info

/*Check log for records related to modifications of table variable itself*/
SELECT Operation,
       Context,
       AllocUnitName,
       [RowLog Contents 0],
       [Log Record Length]
FROM  fn_dblog(NULL, NULL)
WHERE AllocUnitId = @allocId

GO

/*Check total log usage including updates against system tables*/
DECLARE @allocId BIGINT = CAST(CONTEXT_INFO() AS BINARY(8));

WITH T
      AS (SELECT Operation,
           Context,
           CASE
            WHEN AllocUnitId = @allocId THEN 'Table Variable'
            WHEN AllocUnitName LIKE 'sys.%' THEN 'System Base Table'
            ELSE AllocUnitName
           END AS AllocUnitName,
           [Log Record Length]
        FROM  fn_dblog(NULL, NULL) AS D)
SELECT Operation = CASE
              WHEN GROUPING(Operation) = 1 THEN 'Total'
              ELSE Operation
             END,
       Context,
       AllocUnitName,
       [Size in Bytes] = COALESCE(SUM([Log Record Length]), 0),
       Cnt = COUNT(*)
FROM  T
GROUP BY GROUPING SETS( ( Operation, Context, AllocUnitName ), ( ) )
ORDER BY GROUPING(Operation),
        AllocUnitName 

Trả về

Xem chi tiết

Screenshot of results

Chế độ xem Tóm tắt (bao gồm ghi nhật ký cho thả ẩn và bảng cơ sở hệ thống)

Screenshot of results

As far as I've been able to discern hoạt động trên cả hai tạo ra số lượng đăng nhập gần bằng nhau.

Trong khi số lượng khai thác gỗ rất giống một sự khác biệt quan trọng là các bản ghi log liên quan đến #temp bảng không thể xóa ra cho đến khi bất kỳ giao dịch người dùng chứa kết thúc do đó, một giao dịch dài chạy mà tại một số điểm ghi vào #temp bảng sẽ ngăn chặn log cắt ngắn trong tempdb trong khi các giao dịch tự trị sinh ra cho các biến bảng không.

Các biến của bảng không hỗ trợ TRUNCATE vì vậy có thể gặp bất lợi khi ghi nhật ký khi yêu cầu là xóa tất cả các hàng khỏi bảng (mặc dù đối với các bảng rất nhỏ DELETEcan work out better anyway)

Cardinality

Nhiều kế hoạch thực hiện liên quan đến các biến của bảng sẽ hiển thị một hàng được ước tính là đầu ra từ chúng.Kiểm tra các thuộc tính của biến bảng cho thấy SQL Server tin rằng biến bảng cósố khônghàng (Tại sao ước tính 1 hàng sẽ được phát ra từ bảng hàng 0 được giải thích bởi here White here).

Tuy nhiên, kết quả hiển thị trong phần trước làm hiển thị một chính xác rows đếm trong sys.partitions .Vấn đề là trong hầu hết các trường hợp, các câu lệnh tham chiếu các biến của bảng được biên dịch trong khi bảng trống.Nếu câu lệnh được biên dịch lại sau @table_variable thì nó sẽ được sử dụng cho cardinality thay thế (Điều này có thể xảy ra do một recompile rõ ràng hoặc có lẽ vì câu lệnh cũng tham chiếu một đối tượng khác gây ra một biên dịch bị trì hoãn hoặc biên dịch lại).

DECLARE @T TABLE(I INT);

INSERT INTO @T VALUES(1),(2),(3),(4),(5)

CREATE TABLE #T(I INT)

/*Reference to #T means this statement is subject to deferred compile*/
SELECT * FROM @T WHERE NOT EXISTS(SELECT * FROM #T)

DROP TABLE #T

Kế hoạch cho thấy số lượng hàng ước tính chính xác sau khi biên dịch hoãn lại.

Shows accurate row count

Trong SQL Server 2012 SP2, cờ theo dõi 2453 được giới thiệu.Thêm chi tiết trong phần "Động cơ quan hệ" here .

Khi cờ theo dõi này được bật, nó có thể khiến các biên dịch lại tự động tính đến số lượng thẻ đã thay đổi như được thảo luận thêm trong thời gian ngắn.

Không có số liệu thống kê cột

Tuy nhiên, việc có số lượng bảng chính xác hơn không có nghĩa là số lượng hàng ước tính sẽ chính xác hơn (trừ khi thực hiện thao tác trên tất cả các hàng trong bảng).SQL Server hoàn toàn không duy trì số liệu thống kê cột cho các biến số của bảng, do đó sẽ dựa vào dự đoán dựa trên vị từ so sánh (ví dụ: 10% của bảng sẽ được trả về cho một số = so với cột không duy nhất hoặc 30% cho so sánh >) .Trong thống kê cột tương phảnduy trì cho #temp bảng.

SQL Server duy trì số lượng sửa đổi được thực hiện cho mỗi cột.Nếu số lượng sửa đổi kể từ khi kế hoạch được biên dịch vượt quá ngưỡng biên dịch lại (RT) thì kế hoạch sẽ được biên dịch lại và cập nhật số liệu thống kê.RT phụ thuộc vào loại bảng và kích thước.

Từ Plan Caching in SQL Server 2008

RT được tính như sau.(n đề cập đến số lượng thẻ của bảng khi kế hoạch truy vấn được biên dịch.)

Bàn thường trực
- Nếu n <= 500, RT = 500.
- Nếu n> 500, RT = 500 + 0,20 * n.

Bảng tạm thời
- Nếu n <6, RT = 6.
- Nếu 6 <= n <= 500, RT = 500.
- Nếu n> 500, RT = 500 + 0,20 * n.
Bảng biến
- RT không tồn tại.Do đó, việc biên dịch lại không xảy ra do những thay đổi về số lượng của các biến bảng.(Nhưng xem ghi chú về TF 2453 bên dưới)

gợi ý KEEP PLAN có thể được sử dụng để đặt RT cho các bảng #temp giống như các bảng cố định.

Hiệu quả ròng của tất cả những điều này là thường các kế hoạch thực hiện được tạo cho #temp bảng là thứ tự có độ lớn tốt hơn so với @table_variables khi nhiều hàng được tham gia vì SQL Server có thông tin tốt hơn để làm việc.

NB1: Biến bảng không có số liệu thống kê nhưng vẫn có thể phát sinh sự kiện biên dịch lại "Thống kê đã thay đổi" theo cờ theo dõi 2453 (không áp dụng cho các gói "tầm thường") Điều này dường như xảy ra dưới cùng một ngưỡng biên dịch lại như được hiển thị cho các bảng tạm thời ở trên với thêm một cái nếu N=0 -> RT = 1 .tức là tất cả các câu lệnh được biên dịch khi biến bảng trống sẽ kết thúc việc biên dịch lại và sửa chữa TableCardinality lần đầu tiên chúng được thực thi khi không trống.Cardinality của bảng thời gian biên dịch được lưu trữ trong kế hoạch và nếu câu lệnh được thực thi lại với cùng một số lượng thẻ (do dòng lệnh kiểm soát hoặc sử dụng lại kế hoạch được lưu trong bộ nhớ cache) thì không xảy ra biên dịch lại.

NB2: Đối với các bảng tạm thời được lưu trong bộ nhớ cache trong các thủ tục được lưu trữ, câu chuyện biên dịch lại phức tạp hơn nhiều so với mô tả ở trên.Xem Temporary Tables in Stored Procedures để biết tất cả các thông tin chi tiết.

Biên dịch lại

Cũng như các biên dịch lại dựa trên sửa đổi được mô tả ở trên #temp các bảng cũng có thể được liên kết với additional compiles đơn giản vì chúng cho phép các hoạt động bị cấm đối với các biến bảng kích hoạt một biên dịch (ví dụ DDL thay đổi CREATE INDEX , ALTER TABLE)

Khóa

has been stated rằng các biến bảng không tham gia khóa.Đây không phải là trường hợp.Chạy các đầu ra bên dưới vào tab thông báo SSMS, chi tiết về các khóa được lấy và phát hành cho một câu lệnh chèn.

DECLARE @tv_target TABLE (c11 int, c22 char(100))

DBCC TRACEON(1200,-1,3604)

INSERT INTO @tv_target (c11, c22)

VALUES (1, REPLICATE('A',100)), (2, REPLICATE('A',100))

DBCC TRACEOFF(1200,-1,3604)

Đối với các truy vấn SELECT từ các biến bảng, Paul White chỉ ra trong các nhận xét rằng chúng tự động đi kèm với một gợi ý ẩn NOLOCK .Điều này được hiển thị dưới đây

DECLARE @T TABLE(X INT); 

SELECT X
FROM @T 
OPTION (RECOMPILE, QUERYTRACEON 3604, QUERYTRACEON 8607)

Đầu ra

*** Output Tree: (trivial plan) ***

       PhyOp_TableScan TBL: @T Bmk ( Bmk1000) IsRow: COL: IsBaseRow1002 Hints( NOLOCK )

Tuy nhiên, tác động của việc này đối với việc khóa có thể khá nhỏ.

SET NOCOUNT ON;

CREATE TABLE #T( [ID] [int] IDENTITY NOT NULL,
            [Filler] [char](8000) NULL,
            PRIMARY KEY CLUSTERED ([ID] DESC))


DECLARE @T TABLE ( [ID] [int] IDENTITY NOT NULL,
            [Filler] [char](8000) NULL,
            PRIMARY KEY CLUSTERED ([ID] DESC))

DECLARE @I INT = 0

WHILE (@I < 10000)
BEGIN
INSERT INTO #T DEFAULT VALUES
INSERT INTO @T DEFAULT VALUES
SET @I += 1
END

/*Run once so compilation output doesn't appear in lock output*/
EXEC('SELECT *, sys.fn_PhysLocFormatter(%%physloc%%) FROM #T')

DBCC TRACEON(1200,3604,-1)
SELECT *, sys.fn_PhysLocFormatter(%%physloc%%)
FROM @T

PRINT '--*--'

EXEC('SELECT *, sys.fn_PhysLocFormatter(%%physloc%%) FROM #T')

DBCC TRACEOFF(1200,3604,-1)

DROP TABLE #T

Cả hai kết quả trả về này theo thứ tự khóa chỉ mục cho thấy rằng SQL Server đã sử dụng allocation ordered scan cho cả hai.

Tôi đã chạy đoạn script trên hai lần và kết quả cho lần chạy thứ hai nằm bên dưới

Process 58 acquiring Sch-S lock on OBJECT: 2:-1325894110:0 (class bit0 ref1) result: OK

--*--
Process 58 acquiring IS lock on OBJECT: 2:-1293893996:0 (class bit0 ref1) result: OK

Process 58 acquiring S lock on OBJECT: 2:-1293893996:0 (class bit0 ref1) result: OK

Process 58 releasing lock on OBJECT: 2:-1293893996:0 

Đầu ra khóa cho biến bảng thực sự rất nhỏ vì SQL Server chỉ cần có khóa ổn định lược đồ trên đối tượng.Nhưng đối với một bảng #temp nó gần như nhẹ ở chỗ nó lấy ra một khóa đối tượng cấp S .Một gợi ý NOLOCK hoặc READ UNCOMMITTED tất nhiên có thể được chỉ định rõ ràng khi làm việc với các bảng #temp .

Tương tự như vấn đề với việc ghi nhật ký giao dịch người dùng xung quanh có thể có nghĩa là các khóa được giữ lâu hơn cho các bảng #temp .Với kịch bản dưới đây

  --BEGIN TRAN;  

     CREATE TABLE #T (X INT,Y CHAR(4000) NULL);

     INSERT INTO #T (X) VALUES(1) 

     SELECT CASE resource_type
          WHEN 'OBJECT' THEN OBJECT_NAME(resource_associated_entity_id, 2)
          WHEN 'ALLOCATION_UNIT' THEN (SELECT OBJECT_NAME(object_id, 2)
                         FROM tempdb.sys.allocation_units a 
                         JOIN tempdb.sys.partitions p ON a.container_id = p.hobt_id
                         WHERE a.allocation_unit_id = resource_associated_entity_id)
          WHEN 'DATABASE' THEN DB_NAME(resource_database_id)                   
          ELSE (SELECT OBJECT_NAME(object_id, 2)
             FROM  tempdb.sys.partitions
             WHERE partition_id = resource_associated_entity_id)
         END AS object_name,
         *
     FROM  sys.dm_tran_locks
     WHERE request_session_id = @@SPID

     DROP TABLE #T

     -- ROLLBACK 

khi chạy bên ngoài một giao dịch người dùng rõ ràng cho cả hai trường hợp, khóa duy nhất được trả về khi kiểm tra sys.dm_tran_locks là một khóa được chia sẻ trên DATABASE .

Khi không chú ý, BEGIN TRAN ... ROLLBACK 26 hàng được trả về cho thấy các khóa được giữ cả trên chính đối tượng và trên các hàng của bảng hệ thống để cho phép khôi phục và ngăn các giao dịch khác đọc dữ liệu không được cam kết.Hoạt động biến bảng tương đương không phải phục hồi giao dịch người dùng và không cần phải giữ các khóa này để chúng tôi kiểm tra trong câu lệnh tiếp theo nhưng theo dõi các khóa được mua và phát hành trong Profiler hoặc sử dụng cờ theo dõi 1200 cho thấy vẫn còn nhiều sự kiện khóa xảy ra

Chỉ mục

Đối với các phiên bản trước SQL Server 2014, chỉ có thể được tạo hoàn toàn trên các biến của bảng dưới dạng tác dụng phụ của việc thêm một ràng buộc duy nhất hoặc khóa chính.Tất nhiên điều này có nghĩa là chỉ các chỉ mục duy nhất được hỗ trợ.Tuy nhiên, một chỉ mục không phân cụm không duy nhất trên một bảng có chỉ mục phân cụm duy nhất có thể được mô phỏng bằng cách chỉ cần khai báo UNIQUE NONCLUSTERED và thêm khóa CI vào cuối khóa NCI mong muốn (SQL Server sẽ do this behind the scenes anyway ngay cả khi NCI không duy nhất có thể được chỉ định)

Như đã trình bày trước đó, index_option khác nhau có thể được chỉ định trong khai báo ràng buộc bao gồm DATA_COMPRESSION , IGNORE_DUP_KEYFILLFACTOR (mặc dù không có điểm nào trong việc thiết lập lại chỉ số trên bảng chỉ số của bạn. !)

Ngoài ra, các biến bảng không hỗ trợ các cột INCLUDE d, các chỉ mục được lọc (cho đến năm 2016) hoặc phân vùng, các bảng #temp (sơ đồ phân vùng phải được tạo trong tempdb).

Các chỉ mục trong SQL Server 2014

Các chỉ mục không duy nhất có thể được khai báo nội tuyến trong định nghĩa biến bảng trong SQL Server 2014. Cú pháp ví dụ cho điều này là bên dưới.

DECLARE @T TABLE (
C1 INT INDEX IX1 CLUSTERED, /*Single column indexes can be declared next to the column*/
C2 INT INDEX IX2 NONCLUSTERED,
       INDEX IX3 NONCLUSTERED(C1,C2) /*Example composite index*/
);

Các chỉ mục trong SQL Server 2016

Từ CTP 3.1, giờ đây có thể khai báo các chỉ mục được lọc cho các biến bảng.Bởi RTM có thể là trường hợp bao gồm các cột cũng được cho phép mặc dù chúng will likely not make it into SQL16 due to resource constraints

DECLARE @T TABLE
(
c1 INT NULL INDEX ix UNIQUE WHERE c1 IS NOT NULL /*Unique ignoring nulls*/
)

Song song

Các truy vấn chèn vào (hoặc sửa đổi khác) @table_variables không thể có kế hoạch song song, #temp_tables không bị hạn chế theo cách này.

Có một cách giải quyết rõ ràng trong việc viết lại như sau cho phép phần SELECT diễn ra song song nhưng kết thúc bằng cách sử dụng bảng tạm thời ẩn (behind the scenes)

INSERT INTO @DATA ( ... ) 
EXEC('SELECT .. FROM ...')

Không có giới hạn như vậy trong các truy vấn màlựa chọntừ các biến bảng as illustrated in my answer here

Sự khác biệt về chức năng khác

  • #temp_tables không thể được sử dụng bên trong một chức năng.@table_variables có thể được sử dụng bên trong các bảng UDF vô hướng hoặc đa câu lệnh.
  • @table_variables không thể có các ràng buộc được đặt tên.
  • @table_variables không thể SELECT -ed INTO , ALTER -ed, TRUNCATE d hoặc là mục tiêu của DBCC lệnh như DBCC CHECKIDENT hoặc SET IDENTITY INSERT và không hỗ trợ gợi ý bảng như WITH (FORCESCAN)
  • CHECK ràng buộc đối với các biến của bảng không được xem xét bởi trình tối ưu hóa để đơn giản hóa, các biến vị ngữ ngụ ý hoặc phát hiện mâu thuẫn.
  • Các biến bảng dường như không đủ điều kiện cho rowset sharing optimisation có nghĩa là xóa và cập nhật các kế hoạch chống lại các kế hoạch này có thể gặp phải nhiều chi phí hơn và PAGELATCH_EX chờ đợi.(Example)

Chỉ nhớ?

Như đã nêu ở đầu, cả hai được lưu trữ trên các trang trong tempdb .Tuy nhiên tôi đã không giải quyết liệu có bất kỳ sự khác biệt nào trong hành vi khi viết các trang này ra đĩa hay không.

Tôi đã thực hiện một số lượng nhỏ thử nghiệm về điều này bây giờ và cho đến nay đã thấy không có sự khác biệt như vậy.Trong thử nghiệm cụ thể tôi đã thực hiện trên ví dụ về SQL Server 250 trang của mình dường như là điểm bị cắt trước khi tệp dữ liệu được ghi vào.

Lưu ý: Hành vi dưới đây không còn xảy ra trong SQL Server 2014 hoặc SQL Server 2012 SP1/CU10 or SP2/CU1 người viết háo hức không còn háo hức để viết các trang vào đĩa.Thêm chi tiết về sự thay đổi đó tại SQL Server 2014: tempdb Hidden Performance Gem .

Chạy đoạn script dưới đây

CREATE TABLE #T(X INT, Filler char(8000) NULL)
INSERT INTO #T(X)
SELECT TOP 250 ROW_NUMBER() OVER (ORDER BY @@SPID)
FROM master..spt_values
DROP TABLE #T

Và giám sát ghi vào tệp dữ liệu tempdb với Trình giám sát quy trình mà tôi không thấy (ngoại trừ đôi khi vào trang khởi động cơ sở dữ liệu ở độ lệch 73,728).Sau khi thay đổi 250 thành 251 tôi bắt đầu thấy viết như dưới đây.

ProcMon

Ảnh chụp màn hình ở trên hiển thị 5 * 32 trang ghi và một trang viết cho biết 161 trang được ghi vào đĩa.Tôi đã có cùng một điểm cắt 250 trang khi kiểm tra với các biến bảng.Kịch bản dưới đây cho thấy một cách khác bằng cách nhìn vào sys.dm_os_buffer_descriptors

DECLARE @T TABLE (
    X    INT,
    [dba.se] CHAR(8000) NULL)

INSERT INTO @T
         (X)
SELECT TOP 251 Row_number() OVER (ORDER BY (SELECT 0))
FROM  master..spt_values

SELECT is_modified,
       Count(*) AS page_count
FROM  sys.dm_os_buffer_descriptors
WHERE database_id = 2
       AND allocation_unit_id = (SELECT a.allocation_unit_id
                    FROM  tempdb.sys.partitions AS p
                   INNER JOIN tempdb.sys.system_internals_allocation_units AS a
                        ON p.hobt_id = a.container_id
                       INNER JOIN tempdb.sys.columns AS c
                        ON c.object_id = p.object_id
                    WHERE c.name = 'dba.se')
GROUP BY is_modified 

Các kết quả

is_modified page_count
----------- -----------
0      192
1      61

Cho thấy 192 trang đã được ghi vào đĩa và xóa cờ bẩn.Nó cũng cho thấy rằng việc ghi vào đĩa không có nghĩa là các trang sẽ bị đuổi khỏi nhóm bộ đệm ngay lập tức.Các truy vấn đối với biến bảng này vẫn có thể được thỏa mãn hoàn toàn từ bộ nhớ.

Trên một máy chủ nhàn rỗi với max server memory được đặt thành 2000 MBDBCC MEMORYSTATUS báo cáo Các trang nhóm bộ đệm được phân bổ là khoảng 1.843.000 KB (khoảng 23.000 trang) tôi đã chèn vào các bảng ở trên trong mỗi hàng của 1.000 trang.

SELECT Count(*)
FROM  sys.dm_os_buffer_descriptors
WHERE database_id = 2
       AND allocation_unit_id = @allocId
       AND page_type = 'DATA_PAGE' 

Cả biến bảng và bảng #temp cho các đồ thị gần như giống hệt nhau và quản lý tối đa hóa vùng đệm trước khi đến điểm mà chúng không hoàn toàn giữ trong bộ nhớ nên dường như không có giới hạn cụ thể nào về mức độ bao nhiêu bộ nhớ hoặc có thể tiêu thụ.

Pages in Buffer Pool


35

Có một vài điều tôi muốn chỉ ra dựa trên nhiều kinh nghiệm cụ thể hơn là học tập.Là một DBA, tôi rất mới vì vậy hãy sửa cho tôi nếu cần.

  1. Các bảng #temp theo mặc định sử dụng đối chiếu mặc định của đối tượng SQL Server.Vì vậy, trừ khi có quy định khác, bạn có thể gặp vấn đề khi so sánh hoặc cập nhật giá trị giữa các bảng #temp và bảng cơ sở dữ liệu, nếu masterdb có đối chiếu khác với cơ sở dữ liệu.Xem: http://www.mssqltips.com/sqlservertip/2440/create-sql-server-temporary-tables-with-the-correct-collation/
  2. Hoàn toàn dựa trên kinh nghiệm cá nhân, bộ nhớ khả dụng dường như có tác dụng giúp hoạt động tốt hơn.MSDN khuyên bạn nên sử dụng các biến bảng để lưu trữ các tập kết quả nhỏ hơn, nhưng hầu hết thời gian khác biệt thậm chí không đáng chú ý.Tuy nhiên, trong các tập lớn hơn, trong một số trường hợp, các biến của bảng sẽ tốn nhiều bộ nhớ hơn và có thể làm chậm truy vấn để thu thập dữ liệu.
+5

Cũng lưu ý rằng đối chiếu trên các bảng #temp * có thể * kế thừa đối chiếu của cơ sở dữ liệu gọi nếu bạn đang sử dụng SQL Server 2012 và cơ sở dữ liệu được chứa. 29 jan. 132013-01-29 19:51:03

  0

Làm rõ trên # 2 cho các bộ nhỏ so với lớn http://stackoverflow.com/a/14465163/5224021 08 feb. 172017-02-08 14:56:07