Recently I was asked to find all the Primary keys that are not clustered Indexes in the database.I constructed this script which will help us to locate the primary keys in the database which are not clustered index.
f-730512,
2013-09-18 (first published: 2013-09-04)
Recently I was asked to find all the Primary keys that are not clustered Indexes in the database.I constructed this script which will help us to locate the primary keys in the database which are not clustered index.
Use Adventureworks2012 go SELECT idx.name AS IndexName , fg.name AS Filegroup , object_name(idx.object_id) AS TableName , CASE WHEN index_id = 1 THEN 'Clustered' WHEN index_id = 0 THEN 'heap' ELSE 'Non Clustered' END AS TypeOfIndex , CASE WHEN idx.is_primary_key = 1 AND index_id > 1 THEN 'Primary key as Unique Non Clustered Index' WHEN idx.is_primary_key = 1 AND index_id = 1 THEN 'PK and Clustered' ELSE 'Unique Non-Cl' END AS PrimaryKeyAsNonCLIndex FROM sys.filegroups fg JOIN sys.indexes idx ON fg.data_space_id = idx.data_space_id JOIN sys.objects O ON object_name(idx.object_id) = O.name WHERE object_name(idx.object_id) NOT LIKE 'sys%' AND idx.is_primary_key = 1 AND index_id > 1 ORDER BY TableName --Snapshot below----
I inherited a database that was getting large and not performing well. After adding primary keys life got better.
This is the first of a series of articles to analyze the use of surrogate keys in different scenarios.
In this article Brian Ellul explorea how it's possible to eliminate the SORT operator (and its performance cost) on performing SELECTS statements.
It is strange that one can ask simple questions about extended events or Hekaton at professional events and conferences without feeling embarrassed, yet nobody likes to ask vital questions about SQL Server primary keys and foreign keys. Fear not, here are 13 questions you were too shy to ask, answered.
2014-10-14
9,933 reads
Every database developer uses keys, but without always understanding all the ramifications. They come with few hard and fast rules, but if you get them right from the start with a database design, the whole process of database development is simpler, and the result is likely to perform better. We asked Phil for advice, little knowing that the explanation might take a while.
2013-12-19
5,005 reads