David Portas (10/14/2010)
I don't understand your claim that an index alone provides better performance than a uniqueness constraint. Can you back that up with an example? I'm pretty sure you'll find they are always exactly the same, in which case the constraint still has all the advantages I mentioned before.
And:
Tom.Thomson
Good heavens, has someone introduced a major new performance reducing feature into the MS SQL Server optimiser so that it no longer uses the index that supports a UNIQUE constraint in the way it has used it for at least the last 10 years?
I'm going to duck out the discussion in general, but since I got called on this directly... You're absolutely correct. I've been carrying a really bad myth around with me for years and never got called on it because the conversation never came up before on that specific aspect.
Thank you both.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA