Let us first see script which will return us tablename with schema where clustered index is not a primary key.
-- Identify table where clustered index is not a primary key SELECT OBJECT_SCHEMA_NAME(so.[object_id]) AS SchemaName, so.name AS TableName, si.name AS IndexName, si.type_desc AS IndexType, si.is_primary_key FROM sys.indexes si JOIN sys.tables so ON si.[object_id] = so.[object_id] WHERE si.type IN (0, 2) AND si.is_primary_key=1 ORDER BY so.name
When you execute the script, it will list all the tables in the database where there are clustered index on the table but they are not a primary key.
Default Behavior of SQL Server
The default behavior of SQL Server is to create automatically Clustered Index (CI) on the table when we defined Primary Key (PK). This has created many confusions and plenty of developers still believe that Primary Key and Clustered Index is the same thing. In the last survey of 100’s people who attended my user group presentation, you may find it surprising that only 8 people knew that they are different.
There are four different scenarios:
- Scenario 1: PK will default to CI
- Scenario 2: PK is defined as a Non-CI
- Scenario 3: PK defaults to Non-CI with another column defined as a CI
- Scenario 4: PK defaults to CI with other index defaults to Non-CI
I strongly suggest you to read my earlier blog post where I explained in detail each of these scenarios. Primary Key and Non-clustered Index in Simple Words
Clustered Index – Not a Primary Key
The real question is that why do we have to find all the tables where we have clustered index on the table but it is not a primary key. What is the reason for behind the script? The reason is pretty simple.
Primary Key (PK) can be Clustered (CI) or Non-clustered (Non-CI) but it is a common best practice to create a Primary Key (PK) as Clustered Index (CI).
It is considered as the best practice when we Clustered Index on the same table as a Primary Key and that is why SQL Server has defaulted to this behavior (which has generated the confusion). In my career of performance tuning expert I have observed only once where PK and CI are different.
Now here is a question for you – I want you to execute the above script on your server and see if you find any record as a result set. If you see rows in the resultset, I would like you to do figure out what is the business reason behind the table in the reseltset that it has to have different PK and CI. Once you find out the result, I would like to request you to post your answer as a comment. I will be happy to highlight your answer with due credit in the future blog post.
Reference: Pinal Dave (https://blog.sqlauthority.com)
8 Comments. Leave new
Well that’s depends, if you have identity columns as PK :-( and think use that column as CI you are wasting both, PK and CI! and that is the common sceneries… at least on this side of the world. and that is useless!. there a lot sceneries for both cases PK = CI and PK CI. the first case is greats when the table have Natural Key and put in order the columns thinking on get benefits of density and selectivity… if don’t use selectivity and density will waste a Clustered index.
but any way is a nice post. ;)
Performance. If I’m mostly accessing a table via a FK, then I cluster on that so all of the records are together.
Please I have a table (marks) with about 586065 rows; when you try to insert records of about 1,200 rows into the table (marks) it takes about 140 seconds to complete insertion.
When you try to select from the table (marks), it takes about 10 seconds to fetch records.
Is there a way to minimize the time taken to insert or search significantly?
I’m using stored procedure in SQL Server 2008 r2
below is the stored procedure:
USE [MyDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [MyDB].[Mraks_UPDATE]
(
@claEID varchar(50) = null,
@stID varchar(50) = null,
@SeCode varchar(10) = null,
@exID varchar(5) = null,
@dTaken smalldatetime = null,
@mark float = null
)
AS
UPDATE
[MyDB].[Mraks]
SET
[CleID] = @claEID,
[StID] = @stID,
[sCode] = @SeCode,
[ExamID] = @exID,
[DateTaken] = @dTaken,
[Marks] = @mark
WHERE
[CleID] = @claEID
AND
[StID] = @stID
AND
[sCode] = @SeCode
I have fired below Select Statement and i got Error. Any one Help me.
select top 100 MenuID,MenuGroup,MenuName,ObjectName,ObjectTitle from tblMenuMaster
where ApplicationID=3 and recStatus=’A’
Error Message.
A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 – The semaphore timeout period has expired.)
As it says, there was a connection loss to the server.
Yes, both are clearly different. Primary key stands for uniqueness of column where Clustered Index stands to maintain order of data to store inside the table.
So, if we have a requirement where we will need to maintain one column with unique records, but need to maintain order of the data based on another column then your scenario is useful.
Of course this is very rare scenario.
Please guide me if any correction needed from my comment. :)
Btw, nice information sharing @Pinal
This is connection level error. Connect to database again and check.
FYI, If you have tables without PK and Clustered Index defined then below SQL identifies those tables (mostly in DW you see this kind of tables).
— Identify table (with NO PK) and has a clustered index.
SELECT
OBJECT_SCHEMA_NAME(so.[object_id]) AS SchemaName,
so.name AS TableName,
si.name AS IndexName,
si.type_desc AS IndexType,
si.is_primary_key,
*
FROM sys.indexes si
JOIN sys.tables so ON si.[object_id] = so.[object_id]
WHERE si.type = 1 and si.is_primary_key = 0 — si.type IN (0, 2) — AND si.is_primary_key=1
ORDER BY so.name