0

My confusion is down to an apparent contradiction in principles. Primary keys are indexes and those always have statistics, but table variables notoriously always don't have statistics. What follows is an experiment to determine which principle wins out. Run the following in a very quiet database. Let's assume SQL Server 2019.

USE tempdb

SELECT COUNT(*) FROM sys.stats AS stat
CROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) props


DECLARE @FOO TABLE (BAR INT NOT NULL PRIMARY KEY CLUSTERED);


INSERT @FOO (BAR) VALUES (1), (2), (3);


SELECT COUNT(*) FROM sys.stats AS stat
CROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) props

And you will find that you have the same number outputted twice, implying that your primary key has no statistics.

Now repeat this with a temp table.

USE tempdb

SELECT COUNT(*) FROM sys.stats AS stat
CROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) props


CREATE TABLE #FOO_TEMP (BAR INT NOT NULL PRIMARY KEY CLUSTERED);


INSERT #FOO_TEMP (BAR) VALUES (1), (2), (3);


SELECT COUNT(*) FROM sys.stats AS stat
CROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) props

And you will find that your number of rows has changed, implying that your primary key has statistics.

Does this prove that primary keys on table variables have no statistics but those on temp tables do?

J. Mini
  • 109
  • 2
  • 15
  • 2
    It may finally prove it to you, but this has been documented by Microsoft and countless bloggers with varying levels of influence since time immemorial. – Erik Darling Feb 18 '24 at 13:45
  • @ErikDarling It's well-known that table variables have no statistics, but I've never seen it pointed out that their properties also don't. – J. Mini Feb 18 '24 at 20:58
  • 1
    That’s either a failure in reading comprehension or a failure in research on your part then. – Erik Darling Feb 18 '24 at 21:02

0 Answers0