SQL Server Indexing Interview Questions (BASIC-Part 1)

The bundled content focuses on internals, troubleshooting, query tuning, performance optimization, tips, tricks, and loads of content from the real world.

[prdnyadashpute]

Can we perform dml on index, please share the answer if yes then why and how?

[kevinboodhoo]

1. Yes, we can only have one clustered index per table, so with nonclustered primary key, we can still create a clustered index based on another column for instance, which would not be possible if there's already a clustered primary key.

create table dbo.testTable
(id int not null,
constraint PK_test_id primary key nonclustered(id)
);

2. As part of the key columns, City is sorted and stored for optimal query performance. If City is mainly used in Select clause or Joins and queries are sorting or filtering by this column, then simply include it in the index.

3. Previously, the query was using index and the data is sorted from left to right order i.e. first sort by AddressLine1, then AddressLine2 ... and lastly PostalCode. However, with the new index in place, PostalCode is already sorted, hence no need for Sort operator.

[salmanmirza]

Answer 1 - Yes we can create Non cluster index with Primary key. Index scan will be fast due to leaf node of non cluster index key value. Answer 2- Include Key will include many columns in Non cluster index and improve performance due to not key columns also make index smaller becuase if not part of Index tree. Ans 3- Sort operation will not be remove