Sql Server: Understanding on indexes

Understanding on indexes

Indexes are created to provide faster data access during specific data fetch and retrieval form huge data tables.

There are various types of indexes in sql server

a.    Clustered Index

b.    Non clustered index

c.    Unique

d.    Filtered

e.    Spatial

f.     Xml

g.    Full text

h.    Index with computed column

i.     Index with non computed column

Mainly we will discuss here about Clustered index and non clustered index

Before going through understanding about indexes, let’s focus on the questions that one faces.

a.    What is index? Why do we needindexes?

b.    What are the various types ofindexes in sql server?

c.    How will you check if indexesalready exist on a table?

d.    What will happen in case of noindexes?

e.    What is a clustered index?

f.     How many clustered indexes canbe there for one table?

g.    Can you tell some example onclustered index that relates to real life experience?

h.    When you declare primary keyconstraint, then which index is created on that column?

i.     What is non clustered index?

j.     How many non clustered indexescan be created on one table?

k.    Can you tell me some example onnon clustered index that relates to real life experience?

l.     How non-clustered is differentfrom clustered indexes?

m.  What is unique index?

n.    What are the advantages anddisadvantages of index?

o.    How does indexing work?

p.    What does a table with no index called?

q.    Which index are faster clustered or non-clustered?

r.    When should we use clustered index?

s.    When should we use non clustered index?