Monday, August 26, 2013

Sql Server: index part 3

a.    What is non clustered index?

b.    How many non clustered indexes can be created on one table?

c.    Can you tell me some example on non clustered index that relates to real life experience?

d.    How non-clustered is different from clustered indexes?

Non-Clustered Index

 non-clustered index can be created as shown below:
create nonclustered index indexname on [TableName](ColumnName)

Some points regarding non-clustered indexex 
  • It’s similar to index in textbook, index at one place and data at other place. These indexes will have the pointers to the storage location of the actual data.

  • A table can have more than 1 non-clustered index, just like in a book, one index in starting of the book, another at the end of the book.

  • A table can only have one Clustered index and up to 249 Non-Clustered Indexes.  If a table does not have a clustered index it is referred to as a Heap.

Difference between Clustered and non clustered index
  • Max one clustered index and max 249 non clustered can be created on each table.

  • Clustered indexing is faster than non-clustered index. As clustered index will always cover  a query ,since it contains all of the data in the leaf node itself.

  •  Non-clustered indexes requires extra disk storage space.

for more questions and answers on indexes
Previous      Next

No comments:

Post a Comment