Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Bien
Starting Member
12 Posts |
 Posted - 2003-09-17 : 02:51:47
|
Hi! I would just like to ask:1. Assuming I have 10 indexes on a table, does SQL server know which index to use so that the fetching of records will be fast?2. Can I specify (in my SQL statement) which index to use? I am using VB6 and SQL2000. Thanks!  *** :D *** |
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2003-09-17 : 03:03:53
|
| 1. It can make a fair guess yes.2. Look up Index Hints in BOL.3. Try looking at the estimated query plan in query analyzer. It will show you which index is likely to be used.-------Moo. :) |
 |
|
|
Bien
Starting Member
12 Posts |
Posted - 2003-09-17 : 03:50:32
|
quote:
Originally posted by mr_mist
2. Look up Index Hints in BOL.
mr_mist, can you give me an example? I got kind of confused when I looked up Index hints in books online..  quote:
Originally posted by mr_mist
3. Try looking at the estimated query plan in query analyzer. It will show you which index is likely to be used.
Is estimated query plan the same as estimated execution plan? I'm sorry, but I'm not yet familiar with SQL server since I just recently used it.  Please bear with me. Thanks! ****  ****  **** |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2003-09-17 : 05:19:07
|
| see the link below for a code sample....the "hint" says to use index #3....whatever that is on the table....remember the code is just a sample....so the index specified may be of no use....(note: nothing in the post, says that index 3 existed)the point of a hint...is to allow the developer control which index is used, but that choice MAY be worse than the OPTIMISER's choice.estimated query plan is equivalent to estimated execution plan....it's purpose is to highlight how the query results will be obtained....and in particular the performance of getting those results.http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=28868 |
|
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2003-09-17 : 05:28:52
|
LOL!! those icons are too funny...ROTF!!Owais
Make it idiot proof and someone will make a better idiot |
|
|
|
Bien
Starting Member
12 Posts |
Posted - 2003-09-17 : 05:46:46
|
| Thanks for replying.. I used the estimated execution plan but it did not show which index was used.. mohdowais, ------------**** **** **** |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2003-09-17 : 11:38:25
|
| Im the pictorial version of the plan I see, using Query Analyser (after pressing Ctrl-K before running the query), hovering the cursor over a sample "index-seek" icon, shows the index that is used is actually named in the "arguement" section at the bottom of the pop-up display.usually in the formatOBJECT dbname.dbowner.tablename.indexname AS (a), SEEK a.columnname = xxx |
|
|
|
Bien
Starting Member
12 Posts |
Posted - 2003-09-18 : 20:50:16
|
| hi andrew, i finally saw what index my query was using. thanks!**** **** **** |
|
|
|
|
|
|
|
|
Index question