what are the advantages and disadvanteages of indexes in sql?

Answer 1

the advantages :
if your table has large record variant (large selectivity). it will speed up your 'select' query .

the disadvantages :
1. if your table has low selectivity eg. field with male/female record, or state for 1 million people , it will slow down your 'select' query.
2. there's some very little tiny 'performance impact' in 'delete','update','insert' query. (you can ignore this)
3. if your table has very little record, it will slow down (a little) your query . (you can ignore this)

Test your table performance in using/not using index with some query analyzer.

Answer 2

Indexes allow data to be organized in a way that allows optimum performance.

SQL Server does not need indexes to successfully retrieve results for your SELECT statements or to find and modify the specified rows in your data modification statements.

However, as your tables get larger, the value of using proper indexes becomes obvious. You can use indexes to quickly find data rows that satisfy conditions in your WHERE clauses, to find matching rows in your JOIN clauses, or to efficiently maintain uniqueness of your key columns during INSERT and UPDATE operations.

It is the job of the query optimizer to determine which indexes

Disadvantages:
Indexes can become fragmented. The fragmentation can be of two types. Internal fragmentation occurs when space is available within your index pages—that is, when the indexes are not making the most efficient use of space. External fragmentation occurs when the logical order of pages does not match the physical order, or when the extents belonging to a table are not contiguous.

Internal fragmentation means that the index is taking up more space than it needs to.

External fragmentation is truly bad only when SQL Server is doing an ordered scan of all or part of a table or index.

You should also be aware that Indexes slow down data insertions (if you're inserting data of huge size).

Hope the above wasn't too long.

Answer 3

The disadvantages are
1) need space
2) slow down DML, like inserts, as the indexes have to be updated.

Advantages are varied but most come down to
1) Can speed up data access

If you are clever with them you can avoid table accesses all together.

Answer 4

They take up more space, but they speed up searches.