1

I found the MSDN says that "table variables don't have statistics"

https://msdn.microsoft.com/en-us/library/dd535534(v=sql.100).aspx

Also the following article says the SQL Server doesn't support the statistics for the table variable even in SQL 2014

"temporary tables beat table variables easily because their ability to have statistics improves query optimization dramatically. That remains true with the enhancements in SQL Server 2014."

https://www.brentozar.com/archive/2014/04/table-variables-good-temp-tables-sql-2014/

Then I do a test with SQL Server 2014, and found the statistics for the table variable, so in the SQL 2014, now the table variable has the statistics , right?

USE tempdb;

DECLARE
@tb_table TABLE(
RowID INT IDENTITY(1,1) NOT NULL PRIMARY KEY
,ProductName NVARCHAR(50) NOT NULL UNIQUE
,Indate DATETIME NOT NULL  DEFAULT(GETDATE())
,index IX_Indate NONCLUSTERED (Indate)
);

DECLARE
@table_variable_id bigint
;
SELECT TOP 1 @table_variable_id = object_id
FROM tempdb.sys.all_objects AS A
WHERE parent_object_id = 0
ORDER BY create_date DESC

SELECT  
statistics_name = st.name
,table_name = OBJECT_NAME(st.object_id)
,column_name = COL_NAME(stc.object_id, stc.column_id)
FROM    tempdb.sys.stats AS st WITH(NOLOCK) 
    INNER JOIN tempdb.sys.stats_columns AS stc WITH(NOLOCK)
        ON st.object_id = stc.object_id  
        AND st.stats_id = stc.stats_id 
 WHERE st.object_id = @table_variable_id

enter image description here

BTW, if we remove the line ",index IX_Indate NONCLUSTERED (Indate)" from the script above, we still can see two statistics records in the output in SQL server even in SQL2012.

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
YuFeng Shen
  • 1,475
  • 1
  • 17
  • 41
  • I can't reproduce it. In my test `@table_variable_id` is negative and `sys.stats` has only positive `object_id`. – Vladimir Baranov Mar 16 '17 at 05:00
  • Your SQL Server version is 2014? If I remove the line ",index IX_Indate NONCLUSTERED (Indate), the issue also can happens in both SQL 2012 and 2014. – YuFeng Shen Mar 16 '17 at 05:32
  • I'm using SQL Server 2014 Express. `SELECT @@VERSION` returns "Microsoft SQL Server 2014 (SP1-CU7) (KB3162659) - 12.0.4459.0 (X64) May 27 2016 15:33:17 Copyright (c) Microsoft Corporation Express Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1) ". What version do you use? – Vladimir Baranov Mar 16 '17 at 09:13
  • my versions are Microsoft SQL Server 2014 - 12.0.4100.1 (X64) Apr 20 2015 17:29:27 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.3 (Build 14393: ) (Hypervisor) and Microsoft SQL Server 2012 (SP3) (KB3072779) - 11.0.6020.0 (X64) Oct 20 2015 15:36:27 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.3 (Build 14393: ) (Hypervisor) – YuFeng Shen Mar 16 '17 at 13:36
  • You are using Enterprise Edition. I think this explains it. The article by Kendra Little does say that Enterprise Edition is smarter. I'd try to check with [`DBCC SHOW_STATISTICS`](https://msdn.microsoft.com/en-us/library/ms174384.aspx) the details of the statistics objects that you see and confirm whether they have all the data the normal statistics objects have. Statistics objects created for table variables may have less data, for example, no histogram. I don't have Enterprise Edition at hand to test. – Vladimir Baranov Mar 16 '17 at 21:58
  • 1
    @VladimirBaranov -the reason why you can't reproduce this is because the last query needs to be run in the context of `tempdb`. This is not an EE feature or anything to do with different cardinality guesses in later versions. Or to do with the row count being maintained for table variables. – Martin Smith May 27 '19 at 19:57

2 Answers2

2

No SQL Server doesn't maintain statistics for table variables.

There are three indexes on the table variable in your question and SQL Server inserts a corresponding three rows to sysidxstats

Contains a row for each index or statistics for tables and indexed views

These rows do have the 2 flag set in the status column indicating they are for statistics and so are returned by sys.stats but there is no corresponding statistics object.

You can see this using the query here (requires connecting via the DAC)

SELECT name,
       imageval
FROM   tempdb.sys.stats AS s
       INNER JOIN tempdb.sys.sysobjvalues AS o
               ON s.object_id = o.objid
                  AND s.stats_id = o.subobjid
WHERE  s.object_id = @table_variable_id;

Which returns 

+--------------------------------+----------+
|              name              | imageval |
+--------------------------------+----------+
| PK__#AD773B9__FFEE74513158C9C9 | NULL     |
| IX_Indate                      | NULL     |
| UQ__#AD773B9__DD5A978A62C2C478 | NULL     |
+--------------------------------+----------+

The imageval which would contain the actual statistics is NULL.

This is nothing new. You also see the same in 2008 (though only for the PK and UQ as the inline index definition cannot be used there).

The maintenance of rowcounts is done separately and does not need a full blown statistics object with histogram. And again this is not new.

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
1

I found the following phrase in this article by Itzik Ben-Gan Improvements in Table Variables and Temporary Tables in SQL Server 2014:

Cardinality Estimates with TF 2453

The last improvement that I’ll discuss in this article concerns table variables and is available in SQL Server 2014 RTM CU3 and in SQL Server 2012 SP2. You can find the support entry describing it here.

It’s a well-known fact that SQL Server doesn’t maintain statistics like histograms and density vectors for table variables. However, SQL Server does maintain a count of rows in the table, which in some cases can go a long way in helping the optimizer make optimal choices.

You are using Enterprise Edition. I think this explains it. The article by Kendra Little does say that Enterprise Edition is smarter.

I'd try to check with DBCC SHOW_STATISTICS the details of the statistics objects that you see and confirm whether they have all the data the normal statistics objects have. Statistics objects created for table variables should have less data, for example, no histogram. I don't have Enterprise Edition at hand to test.

Vladimir Baranov
  • 31,799
  • 5
  • 53
  • 90