CODEWITHSUNDEEP
sql-server-2008
Siva Charan
Siva Charan

Reputation: 18064

SQL Server 2008 - Advantages of Indexing the tables

What are the main advantages of indexing the tables on SQL Server 2008?

Please explain with examples (if possible)..

Thanks

Upvotes: 1

Views: 10271

Answers (4)

Sambasiva
Sambasiva

Reputation: 1044

Advantages:

1.Index Plays major role when a data is to be searched in Bulk Records
2.When data is searched in a table , it uses "sequential search technique, which
  is always a time consuming process ( Or ) it leads to wastage of time.
3.When ever finds the value, it stops the searching process

Upvotes: 0

ekaczynski
ekaczynski

Reputation: 116

Indexing provides a way to improve the performance of your data access queries. Suppose you have a table with different identifying columns. Putting an index on each column, or combinations of columns that are queried together will improve your response time.

Example: you have a User table with columns:

FirstName | LastName | ZipCode

Put an index on LastName if you are querying on last name, such as:

SELECT * FROM User where LastName = 'SMITH'

Index could be:

CREATE NONCLUSTERED INDEX [MyIndex] ON [User] 
(
    [LastName] ASC
) WITH (
    PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 
    SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, 
    ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON
) 
ON [PRIMARY]

Or, put an index on 2 columns if you are querying these columns together:

SELECT * FROM User where LastName = 'SMITH' and ZipCode = '14222'

Index could be:

CREATE NONCLUSTERED INDEX [MyIndex] ON [User] 
(
    [LastName] ASC,
    [ZipCode] ASC
) WITH (
    PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 
    SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, 
    ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON
) 
ON [PRIMARY]

All else equal, your queries will be faster if you create the index. Be careful though, too many indices may actually decrease performance.

Upvotes: 10

Martin Smith
Martin Smith

Reputation: 453287

A few possible advantages that spring to mind

  1. Can be used to quickly locate specific rows or ranges of rows.
  2. Even if the index cannot be used to match the seek predicate directly it can be used to limit the number of pages read (can be quicker to scan a narrower covering non clustered index than the whole table)
  3. Can be used to avoid a sort operation by providing the data pre-sorted

Upvotes: 1

SQLMenace
SQLMenace

Reputation: 135011

The same as indexing a book. If you need to find something quickly SQL Server can do a lookup instead of reading every row from the table.

Imagine if you had to lookup something in a book and didn't have an index? Same principle

Upvotes: 2

Related Questions