Supposing my table has 10 columns .... which columns i have to choose to create an index ? Are there any guide rules to do that ? What are the disadvantages of indexes ? If i update column which is part of INDEX then what will happen ? Does it lowers the performance of INDEXES ?
A:
To get answers to all of these questions it would be better to read a book about sql server such as Inside Microsoft SQL Server 2005: Query Tuning and Optimization
Giorgi
2010-09-13 10:47:15
+1
A:
An index is required to give your records a unique identity.
When you update records the index should remain constant and unaltered.
Michael Eakins
2010-09-13 10:49:52
Index is not required to give the records unique identity, but usually it is employed by RDBMS to efficiently implement the UNIQUE constraint and also the primary keys.
Unreason
2010-09-13 12:11:12
When you update a field which is indexed, or that is a part of an index (for multi field indexes), the index is updated as well.
Unreason
2010-09-13 12:12:37
The good thing about an autonumber index is the guarantee of uniqueness. You don't NEED it, but its not guaranteed unless you do.
Jimmie Clark
2010-09-13 12:39:13
I recommend adding an extra ID column as an index, with autonumber enabled. This is, in my opinion what the Answer is saying, and the best way to guarantee uniqueness.
Jimmie Clark
2010-09-13 12:40:06
A:
Check this link. It gives some basic thumb rules for indexing -
http://www.sql-server-performance.com/tips/optimizing_indexes_general_p1.aspx
Sachin Shanbhag
2010-09-13 10:50:25
+1
A:
From other posts:
Answer for Question 1:
1.1 You should create indexes on columns that are used frequently in WHERE clauses.
1.2 You should create indexes on columns that are used frequently to join tables.
1.3 You should create indexes on columns that are used frequently in ORDER BY clauses.
1.4 You should create indexes on columns that have few of the same values or unique values in the table.
1.5 You should not create indexes on small tables (tables that use only a few blocks) because a full table scan may be faster than an indexed query.
1.6 If more than one column in a concatenated index is used frequently in WHERE clauses, place the most selective column first in the CREATE INDEX statement.
1.7 There are many nulls in the column and you do not search on the non-null values.
1.8 Primary and unique keys automatically have indexes, but you might want to create an index on a foreign key;
Answer for Question 2:
2.1 previous rules
Answer for Question 3:
3.1 if you don't consider previous advices,then index becomes interruptive in a good performance of a database.
Answer for Question 4 and 5:
4.1,5.1 Do not index columns that are modified frequently. UPDATE statements that modify indexed columns and INSERT and DELETE statements that modify indexed tables take longer than if there were no index. Such SQL statements must modify data in indexes as well as data in tables. They also generate additional undo and redo.
kupa
2010-09-13 13:59:05