sqlsql-serverdb2database-migrationsql-server-migration-assi

Primary key -Primary Index in DB2 Vs Primary Key - Clustered index in SQL

Primary Index in DB2 Vs Clustered index in SQL. While migrating db2 to SQL i came to know that primary key in db2 is creating primary index not clustered index.

I found primary index is different from clustered index in DB2 is it correct? In that case which index we need to use in SQL?

Solution

  • A SQL Server Clustered Index is basically like the following index in DB2

    CREATE INDEX ... ON ... (pk-columns) INCLUDE (all-other-columns)

    I.e. it is useful if your main use of this index is to fetch by the pk-columns and select many other columns.

    As this index contains all columns, there is no separate table structure (no heap table) for SQL Server tables that have a clustered index.

    IMHO the clustered index is heavily over used in SQL Server. I wrote this article to explain one problem clustered indexes introduce: