About the differences between temporal tables and table variables 1

Temporary tables, table variables, these 2 brothers are not unfamiliar when used in peacetime. Most of the time, we borrowed the 2 brothers to do the caching of intermediate result sets. Then I'll simply say that the 2 brothers in the query time some of the small differences

1, first I set up a table to store 100W of data

--the data is simple, but every line is full ~CREATE TABLETMP (IDINT PRIMARY KEY, Col1CHAR(8000))INSERT  intodbo. TMP (ID, Col1)SELECT TOP 1000000Row_number () Over(ORDER  by(SELECT 1)) asROW,'a' fromsys.objects a,sys.objects b,sys.objects C

2, and then create a temporary table and table variables query, not much, just check 1000

SET TRANSACTION Isolation  Level READ Uncommitted;DECLARE @Tb  as TABLE(IDINT)IF object_id('tempdb: #Tmp') is  not NULL    DROP TABLE#Tmp; withCte as (    SELECT 1  asRowUNION  All    SELECTRow+ 1         fromCTEWHERERow<  -)SELECT TOP  +CHECKSUM (NEWID())%1000000  asRow into#Tmp fromCTE, CTE vINSERT  into @Tb(ID)SELECT TOP  +CHECKSUM (NEWID())%1000000     from#TmpDECLARE @DT DATETIME=GETDATE()SELECT *     fromdbo. TMP aWHERE EXISTS(SELECT *  from @Tb WHEREId=a.id)SELECT DATEDIFF(MS,@DT,GETDATE())SELECT *     fromdbo. TMP aWHERE EXISTS(SELECT *  from#TmpWHERERow=a.id)SELECT DATEDIFF(MS,@DT,GETDATE())

Then the result is that using temporal table execution time is probably half as short as the table variable!!

I just looked at it. Execution Plan discovery execution plan shows the query cost table variable occupies less proportion Ah!!!

Obviously the implementation plan is this paper, why I see the execution time and evaluation of the plan is inconsistent!! This is the gap between me and Ming _ (: З"∠) _

Then I looked at the IO output, it dawned on the table variable reading caused more than 900 physics read! While the temporary table is written in physical reading only 1 times, so the execution time above is not the same!

--table Variable modeTable'TMP'。 Scan Count0, logical Read4000Times, physical reads968Times, pre-read0Times, LOB logical reads0Times, lob physical reads0Times, LOB pre-read0times. Table'#A5842A1D'。 Scan Count1, logical Read2Times, physical reads0Times, pre-read0Times, LOB logical reads0Times, lob physical reads0Times, LOB pre-read0times. --temporary table modeTable'TMP'。 Scan Count0, logical Read6402Times, physical reads1Times, pre-read8040Times, LOB logical reads0Times, lob physical reads0Times, LOB pre-read0times. Table'#Tmp______________________________________________________________________________________________________ __________000000000018'。 Scan Count1, logical Read2Times, physical reads0Times, pre-read0Times, LOB logical reads0Times, lob physical reads0Times, LOB pre-read0Times.

PS: Sometimes if you use table variables, intermediate cache of verses more (perhaps more than hundreds of, it may affect the subsequent query efficiency, this time can try to use temporary table to replace, perhaps efficiency immediately up ~)

The place that is not good, also invites you to shoot the brick guidance Ah!

About the differences between temporal tables and table variables 1