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 |
|
Vinnie881
Master Smack Fu Yak Hacker
1231 Posts |
 Posted - 2007-02-10 : 00:59:29
|
| If I have two tablesTable1(tID, DESC)Table2(Tbl2ID,tID,Desc)The primary keys aretable1 - tIDTable2 - tbl2ID in the scenerio that I never call a record in table2 w/o joining on the tID field, is it better to make my clustered index tID on both tables?It just seems like since my primary key is never going to be used to referance the record, wouldn't performance increase if I use the Clustered index on that field, rather then just setting up a non-clustered?sample query Select a.*from table1 a inner join table2bon a.tID = b.tID |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2007-02-10 : 04:20:17
|
| it'll still be an index scan..question is... do you allow new records on table2 not existing in table1? if no keep the foreign key--------------------keeping it simple... |
 |
|
|
Vinnie881
Master Smack Fu Yak Hacker
1231 Posts |
Posted - 2007-02-10 : 05:07:33
|
| why would it be a index scan? If it's the clustered index wont sql be able to use that? Please clearify. THANKS |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2007-02-10 : 16:06:55
|
| There is lots more to this...Assuming normal usage. (your sample select clause is abnormal, since it only selects a.* (table1), which is a quite pointless use of the join in this case):With a clustered index on(tID for table2) it will probably be an index scan.With a normal index on(tID for table2) it will probably be an index seek followed by a bookmark lookup.rockmoose |
|
|
|
Vinnie881
Master Smack Fu Yak Hacker
1231 Posts |
Posted - 2007-02-10 : 18:55:58
|
| why ist that pointless if I only wanted to show records that were in both the tables? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-02-11 : 05:27:07
|
| "if I only wanted to show records that were in both the tables?"Can two records existing in TABLE2 for a single row in TABLE1?If so (or possible anyway) try using an EXISTS instead?Kristen |
|
|
|
|
|
|
Index question- awaiting response