November 7, 2014 at 3:53 pm
I have come across tables where there is a clustered index with 2 columns, also a non clustered index is created with same two columns. Is there a reason ever nc index seek or scan would be better than clustered index seek or scan
November 7, 2014 at 4:03 pm
Depends on how much else is in the table. The Clustered Index carries the entire table at the leaf level, the Non-Clustered only carries included columns and information needed for key lookups on top of the B-Tree.
So, if you've got a table with, say, CustomerID and CustomerName, and then a few dozen VARCHAR(4000) fields trailing it for address details and the like, you don't necessarily want to go through that entire thing everytime you want to simply pull the customer name for the top of a webpage (IE: LoggedInAs: Evil Kraig F).
It's somewhat repetitive, but I've done it in special cases for extremely attribute heavy tables. Linking Tables (between a many to many join) in particular can carry a lot of excess baggage when you're just trying to traverse between tables for a different set of information. It's a balance of the cost of maintaining the index twice vs. access speed and memory grants. Index Scans in particular make a huge difference.
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply