Monday, August 26, 2013

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
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

No comments:

Post a Comment