Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


SQL SERVER Indexes – ALL (Covers almost everything) [Continued – Page 4]

https://msbiskills.com/

Download – SQL SERVER Indexes Interview Notes – Pawan Kumar Khowal

Differences between Clustered Index Vs NonClustered Index

Clustered Index NonClustered Index
Here data is physically sorted based on the Key columns Here data is not physically sorted based on the key columns
At leaf level you will get entire data means data for all columns. Even if you create clustered index on a single column all the columns will be available at leaf level. Here at leaf level you will get Key value and row locator. For a clustered table, the row locator is the clustered index key. For a heap, a row locator is a RID pointer to the row.
We can have only 1 clustered index per table We can have 999 NonClustered index per table.
Example for Clustered Index is given below-


--

CREATE TABLE testPrimClus4
(
	ID INT
)
GO

CREATE UNIQUE CLUSTERED INDEX Ix_Clx ON testPrimClus4(ID)

--

Example for NonClustered Index is given below-


--

CREATE TABLE testPrimClus4
(
	ID INT
)
GO

CREATE UNIQUE NONCLUSTERED INDEX Ix_NonCl ON testPrimClus4(ID)

--

Differences between Clustered Index Vs Primary Key

Clustered Index Primary Key
Clustered index will create only Index on the table. It will not create constraint on the table. Primary key internally creates 2 objects. They are Index and Primary Key constraint. Both Index and Primary Key constraint can be clustered or non-clustered depending on what you have written in primary key definition. If you don’t specify anything then Unique clustered index will be created and a Primary key constraint (clustered) will be created.
Here if you create non unique clustered index you can insert multiple null values. If you create unique clustered index you can insert single null value. We cannot insert null values if we have Primary key on the table. You will get below error if you try. Msg 515, Level 16, State 2, Line 6 Cannot insert the value NULL into column ‘ID’, table ‘Pawan.dbo.testPrimClus1’; column does not allow nulls. INSERT fails. The statement has been terminated.
We can also add Clustered index after table creation using create index command. We don’t need to alter table in this case. E.g.


--

CREATE TABLE testPrimClus4
(
	ID INT
)
GO

CREATE UNIQUE CLUSTERED INDEX Ix_Clx ON testPrimClus4(ID)

--

We can add primary key after that creating table using below alter command. Please note that we need to first drop existing constraints. Also primary key column should be non-null. E.g.


--

CREATE TABLE testPrimClus113
(
	ID INT NOT NULL
)
GO

ALTER TABLE testPrimClus113 ADD PRIMARY KEY (ID)

--

Advertisements