临时表-表变量的统计信息

--临时表(堆表,没有主键没有索引)
CREATE TABLE #tempTest1
(
    --FID INT IDENTITY(1,1) PRIMARY KEY,
    FAddTime DATETIME,
    FGuid UNIQUEIDENTIFIER, 
    FSessionID INT
)

DECLARE @i INT = 0
WHILE(@i <= 50)
BEGIN
    INSERT INTO #tempTest1 (FAddTime,FGuid,FSessionID)
    VALUES (GETDATE(),NEWID(),@@SPID)
    
    SET @i += 1; 
END

--临时表的统计信息
SET STATISTICS XML ON

SELECT * FROM #tempTest1

SET STATISTICS XML OFF 

DROP TABLE #tempTest1

 

--临时表(有主键)
CREATE TABLE #tempTest1
(
    FID INT IDENTITY(1,1) PRIMARY KEY, --主键
    FAddTime DATETIME,
    FGuid UNIQUEIDENTIFIER,
    FSessionID INT
)

DECLARE @i INT = 0
WHILE(@i <= 50)
BEGIN
    INSERT INTO #tempTest1 (FAddTime,FGuid,FSessionID)
    VALUES (GETDATE(),NEWID(),@@SPID)
    
    SET @i += 1; 
END

--临时表的统计信息
SET STATISTICS XML ON

SELECT * FROM #tempTest1

SET STATISTICS XML OFF 

DROP TABLE #tempTest1

 

 

--表变量
DECLARE @tTest2 TABLE
(
    --FID INT IDENTITY(1,1) PRIMARY KEY,
    FAddTime DATETIME,
    FGuid UNIQUEIDENTIFIER, 
    FSessionID INT
)

DECLARE @i INT = 0
WHILE(@i <= 50)
BEGIN
    INSERT INTO @tTest2 (FAddTime,FGuid,FSessionID)
    VALUES (GETDATE(),NEWID(),@@SPID)
    
    SET @i += 1; 
END

--表变量的统计信息
SET STATISTICS XML ON

SELECT * FROM @tTest2

SET STATISTICS XML OFF 

 

--表变量(有主键)
DECLARE @tTest2 TABLE
(
    FID INT IDENTITY(1,1) PRIMARY KEY,
    FAddTime DATETIME,
    FGuid UNIQUEIDENTIFIER, 
    FSessionID INT
)

DECLARE @i INT = 0
WHILE(@i <= 50)
BEGIN
    INSERT INTO @tTest2 (FAddTime,FGuid,FSessionID)
    VALUES (GETDATE(),NEWID(),@@SPID)
    
    SET @i += 1; 
END

--表变量的统计信息
SET STATISTICS XML ON

SELECT * FROM @tTest2

SET STATISTICS XML OFF 

 

 

SET STATISTICS XML ON

--CTE
;WITH cteTest3(FAddTime,FGuid,FSessionID)
AS
(
    SELECT GETDATE(),NEWID(),@@SPID
    UNION ALL
    SELECT GETDATE(),NEWID(),@@SPID
    UNION ALL
    SELECT GETDATE(),NEWID(),@@SPID
    UNION ALL
    SELECT GETDATE(),NEWID(),@@SPID
    UNION ALL
    SELECT GETDATE(),NEWID(),@@SPID
)

--SET STATISTICS XML ON

SELECT * FROM cteTest3

SET STATISTICS XML OFF 

 

总结：

根据执行计划的 "Estimated Number of Rows"(预估行数) = 1 可以知道。

临时表是有统计信息的。

表变量没有统计信息。

CTE有统计信息的。