What are the differences between CLUSTERD INDEX and
NONCLUSTERD INDEX [CLUSTERD INDEX vs NONCLUSTERD INDEX]?
|
CLUSTERED INDEX
|
NONCLUSTERED INDEX
|
|
|
1
|
The leaf level of a CLUSTERED INDEX is the actual data page; data is
physically stored on a data page in ascending order.
|
The leaf level of a NONCLUSTERED INDEX contains key values not
the actual data, these key values map to pointers or clustering key that
locates rows in data pages.
|
|
2
|
Table can have only one CLUSTERED INDEX.
|
Table can have more
than one NONCLUSTERED INDEX.
SQL server 2008 and
later versions, Maximum 999 Non Clustered Indexes can be created on a
table.
|
|
3
|
When you create a
PRIMARY KEY on a table, SQL server creates a CLUSTERED INDEX automatically on the table unless specified
“CLUSTERED or NON CLUSTERED”
NOTE: Create the
PRIMARY Key column with CLUSTERED INDEX by
specifying “CLUSTERED”)
EXAMPLE:
CREATE TABLE [dbo].[member](
[member_no]
[int] IDENTITY(1,1) NOT NULL,
[lastname]
[varchar](50)
NOT NULL,
[firstname]
[varchar](50)
NULL,
[salary]
[decimal](10,2) NULL
)
GO
ALTER TABLE [member] ADD CONSTRAINT
[PK_member_lastname] PRIMARY KEY --NONCLUSTERED
(
[lastname]
ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF,ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS
= ON, FILLFACTOR = 90) ON [PRIMARY]
GO
|
When you create a
PRIMARY KEY on table, SQL server creates a NONCLUSTERED INDEX automatically by specifying “NONCLUSTERED”,
it is not recommended.
EXAMPLE:
CREATE TABLE [dbo].[member](
[member_no]
[int] IDENTITY(1,1) NOT NULL,
[lastname]
[varchar](50)
NOT NULL,
[firstname]
[varchar](50)
NULL,
[salary]
[decimal](10,2) NULL
)
GO
ALTER TABLE [member] ADD CONSTRAINT
[PK_member_lastname] PRIMARY KEY NONCLUSTERED
(
[lastname]
ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF,ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS
= ON, FILLFACTOR = 90) ON [PRIMARY]
GO
|
|
4
|
Data retrieval is faster than NONCLUSTERED INDEX,because
the leaf level is the bottom level of CLUSTERED INDEX contains the data pages
or complete data rows.
|
Data retrieval is slower than CLUSTERED INDEX, because the
leaf level is the bottom level of a NONCLUSTERED INDEX contains key values not the actual data.
These key values map to pointers or clustering keys the locate rows in the
data pages.
|
|
5
|
Do not need extra
space to store logical structure.
CLUSTERED INDEX stores
the base table data in same physical order as index’s logical order, so it
does not require additional storage space.
|
Use extra space to
store logical structure.
In a NONCLUSTERED
INDEX, the index is stored in a separate location which requires additional
storage space.
|
