June 2, 2014 at 5:47 pm
Please correct me if I am wrong
Usually it is better to include the columns in the index that are in where clause, select list and join.
I am thinking that the columns in the selected list is better to keep as index columns and the columns that are in the where clause is better to keep in key columns.
Where do we use join column is it better to create as main key column or included column.
June 2, 2014 at 6:38 pm
ramana3327 (6/2/2014)
Please correct me if I am wrongUsually it is better to include the columns in the index that are in where clause, select list and join.
I am thinking that the columns in the selected list is better to keep as index columns and the columns that are in the where clause is better to keep in key columns.
Where do we use join column is it better to create as main key column or included column.
I'm not sure if it's a language barrier, or just a lingo barrier, but I'm not sure what you're asking here.
You want to use columns in the index that help your query seek the data the most efficiently. In some cases, that's the JOIN, in some cases if it's one of your primary filters it'll be what's in the WHERE clause. The JOIN is usually a range seek (because of the range from the other table), whereas the WHERE can be equijoin or range (Field = @a vs. Datefield between x and y).
The columns in the select list will have no affect on the seek properties of the index. These are INCLUDE items, typically, because you won't seek on them, you just report on them. However, INCLUDE columns have no control over the initial row locating method as far as the index is concerned. They are treated the same way as a scanned table would be, and used as a post-search predicate.
I'm not sure if I answered your questions. If not, please clarify for me exactly what you're trying to decipher.
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
June 3, 2014 at 2:04 am
Last time you asked that question (http://www.sqlservercentral.com/Forums/Topic1574340-2799-1.aspx), I referred you to three articles on indexes. Did you read them?
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
June 3, 2014 at 7:56 am
Yes Gail.
June 3, 2014 at 10:26 am
The key column vs include column should be answered by the third one.
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply