0

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?

PMP
  • 13
  • 1
  • Just declare the column(s) as the primary key. Clustered indexes are an implementation detail. You only need to use them if you know how to use them. – Gordon Linoff Jun 22 '18 at 12:41
  • Yes i have done the same. but my doubt raises when DB2 create primary index while creating Primary key while SQL create clustered index when creating primary key. – PMP Jun 22 '18 at 12:44
  • 1
    . . They are both indexes that accomplish the sample purpose with respect to the primary key. It is like caring whether you are flying on a Boeing or Airbus. They are different, but if you don't know or care about the differences, you can just book the flight. – Gordon Linoff Jun 22 '18 at 13:02

1 Answers1

1

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:

Markus Winand
  • 8,371
  • 1
  • 35
  • 44