CODEWITHSUNDEEP
sql-server
YuFeng Shen
YuFeng Shen

Reputation: 1565

SQL Server doesn't have the statistics for the table variable?

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.

Upvotes: 1

Views: 2945

Answers (2)

Martin Smith
Martin Smith

Reputation: 453278

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.

Upvotes: 3

Vladimir Baranov
Vladimir Baranov

Reputation: 32695

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.

Upvotes: 1

Related Questions