Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
 Posted - 2008-03-08 : 10:18:54
|
Hello all,I have a table:CREATE TABLE [dbo].[Shaw]( [Bank] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [Customer] [char](8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [LoanNumber] [char](4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,.....lots more columnsCONSTRAINT [PK_Shaw] PRIMARY KEY CLUSTERED ( [Bank] ASC, [Customer] ASC, [LoanNumber] ASC) ON [PRIMARY]) ON [PRIMARY] Bank,Customer,LoanNumber are used to join to a historical table which has the same 3 columns, but also a Tapeyear and Tapemonth column included in it's primary key. These 3 columns are part fo what is a full account number at 17 characters.There are only 2 different Banks, and only 3 or 4 different loan numbers. The customer number by itself is not unique as a customer number may be re-used (this just started happening about 4 months ago) with a different bank or loannumber.Customer level info is not accessed as much as aggregate queries..Would I be better served to recreate this table with and Identity column...and make that the PK, and move the 3 columns above to a composite nonclustered index? (and would that be applicable to the historical table as well? )There are about 1.8 million rows now, and each month it is updated such that about 700K rows with are deleted, and then updated information is inserted. These same accounts that are updated are then added to the historical snapshot.(which currently has 78 million records)Information is only updated once per month, so I am not concerned with the overhead during the update as much as I am improving overall performance and efficiency during queries and such..I am not looking to use the Identity to link the tables, just wondering if it is a more practical PK for both tables...Performance right now isn't bad at all, but just curious if there is an improvement to be made in this respect... I inherited an incredibly horrible design a while back, and have made many improvements,...but this is something that has lingered as a question of mine.
Poor planning on your part does not constitute an emergency on my part. |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2008-03-08 : 11:52:08
|
| >>I am not looking to use the Identity to link the tables, just wondering if it is a more practical PK for both tables...You've answered your own question right there. The only reason to use an identity over a composite PK is if you feel that there is some advantage when relating other tables, since you can do it with a single numeric key that in theory is smaller and faster (though, realistically, it is not much).You must define a unique index on those composite columns anyway, not just a regular index, for your data to have integrity ... so, since there is no value in adding an identity PK, don't bother doing that -- just use the composite columns as your PK.- Jeffhttp://weblogs.sqlteam.com/JeffS |
 |
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2008-03-08 : 12:07:43
|
Thanks Jeff,..I was getting lost in all the various articles I was reading and just wasn't sure if there was something to be gained from that. quote:
... must define a unique index on those composite columns anyway, not just a regular index, for your data to have integrity ... so, since there is no value in adding an identity PK, don't bother doing that -- just use the composite columns as your PK.
I realized I would have to create a unique composite index on the 3 columns anyway if I were to go the ID column route.. or are you saying I need to add something to the already PK'd columns?Data gets returned really fast off what I have now, compared to what I inherited. (hours previously is now minutes), but sometimes the analysts (my "clients" in this case) still complain. Either way, I am just trying to make it "better"...even if it is already decent.One thing I am struggling with is that I just don't have many candidate columns for other, or better indexes due to selectivity being minimal on many of the columns used in the analysts aggregations and groupings. You saw my other post on the potential indexed view (which turns out was not practical per your comments).
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
|
|
|
|
|
Clustered Unique composite PK, or add Identity