September 22, 2008 at 4:43 am
1. How to choose the best index?
2. How to Rebuild the index?
3. What will be the affect of adding a clustered & non clustered index on each and every column of a table consisting large no of rows?
September 22, 2008 at 5:41 am
1. generally, you should create an index on columns used in your where criteria. Use "database engine tuning advisor" to see what MSSQL suggests
2. "alter index rebuild or "alter index reorganize" see BOL
3. your can only add 1 clustered index to a table (and multiple non-clustered indexes). Generally, if you add too many indexes, you'll get a performance degradation because each update on your table will also update your index(-es). Also, you'll see that some indexes are rarely/never used.
Make sure statistics are also up-to-date, because the optimizer will use this data in its decision which index to use
Wilfred
The best things in life are the simple things
September 22, 2008 at 6:29 am
susantapattu (9/22/2008)
3. What will be the affect of adding a clustered & non clustered index on each and every column of a table consisting large no of rows?
Massive waste of space, degradation of insert/update/delete performance. There's a good chance if you create a nonclustered index on every column that most will not be used. Wider nonclustered indexes are usually more useful than narrow ones. You should look at the queries that run against the tables to determine the most useful indexes
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 22, 2008 at 7:40 am
Actually, the question sounds like it belongs in an interview or an exam.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply