Do Primary Keys On Table Variables Have No Statistics At All?
0
votes
0
answers
61
views
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?**
Asked by J. Mini
(1239 rep)
Feb 17, 2024, 09:43 PM