Sql Server: index part 1

a.    What is index? Why do we need indexes?
b.    What are the various types of indexes in sql server?
c.    How will you check if indexes already exist on a table
d.    What will happen in case of no 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

  

    Using below procedure we can identify, if indexes exist on a table

Execute sp_helpindex table_name

For example:

In case of No index

If no index exists on a table, a table scan must be performed for each table referenced in a database query. The larger the table, the longer a table scan takes because a table scan requires each table row to be accessed sequentially. Although a table scan might be more efficient for a complex query that requires most of the rows in a table, for a query that returns only some table rows an index scan can access table rows more efficiently.

Next for more questions and answers on indexes
Understanding on Indexes