July 15, 2011 at 1:30 pm
Will a composite PK be used in future queries (JOIN/WHERE) if only 1 of the 2 columns is used. I am asking this at the database design phase to determine if I should create a composite key or just a single column PK with only the 1 column that will be used 100% of the time in queries.
The composite key would be used maybe 70% of the time in queries.
July 15, 2011 at 2:05 pm
primary keys are the physical structuring of the table. any query against the table will always use the primary key, unless there is a covering index which can supply the fields.
if you have a composite key across Column A and Column B, and your query WHERE clause specifies only a search against Column B, then the optimiser will do a CLUSTERED INDEX SCAN, which is basically the same thing as a TABLE SCAN. If the WHERE clause uses Column A, or both Column A and Column B, then it will do a CLUSTERED INDEX SEEK or a CLUSTERED INDEX SCAN, depending on which the optimiser determines will be the faster operation.
my personal opinion is that it would be better for you to create a unique key instead of a composite one. I usually use an INT IDENTITY (1, 1) as my PRIMARY KEY, and then use indexes to handle any searches.
July 15, 2011 at 2:06 pm
though I should note, if your table is *only* going to contain two columns, such as perhaps if it is a link table between two tables that have a many-to-many association, then I have at times used a PRIMARY KEY across the two columns, and an INDEX across the two columns in reverse.
July 15, 2011 at 2:11 pm
Thank you for the response. There are other columns in the table so I will go with your suggestion of creating the PK on an identity column and create a secondary unique constraint on Column A and Column B
July 15, 2011 at 3:38 pm
Depends. If they use the leading column, yes. If they use the second column in the key (primary or unique, makes no difference here) then no.
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
July 15, 2011 at 4:22 pm
So if I use the column that is used 100% of the time is the first column in the composite PRIMARY KEY and the second column will only be used sometimes a query with a JOIN/WHERE on just the first column will still do an index seek instead of a index scan? This was the behavior I thought would happen.
July 15, 2011 at 4:34 pm
whattheETL (7/15/2011)
So if I use the column that is used 100% of the time is the first column in the composite PRIMARY KEY and the second column will only be used sometimes a query with a JOIN/WHERE on just the first column will still do an index seek instead of a index scan? This was the behavior I thought would happen.
In theory. It'll depend on a few factors. Primarily it's going to depend on the statistics staying updated, the density of the column's data disparity, and the row size. Look into 'Tipping Point' (I believe Kimberly Tripp has an excellent blog on this) for more information between seek and scan points. The optimizer at some point will walk the pages simply because it's faster.
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
July 15, 2011 at 5:35 pm
thanks for the article reference. i will check it out. the only inserts that will be occurring will be thru the etl so we can manage the statistics.
July 16, 2011 at 3:47 am
whattheETL (7/15/2011)
So if I use the column that is used 100% of the time is the first column in the composite PRIMARY KEY and the second column will only be used sometimes a query with a JOIN/WHERE on just the first column will still do an index seek instead of a index scan? This was the behavior I thought would happen.
If you're accepting the default for a primary key (clustered index) then yes, the first column should still seek (unless there are more suitable indexes). If the second column is used alone, it'll scan, if used in combination with the first again it should be a seek.
http://sqlinthewild.co.za/index.php/2009/01/19/index-columns-selectivity-and-equality-predicates/
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
July 16, 2011 at 7:58 am
kramaswamy (7/15/2011)
primary keys are the physical structuring of the table. any query against the table will always use the primary key, unless there is a covering index which can supply the fields.
Let me add that...
1- If PK is not clusterd then "physical structure" would follow whaterver clustered index is on the table.
2- If table is accessed via Full Table Scan then PK is out of the equation.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply