July 9, 2025 at 5:00 am
Comments posted to this topic are about the item Has the PK been used --
July 9, 2025 at 2:08 pm
Maybe I'm being pedantic, but I've never heard of a primary key index. As far as I know, you can make a table with a primary key that is a heap. I wouldn't do that, but I think you can. I think you meant "Clustered Index" OR maybe I'm wrong on this.
But I liked the question. It made me think and I got it right!
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
July 9, 2025 at 4:38 pm
It is also possible to use the DMV sys.dm_db_index_usage_stats, e.g. like this:
Run the following query to see how often each index in the current database
has been used for seeks, scans, lookups, and updates:
SELECT OBJECT_NAME(ius.object_id) AS TableName,
i.name AS IndexName,
ius.user_seeks,
ius.user_scans,
ius.user_lookups,
ius.user_updates
FROM sys.dm_db_index_usage_stats ius
JOIN sys.indexes i ON ius.object_id = i.object_id AND ius.index_id = i.index_id
WHERE ius.database_id = DB_ID();
July 10, 2025 at 7:18 am
"determine whether the query optimizer chose to use a primary key index"
The execution plan will definitely provide that information. However so would sys.dm_db_index_usage_stats in controlled cases where you know how many times the index was used before and after you ran your query, and no other sessions were running.
But I get it, the execution plan is the sure way.
----------------------------------------------------
July 10, 2025 at 12:46 pm
Maybe I'm being pedantic, but I've never heard of a primary key index. As far as I know, you can make a table with a primary key that is a heap. I wouldn't do that, but I think you can. I think you meant "Clustered Index" OR maybe I'm wrong on this.
But I liked the question. It made me think and I got it right!
Primary key, and a unique constraint, is enforced through the use of an index. So, yeah, primary key index.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 11, 2025 at 8:50 pm
Mr. Brian Gale wrote:Maybe I'm being pedantic, but I've never heard of a primary key index. As far as I know, you can make a table with a primary key that is a heap. I wouldn't do that, but I think you can. I think you meant "Clustered Index" OR maybe I'm wrong on this.
But I liked the question. It made me think and I got it right!
Primary key, and a unique constraint, is enforced through the use of an index. So, yeah, primary key index.
Thanks for the info Grant. I had always thought SQL had 2 index types - clustered and non-clustered and that USUALLY your clustered index was made against the primary key, but that it wasn't essential to do that. Learn something new every day, eh?
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply