mySQL表变量和临时表的区别_SQLServer中的临时表和表变量之间有什么区别？

只要看看接受的答案中的声明，表变量就不会参与日志记录。

一般情况下，伐木量上的差异似乎是不真实的(至少对于insert/update/delete对表本身的操作，尽管我有自发现以来由于额外的系统表更新，存储过程中缓存的临时对象在这方面有一些微小的差异)。

我查看了两个@table_variable和一个#temp表用于下列操作。成功插入

多行插入语句由于违反约束而回滚。

更新

删除

取消分配

所有操作的事务日志记录几乎相同。

表变量版本实际上有几个额外日志条目，因为它将一个条目添加到(稍后从)sys.syssingleobjrefs基表，但总体上记录的字节数较少，因为表变量的内部名称比#temp表(减少118份)nvarchar)。

要复制的完整脚本(最好在以单个用户模式启动的实例上运行，并使用sqlcmd模式):setvar tablename "@T" :setvar tablescript "DECLARE @T TABLE"/*

--Uncomment this section to test a #temp table

:setvar tablename "#T"

:setvar tablescript "CREATE TABLE #T"

*/USE tempdb

GO

CHECKPOINTDECLARE @LSN NVARCHAR(25)SELECT @LSN = MAX([Current LSN])FROM fn_dblog(null, null) EXEC(N'BEGIN TRAN StartBatch

SAVE TRAN StartBatch

COMMIT

$(tablescript)

(

[4CA996AC-C7E1-48B5-B48A-E721E7A435F0] INT PRIMARY KEY DEFAULT 0,

InRowFiller char(7000) DEFAULT ''A'',

OffRowFiller varchar(8000) DEFAULT REPLICATE(''B'',8000),

LOBFiller varchar(max) DEFAULT REPLICATE(cast(''C'' as varchar(max)),10000)

)

BEGIN TRAN InsertFirstRow

SAVE TRAN InsertFirstRow

COMMIT

INSERT INTO $(tablename)

DEFAULT VALUES

BEGIN TRAN Insert9Rows

SAVE TRAN Insert9Rows

COMMIT

INSERT INTO $(tablename) ([4CA996AC-C7E1-48B5-B48A-E721E7A435F0])

SELECT TOP 9 ROW_NUMBER() OVER (ORDER BY (SELECT 0))

FROM sys.all_columns

BEGIN TRAN InsertFailure

SAVE TRAN InsertFailure

COMMIT

/*Try and Insert 10 rows, the 10th one will cause a constraint violation*/

BEGIN TRY

INSERT INTO $(tablename) ([4CA996AC-C7E1-48B5-B48A-E721E7A435F0])

SELECT TOP (10) (10 + ROW_NUMBER() OVER (ORDER BY (SELECT 0))) % 20

FROM sys.all_columns

END TRY

BEGIN CATCH

PRINT ERROR_MESSAGE()

END CATCH

BEGIN TRAN Update10Rows

SAVE TRAN Update10Rows

COMMIT

UPDATE $(tablename)

SET InRowFiller = LOWER(InRowFiller),

OffRowFiller  =LOWER(OffRowFiller),

LOBFiller  =LOWER(LOBFiller)

BEGIN TRAN Delete10Rows

SAVE TRAN Delete10Rows

COMMIT

DELETE FROM  $(tablename)

BEGIN TRAN AfterDelete

SAVE TRAN AfterDelete

COMMIT

BEGIN TRAN EndBatch

SAVE TRAN EndBatch

COMMIT')DECLARE @LSN_HEX NVARCHAR(25) =

CAST(CAST(CONVERT(varbinary,SUBSTRING(@LSN, 1, 8),2) AS INT) AS VARCHAR) + ':' +

CAST(CAST(CONVERT(varbinary,SUBSTRING(@LSN, 10, 8),2) AS INT) AS VARCHAR) + ':' +

CAST(CAST(CONVERT(varbinary,SUBSTRING(@LSN, 19, 4),2) AS INT) AS VARCHAR)        SELECT

[Operation],

[Context],

[AllocUnitName],

[Transaction Name],

[Description]FROM   fn_dblog(@LSN_HEX, null) AS DWHERE  [Current LSN] > @LSN

SELECT CASE

WHEN GROUPING(Operation) = 1 THEN 'Total'

ELSE Operation       END AS Operation,

Context,

AllocUnitName,

COALESCE(SUM([Log Record Length]), 0) AS [Size in Bytes],

COUNT(*)                              AS CntFROM   fn_dblog(@LSN_HEX, null) AS DWHERE  [Current LSN] > @LSN

GROUP BY GROUPING SETS((Operation, Context, AllocUnitName),())

结果+-----------------------+--------------------+---------------------------+---------------+------+---------------+------+------------------+|                       |                    |                           |             @TV      |             #TV      |                  |+-----------------------+--------------------+---------------------------+---------------+------+---------------+------+------------------+| Operation             | Context            | AllocUnitName             | Size in Bytes | Cnt  | Size in Bytes | Cnt  | Difference Bytes |+-----------------------+--------------------+---------------------------+---------------+------+---------------+------+------------------+| LOP_ABORT_XACT        | LCX_NULL           |                           | 52            | 1    | 52            | 1    |                  || LOP_BEGIN_XACT        | LCX_NULL           |                           | 6056          | 50   | 6056          | 50   |                  || LOP_COMMIT_XACT       | LCX_NULL           |                           | 2548          | 49   | 2548          | 49   |                  || LOP_COUNT_DELTA       | LCX_CLUSTERED      | sys.sysallocunits.clust   | 624           | 3    | 624           | 3    |                  || LOP_COUNT_DELTA       | LCX_CLUSTERED      | sys.sysrowsets.clust      | 208           | 1    | 208           | 1    |                  || LOP_COUNT_DELTA       | LCX_CLUSTERED      | sys.sysrscols.clst        | 832           | 4    | 832           | 4    |                  || LOP_CREATE_ALLOCCHAIN | LCX_NULL           |                           | 120           | 3    | 120           | 3    |                  || LOP_DELETE_ROWS       | LCX_INDEX_INTERIOR | Unknown Alloc Unit        | 720           | 9    | 720           | 9    |                  || LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | sys.sysallocunits.clust   | 444           | 3    | 444           | 3    |                  || LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | sys.sysallocunits.nc      | 276           | 3    | 276           | 3    |                  || LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | sys.syscolpars.clst       | 628           | 4    | 628           | 4    |                  || LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | sys.syscolpars.nc         | 484           | 4    | 484           | 4    |                  || LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | sys.sysidxstats.clst      | 176           | 1    | 176           | 1    |                  || LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | sys.sysidxstats.nc        | 144           | 1    | 144           | 1    |                  || LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | sys.sysiscols.clst        | 100           | 1    | 100           | 1    |                  || LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | sys.sysiscols.nc1         | 88            | 1    | 88            | 1    |                  || LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | sys.sysobjvalues.clst     | 596           | 5    | 596           | 5    |                  || LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | sys.sysrowsets.clust      | 132           | 1    | 132           | 1    |                  || LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | sys.sysrscols.clst        | 528           | 4    | 528           | 4    |                  || LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | sys.sysschobjs.clst       | 1040          | 6    | 1276          | 6    | 236              || LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | sys.sysschobjs.nc1        | 820           | 6    | 1060          | 6    | 240              || LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | sys.sysschobjs.nc2        | 820           | 6    | 1060          | 6    | 240              || LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | sys.sysschobjs.nc3        | 480           | 6    | 480           | 6    |                  || LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | sys.syssingleobjrefs.clst | 96            | 1    |               |      | -96              || LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | sys.syssingleobjrefs.nc1  | 88            | 1    |               |      | -88              || LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | Unknown Alloc Unit        | 72092         | 19   | 72092         | 19   |                  || LOP_DELETE_ROWS       | LCX_TEXT_MIX       | Unknown Alloc Unit        | 16348         | 37   | 16348         | 37   |                  || LOP_FORMAT_PAGE       | LCX_HEAP           | Unknown Alloc Unit        | 1596          | 19   | 1596          | 19   |                  || LOP_FORMAT_PAGE       | LCX_IAM            | Unknown Alloc Unit        | 252           | 3    | 252           | 3    |                  || LOP_FORMAT_PAGE       | LCX_INDEX_INTERIOR | Unknown Alloc Unit        | 84            | 1    | 84            | 1    |                  || LOP_FORMAT_PAGE       | LCX_TEXT_MIX       | Unknown Alloc Unit        | 4788          | 57   | 4788          | 57   |                  || LOP_HOBT_DDL          | LCX_NULL           |                           | 108           | 3    | 108           | 3    |                  || LOP_HOBT_DELTA        | LCX_NULL           |                           | 9600          | 150  | 9600          | 150  |                  || LOP_INSERT_ROWS       | LCX_CLUSTERED      | sys.sysallocunits.clust   | 456           | 3    | 456           | 3    |                  || LOP_INSERT_ROWS       | LCX_CLUSTERED      | sys.syscolpars.clst       | 644           | 4    | 644           | 4    |                  || LOP_INSERT_ROWS       | LCX_CLUSTERED      | sys.sysidxstats.clst      | 180           | 1    | 180           | 1    |                  || LOP_INSERT_ROWS       | LCX_CLUSTERED      | sys.sysiscols.clst        | 104           | 1    | 104           | 1    |                  || LOP_INSERT_ROWS       | LCX_CLUSTERED      | sys.sysobjvalues.clst     | 616           | 5    | 616           | 5    |                  || LOP_INSERT_ROWS       | LCX_CLUSTERED      | sys.sysrowsets.clust      | 136           | 1    | 136           | 1    |                  || LOP_INSERT_ROWS       | LCX_CLUSTERED      | sys.sysrscols.clst        | 544           | 4    | 544           | 4    |                  || LOP_INSERT_ROWS       | LCX_CLUSTERED      | sys.sysschobjs.clst       | 1064          | 6    | 1300          | 6    | 236              || LOP_INSERT_ROWS       | LCX_CLUSTERED      | sys.syssingleobjrefs.clst | 100           | 1    |               |      | -100             || LOP_INSERT_ROWS       | LCX_CLUSTERED      | Unknown Alloc Unit        | 135888        | 19   | 135888        | 19   |                  || LOP_INSERT_ROWS       | LCX_INDEX_INTERIOR | Unknown Alloc Unit        | 1596          | 19   | 1596          | 19   |                  || LOP_INSERT_ROWS       | LCX_INDEX_LEAF     | sys.sysallocunits.nc      | 288           | 3    | 288           | 3    |                  || LOP_INSERT_ROWS       | LCX_INDEX_LEAF     | sys.syscolpars.nc         | 500           | 4    | 500           | 4    |                  || LOP_INSERT_ROWS       | LCX_INDEX_LEAF     | sys.sysidxstats.nc        | 148           | 1    | 148           | 1    |                  || LOP_INSERT_ROWS       | LCX_INDEX_LEAF     | sys.sysiscols.nc1         | 92            | 1    | 92            | 1    |                  || LOP_INSERT_ROWS       | LCX_INDEX_LEAF     | sys.sysschobjs.nc1        | 844           | 6    | 1084          | 6    | 240              || LOP_INSERT_ROWS       | LCX_INDEX_LEAF     | sys.sysschobjs.nc2        | 844           | 6    | 1084          | 6    | 240              || LOP_INSERT_ROWS       | LCX_INDEX_LEAF     | sys.sysschobjs.nc3        | 504           | 6    | 504           | 6    |                  || LOP_INSERT_ROWS       | LCX_INDEX_LEAF     | sys.syssingleobjrefs.nc1  | 92            | 1    |               |      | -92              || LOP_INSERT_ROWS       | LCX_TEXT_MIX       | Unknown Alloc Unit        | 5112          | 71   | 5112          | 71   |                  || LOP_MARK_SAVEPOINT    | LCX_NULL           |                           | 508           | 8    | 508           | 8    |                  || LOP_MODIFY_COLUMNS    | LCX_CLUSTERED      | Unknown Alloc Unit        | 1560          | 10   | 1560          | 10   |                  || LOP_MODIFY_HEADER     | LCX_HEAP           | Unknown Alloc Unit        | 3780          | 45   | 3780          | 45   |                  || LOP_MODIFY_ROW        | LCX_CLUSTERED      | sys.syscolpars.clst       | 384           | 4    | 384           | 4    |                  || LOP_MODIFY_ROW        | LCX_CLUSTERED      | sys.sysidxstats.clst      | 100           | 1    | 100           | 1    |                  || LOP_MODIFY_ROW        | LCX_CLUSTERED      | sys.sysrowsets.clust      | 92            | 1    | 92            | 1    |                  || LOP_MODIFY_ROW        | LCX_CLUSTERED      | sys.sysschobjs.clst       | 1144          | 13   | 1144          | 13   |                  || LOP_MODIFY_ROW        | LCX_IAM            | Unknown Alloc Unit        | 4224          | 48   | 4224          | 48   |                  || LOP_MODIFY_ROW        | LCX_PFS            | Unknown Alloc Unit        | 13632         | 169  | 13632         | 169  |                  || LOP_MODIFY_ROW        | LCX_TEXT_MIX       | Unknown Alloc Unit        | 108640        | 120  | 108640        | 120  |                  || LOP_ROOT_CHANGE       | LCX_CLUSTERED      | sys.sysallocunits.clust   | 960           | 10   | 960           | 10   |                  || LOP_SET_BITS          | LCX_GAM            | Unknown Alloc Unit        | 1200          | 20   | 1200          | 20   |                  || LOP_SET_BITS          | LCX_IAM            | Unknown Alloc Unit        | 1080          | 18   | 1080          | 18   |                  || LOP_SET_BITS          | LCX_SGAM           | Unknown Alloc Unit        | 120           | 2    | 120           | 2    |                  || LOP_SHRINK_NOOP       | LCX_NULL           |                           |               |      | 32            | 1    | 32               |+-----------------------+--------------------+---------------------------+---------------+------+---------------+------+------------------+| Total                 |                    |                           | 410144        | 1095 | 411232        | 1092 | 1088             |+-----------------------+--------------------+---------------------------+---------------+------+---------------+------+------------------+