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 |
|
rajani
Constraint Violating Yak Guru
367 Posts |
 Posted - 2005-09-06 : 19:04:11
|
| Hi friends i have table with foreign key and when i query on that field (like below) do we normally expect it use index scan or index seek ? select assid,fk_staffid from assignment where fk_staffid='assole'abv query using index scan when i checked in query analyzer !! when im expecting it to see index seek ? or am i missing something!!Thanks for ur ideas Cheers |
|
|
ryanston
Microsoft SQL Server Product Team
89 Posts |
Posted - 2005-09-06 : 19:25:58
|
| It depends on the cardinality of the column in your predicate, and on the way your index is defined. If the optimizer determines that there aren't many distinct values for your search argument, or that the table is fairly small, then it could think that an index scan is more efficient than a seek. Have you looked at (and updated) the statistics for this index? Have a look through DBCC SHOW_STATISTICS and that should help you determine whether the cardinality of the index will lead the optimizer to the index seek solution.Thanks, and let us know how it goes.----------------------Ryan StonecipherDeveloper, Microsoft SQL Server Storage Engine, DBCC(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.) |
 |
|
|
rajani
Constraint Violating Yak Guru
367 Posts |
Posted - 2005-09-09 : 00:17:00
|
| Ryan thanks for the post.actually i defined foreign key constraint only not index thats why it was doing index scan.i was under wrong impression that creating a FK will also create a index automatically just like creating a primary key field.Cheers |
|
|
|
ryanston
Microsoft SQL Server Product Team
89 Posts |
Posted - 2005-09-09 : 00:25:19
|
| Ah, that's what I get for jumping to conclusions :) Thanks for the follow-up.----------------------Ryan StonecipherDeveloper, Microsoft SQL Server Storage Engine, DBCC(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.) |
|
|
|
|
|
|
|
|
not using index