August 1, 2011 at 12:00 pm
Hello
I have a sales transaction table. Very large - 100+m rows.
(not exact names)
strnum - int
register - smallint
date
tran# - int
tran type - smallint
sku - int
unit sales
sales
margin
tran id - char(23)
the natural PK is
strnum, register, date, tran# tran type, sku
On an insert i create the tran id -
strnum + register + date + tran# (concatonate, not add)
So i could key the table with
Tran Id, sku, tran typ
So my question is -
Is there efficiency gained between with the second scenario for a PK?
Will i have less physical space used (from less fields in the PK)?
Or does creating the extra field offset it?
Will SELECT's be faster with either scenario.
As in my previous question, i know i should be creating a test environment and answering these myself. I'm looking for more theory and best practice commentary. AND...I only play a DBA and don't play it very well sometimes!!
Thanks
Peter
August 1, 2011 at 12:46 pm
Where the pk is is utterly irrelevant. It's the clustered index that you need to be concerned about (the pk is clustered by default, but it's just a default)
Maybe take a read through this series
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
August 1, 2011 at 1:17 pm
Thank you Gail....I will read through these.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply