MS SQL SERVER INDEX INTERVIEW QUESTIONS AND ANSWERS FOR EXPERIENCED AND FRESHER

This is the new set of MS SQL Server index related interview questions-answers, In every sql server interview/.net interview you must face questions related to speed up query? or what is index? etc.
So set of questions will clear your doubt about index. This set is for both experienced as well as fresher ms sql server users,

First you can see the SQL Server index video tutorial by Shivprasad Koirala
https://www.youtube.com/watch?v=rtmeNwn4mEg
Then go-through following questions-answers series


Lest Start :
What is an Index?
Indexes of SQL Server are similar to the indexes in books. They help SQL Server retrieve the data quicker. Index is a database object, which can be created on one or more columns. 
When creating the index will read the column(s) and forms a relevant data structure to minimize the number of data comparisons. The index will improve the performance of data retrieval and adds some overhead on data modification such as create, delete and modify. So it depends on how much data retrieval can be performed on table versus how much of DML (Insert, Delete and Update) operations.

How many clustered indexes there can be in one table?
Only one.

How many non-clustered indexes there can be in one table?
For SQL Server 2005: 249 Nonclustered Index
For SQL Server 2008: 999 Nonclustered Index

What is clustered table?
A table having clustered index also called as clustered table.

Disadvantages of the Indexes?
Inserts and updates takes longer time with clustered index.
It takes some disk space to create Non-Clustered index

How many columns can we include in non clustered index?
Max 16 columns can be combined to make a single composite index key, with a cap that the max size of the combined values is 900 bytes.

Why Use an Index?
Use of SQL server indexes provide many facilities such as:
* Rapid access of information
* Efficient access of information
* Enforcement of uniqueness constraints

Types of Indexes?
SQL Server has two major types of indexes:
Clustered 
Non-Clustered

What is Clustered index?
A clustered index sorts and stores the data rows of the table or view in order based on the clustered index key. The clustered index is implemented as a B-tree index structure that supports fast retrieval of the rows, based on their clustered index key values.

What is Non-Clustered index?
A nonclustered index can be defined on a table or view with a clustered index or on a heap. Each index row in the nonclustered index contains the nonclustered key value and a row locator. This locator points to the data row in the clustered index or heap having the key value. The rows in the index are stored in the order of the index key values, but the data rows are not guaranteed to be in any particular order unless a clustered index is created on the table.
For understand deeply follow the link
http://blog.sqlauthority.com/2013/02/10/sql-server-primary-key-and-nonclustered-index-in-simple-words/

Write the T-Sql statement/syntex for create and index?
Creates an index on a table. Duplicate values are allowed:
CREATE INDEX index_name
ON table_name (column_name)

SQL CREATE UNIQUE INDEX Syntax
Creates a unique index on a table. Duplicate values are not allowed:
CREATE UNIQUE INDEX index_name
ON table_name (column_name)

Difference Between Unique Index vs Unique Constraint?
Unique Index and Unique Constraint are the same. They achieve same goal. SQL Performance is same for both.

What is the difference between a Clustered and Non-Clustered Index?
Clustered Index 
1.There can be only one Clustered index for a table 
2.Usually made on the primary key 
3.The leaf nodes of a clustered index contain the data pages.
4. A clustered index actually describes the order in which records are physically stored on the disk, hence the reason you can only have one.

Non-Clustered Index 
1.There can be only 249/999(2005/2008) Non-Clustered index for a table 
2.Usually made on the any key 
3.The leaf node of a nonclustered index does not consist of the data pages. Instead, the leaf nodes contain index rows
4.A Non-Clustered Index defines a logical order that does not match the physical order on disk.

Is Clustered index store the table data in sorted order?
Yes!
When you create an index on a column or number of columns in MS SQL Server, you can specify that the index on each column be either ascending or descending.


Generally which index perform faster Clustered or Non-Clustered?
Generally it is faster to read from a clustered index if you want to get back all the columns. You do not have to go first to the index and then to the table.
But not its not always true, have a look on the following article
http://www.mssqltips.com/sqlservertip/3041/when-sql-server-nonclustered-indexes-are-faster-than-clustered-indexes/

What is Fill Factor and What is the Best Value for Fill Factor?
Fill factor is the value that determines the percentage of space on each leaf-level page to be filled with data. In an SQL Server, the smallest unit is a page, which is made of  Page with size 8K. Every page can store one or more rows based on the size of the row. The default value of the Fill Factor is 100, which is same as value 0. The default Fill Factor (100 or 0) will allow the SQL Server to fill the leaf-level pages of an index with the maximum numbers of the rows it can fit. There will be no or very little empty space left in the page, when the fill factor is 100.
Ref. http://blog.sqlauthority.com/2011/01/31/sql-server-what-is-fill-factor-and-what-is-the-best-value-for-fill-factor/



Ref. Sqlauthority.com and some other sites.