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 |
|
Jhouston132
Starting Member
11 Posts |
 Posted - 2009-08-04 : 14:52:28
|
| I'm just learning about indexes. Is this a good place to create a non-clustered index?I have two tables. Table X has a primary key that is a clustered index. Table Y has another primary key with a clustered index, and another foreign key field from table X (that is not an index). To inner join them, I do:select * from xwhere x inner join y on x.field = y.fieldSo I'm joining a clustered index key against a foreign key that is not an index. Should I make this foreign key into a non-clustered index? Is there any benefit? Does it matter on the selectivity?Thanks! |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-08-04 : 16:12:08
|
WHERE x INNER JOIN ... isn't a known syntax.An index is used to retrieve / find a specific value.An index is great to find a specific value using WHERE clause.And yes - indexes on joined columns will optimize performance for this.
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
Jhouston132
Starting Member
11 Posts |
Posted - 2009-08-04 : 18:48:11
|
quote:
Originally posted by webfred
WHERE x INNER JOIN ... isn't a known syntax.An index is used to retrieve / find a specific value.An index is great to find a specific value using WHERE clause.And yes - indexes on joined columns will optimize performance for this.
No, you're never too old to Yak'n'Roll if you're too young to die.
Thanks for the reply! Sorry, the "where" before the "x INNER JOIN" was a typo on my part. So, you'll only receive a performance gain if there's a "WHERE" clause"? If I had a table with data and it had an index (the primary key), and I did a SELECT all on it without a WHERE clause - would that be faster than doing the same thing on a table that didn't have an index for the primary key? The reason I bring this up is because I tested the speed on a table with 100 records (maybe it wasn't big enough). These were the scenerios:Scenerio 1: I inner joined two tables. One table had a clustered index primary key being joined to a non-clustered index foreign key on another table. Scenerio 2: I inner joined two tables. One table had a clustered index primary key being joined to a column (it wasn't an index or a foreign key) a on another table. I noticed the same result time for both queries. Am I missing something? Should I see more if the dataset was bigger? If I joined more tables?Thanks again! |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-08-05 : 03:05:41
|
You cannot really see something significant when you have a small table with 100 rows.SQL Server reads this table in one block (8 pages = 1 block / extend)=64 kb (block is the german word for this).So there is no need to use an index.
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
|
|
|
When to Create an Index (just learning)