When working with SQL Server, it is important to understand the concepts of clustered index and primary key. Many developers mistakenly believe that primary key and clustered index are the same thing, but they are actually different.

By default, SQL Server automatically creates a clustered index on a table when a primary key is defined. This default behavior has led to confusion among developers. In a recent survey, only a small percentage of participants were aware of the difference between primary key and clustered index.

There are four different scenarios to consider:

1. Primary key defaults to clustered index
2. Primary key is defined as a non-clustered index
3. Primary key defaults to non-clustered index with another column defined as a clustered index
4. Primary key defaults to clustered index with other indexes defaulting to non-clustered

It is recommended to read a previous blog post that explains each of these scenarios in detail.

Now, let’s discuss why it is important to identify tables where the clustered index is not a primary key. The reason is simple – while a primary key can be either clustered or non-clustered, it is considered a best practice to have the primary key as a clustered index. This is because having a clustered index on the same table as the primary key can improve performance.

To identify tables where the clustered index is not a primary key, you can use the following script:

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 this script, it will list all the tables in the database where there is a clustered index on the table, but it is not a primary key.

Now, I challenge you to execute the above script on your server and see if you find any records in the result set. If you do, try to figure out the business reason behind having a different primary key and clustered index for that table. Feel free to share your findings in the comments below. I will be happy to highlight your answer with due credit in a future blog post.

Understanding the concepts of clustered index and primary key in SQL Server is crucial for optimizing database performance. By following best practices and correctly configuring these indexes, you can ensure efficient data retrieval and improve overall system performance.