Indexes on sql Server table

I have discussed with many interviewer and interviewee about the SQL Server tricky interview Questions. Most of them told me that Indexes and Triggers are most important area. Today we will discuss more about the Indexes and Triggers. To make it simple i will post question first and then explain the answer.

1. What is clustered and Non Clustered Index?

The leaf nodes of a clustered index contain the data pages i.e. in simple terms the clustered index stores the information how physically information is stored.It is same as the index page of a book.

A nonclustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. 
In simple terms we can say that the non clustered Index stores the logical ordering of data.
  
2. How many Cluster Index you can create on a table?

One Clustered Index is allowed per table

3. Can we create Clustered Index on Null value column? 
Yes we can create Clustered Index on a non-unique and nullable column. If the clustered index is not a unique index, SQL Server makes any duplicate keys unique by adding an internally generated value called a unique identifier.

4. How many Null value can a clustered Index column can take?


Only one NULL value is allowed in Clustered Index column as it is considered as a unique value.

5. Can we create Clustered Index on Non primary key column? 

 yes. We can create Clustered Index on Non primary key.

6. How many NON-Clustered Index you can create on a table?

SQL Server allows maximum up to 249 NON-CLUSTERED indexes that can be created in single table. This has been increased to 999 in SQL Server 2008 R2 version for both 32-bit and 64-bit server.

7. Can we drop Cluster Index on Primary Key. If yes then how it will work ?

Yes We can drop a Clustered Index on Primary key column and create it on another column.

8. Can we create a table without Clustered Index.

Yes we can.The table is called as Heap.Data is stored in the heap without specifying an order. To find a row we must now RID.  

9. Can we create Non-Clustered index on a table which donst have Clustered Index.

Yes we can create Non clustered index on a table which don't have clustered index