Tags
2.NonClustered Index, Advanced SQL tutorial pdf, change fill factor while creating NonClustered Index, clustered and non clustered index in sql, Clustered and Nonclustered Indexes Described, Clustered Index, clustered index in sql, Clustered Index vs. Non-Clustered Index in SQL Server, Clustered versus Non Clustered Index, Clustered vs. Nonclustered Index Structures in SQL Server, Covering Index, Covering Index in SQL, Difference Between Clustered and Non-Clustered Indexes, Difference between clustered and nonclustered index, difference between clustered and nonclustered index in sql, difference between clustered and nonclustered index in sql server with example, Differences between Clustered Index Vs NonClustered Index, Differences between Clustered Index Vs Primary Key, Download SQL Questions, Download SQL Server Interview Question in PDF, Download SQL SERVER Interview questions, Download SQL Server Interview questions and answers, download sql server interview questions and answers pdf, download sql server interview questions by Pawan Khowal, download sql server interview questions by Pawan Kumar, download sql server interview questions by Pawan Kumar Khowal, Download T-SQL Interview Questions, Fill factor in Indexes, Filtered Index, Free Download SQL SERVER Interview questions, Indexes in detail, Learn Indexes in detail, NonClustered Index with Included columns, SQL, SQL Common Interview Questions, SQL Common Interview Questions and answers, SQL FAQ, SQL FAQs, SQL Interview Q & A, SQL Interview Questions, SQL Queries asked in interviews, SQL Questions, SQL Server - General Interview Questions and Answers, SQL Server developer Interview questions and answers, SQL Server developer Interview questions with answers, SQL SERVER Indexes, SQL SERVER Interview questions, SQL SERVER Interview questions & Answers, SQL Server Interview questions and answers, SQL Server Interview Questions and Answers - Free PDF, sql server interview questions and answers for net developers, SQL SERVER Tips, SQL Tips & Tricks, SQL Tips and Tricks, SQL Tricks, T-SQL Server Interview Questions, Types of Indexes, Types of Indexes in SQL
SQL SERVER Indexes – ALL (Covers almost everything) [Continued – Page 4]
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) -- |
You must be logged in to post a comment.