index question

Question

index question

sqlguy-736318

Hall of Fame

Points: 3859
More actions
May 13, 2013 at 11:26 am

#272671

Hello - I know that indexes are generally used on columns that are referenced in the where clause. I assume the implications are even greater for join columns which should generally be PK's (clustered indexes). Is this correct?
Also, can you confirm if an index would improve performance in the following scenario?:
* nullable date column (createdon)
* where condition: where createdon is not null

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

sqlguy-736318 (5/13/2013)
I assume the implications are even greater for join columns which should generally be PK's (clustered indexes). Is this correct?

No. They'll be primary keys on the one side, foreign keys on the other, what kind of index enforces the primary key and what kind of index is on the foreign key is a whole nother matter.

Also, can you confirm if an index would improve performance in the following scenario?:
* nullable date column (createdon)
* where condition: where createdon is not null

Maybe.

http://www.sqlservercentral.com/articles/Indexing/68439/

http://www.sqlservercentral.com/articles/Indexing/68563/

http://www.sqlservercentral.com/articles/Indexing/68636/

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sqlguy-736318 Hall of Fame Points: 3859 More actions · Answer 2

Thanks Gail!

When you say "Maybe", in what scenarios "Yes" and in what scenarios "No"?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

sqlguy-736318 (5/13/2013)
Thanks Gail!
When you say "Maybe", in what scenarios "Yes" and in what scenarios "No"?

Have you read the articles she provided the links to below her answer? Those will help answer your questions or allow you to ask more detailed questions.