composite primary keys versus composite unique indexes

Hello,

I have a table which has a composite primary key consisting of four columns, one of them being a datetime called Day.

The nice thing afaik with this composite key is that it prevents duplicate entries in the table for any given day. But the problem is probably two-fold

1. multiple columns need to be used for joins and I think this might degrade performance?
2. in client applications such as asp.net these primary keys must be sent in the query string and the query string becomes long and a little bit unmanagable.

A possible solutions I'm thinking of is dropping the existing primary key and creating a new identity column and a composite unique index on the columns from the existing composite key.

I would like to have some tips, recommendations and alternatives for what I should do in this case.

One item that is not always immediately apparent for this type of scenario has to do with clustering. Is your primary key also presently your clustered index? If the answer is yes and you change to a primary key based on an identity column and leave the CLUSTERED / NOT CLUSTERED aspect of this primary key to be defaulted then any query that still accesses data based on your current primary key will experience a new performance problem -- these accesses will now also require a bookmark lookup.

The good news is that this is might not grieve you for most OLTP activity, but it is likely to impact some reports and perhaps some record lookups; however, this is rarely enough of a drawback to preclude the change your are considering. Just file keep this in mind and if some reports or lookups become slower there is a chance that this modification might be the reason.

The bigger problem usually is modifying tables that reference your table with foreign key constraints. You have two alternatives: First, you can replicate the identity column into the foreign table and now use this value as the foreign key reference; however, if you do this you should also drop the columns that references the old foreign key columns of the table that you are changing. The second alternative is to continue to reference your modified table by what has now become the UNIQUE constraint rather than the primary key. This works and I have used this a number of times but it can be unsavory. Also, if you choose this alternative you need to know the that REFERENCES clause of the foreign key constraint will now need to explicitly list the columns of the UNIQUE constraint or your FK constraint will not compile.

Also, you might have some reports, functions, or stored procedures that filter based on one of these columns in one of your foreign tables. Changing the foreign key reference might be an impact here also.

The moral of this second problem is that you want to know all tables that have foreign key constraints to the table you are considering modifying. There might be a lot of hidden additional work if your table is referenced from a couple of dozen other tables.

COMPOSITE PRIMARY KEYS

Hi,
I have 150 tables in a database.
Composite primary key is used in some of those tables.
Can I list out the table names having composite primary key.
Thanks,
Soura
SouRa
Run the following in Query Analyzer-
select name from sysindexes where keycnt <> 0
Hope that helps,
Donna
"SouRa" wrote:

> Hi,
> I have 150 tables in a database.
> Composite primary key is used in some of those tables.
> Can I list out the table names having composite primary key.
> Thanks,
> Soura

composite primary key

i have a master table with around 15 columns and i am trying to find
the appropriate primary keys and indexes for the table.

To make the records unique, i need to include two datetime columns (
start and end dates ) and two integer columns ( attributes of the
record ) to make up a composite primary key. Both of these four
columns are found in the WHERE clause of my queries.

Is it acceptable in the view of performance and how should i create
the indexes?Hi

I think this is probably a candidate for a surrogate key.

John
"onder" <okeen1@.hotmail.com> wrote in message
news:9c3d3756.0309022355.10656428@.posting.google.c om...
> i have a master table with around 15 columns and i am trying to find
> the appropriate primary keys and indexes for the table.
> To make the records unique, i need to include two datetime columns (
> start and end dates ) and two integer columns ( attributes of the
> record ) to make up a composite primary key. Both of these four
> columns are found in the WHERE clause of my queries.
> Is it acceptable in the view of performance and how should i create
> the indexes?|||Hi John,

sorry for this but can you tell a little more on that?|||Hi

There is usually quite a debate in the newsgroups when surrogate keys are
mentioned. Basically it is when you add a column that you populate with
unique values, so you can reference a row by that instead of the 4 columns
you mentioned. This is a way to reduce the width of any table that refers to
your table with a foreign key (you would need all 4 columns replicated in
that table if you did not use a surrogate key). If you searched google for
"Surrogate SQLServer" it will turn up a few debates.

Quite often a surrogate key is given the IDENTITY property, so SQL Server
will populate the values for you,
http://msdn.microsoft.com/library/d...asp?frame=true
but it is possible to maintain the values your self.

HTH

John

"onder" <okeen1@.hotmail.com> wrote in message
news:9c3d3756.0309030711.5f4f582c@.posting.google.c om...
> Hi John,
> sorry for this but can you tell a little more on that?|||Ok about the surrogate keys. But i have an issue to ask:

In my 'select' queries, i always have the three columns which defines
a unique combination. So, i think the optimization of the table design
should bear this in mind.
If i add a surrogate key to the table to be a primary key, then i will
need to get that column to my select queries and then do the updates
based on that column.

What would you recommend for the index of the table?|||Hi

You would need to have the surrogate key in the JOIN or WHERE clause. This
could be totally transparent to the user. As a Primary Key there will
already be an index on the surrogate key column,

It is possible a covering index on the surrogate and candidate key columns
will be useful, but like any index you should investigate if it is being
used and if the speed improvements are worth while.

John

"onder" <okeen1@.hotmail.com> wrote in message
news:9c3d3756.0309080039.65cccc67@.posting.google.c om...
> Ok about the surrogate keys. But i have an issue to ask:
> In my 'select' queries, i always have the three columns which defines
> a unique combination. So, i think the optimization of the table design
> should bear this in mind.
> If i add a surrogate key to the table to be a primary key, then i will
> need to get that column to my select queries and then do the updates
> based on that column.
> What would you recommend for the index of the table?

Composite Primary Key

WHile designing a Database should one go for composite Primary Keys.
Or what are the Pros and Corns of Composite Primary Keys
ThanxNo, you should only use IDENTITY columns...|||Just kidding...

http://www.datamodel.org/

Define what it takes to uniquely identify a row in a table, no matter how many columns...

And then establish relationships between these objects...

Then you'll have a logical model...

How you physically implement is another story...but start there...|||Come on...I thought that was funny...

..."but what if I need to change the name of a City? Like when New Amsterdam became New York City...IDENTITY would be good there, no?"|||identity? heh, yes, i thought that was funny

and the delivery was excellent

create table constants
( id smallint not null primary key
, name varchar(5) not null
, constant double precision not null
)
insert into constants (name,constant)
values ('pi', 3.141592653589793)

always reference a row by its primary key

which for pi is 1 in the above case, since that was the first constant entered into the table

so whatever id number it gets when you insert that row in your table, just remember the id number and always use it in your sql

the benefit of this is that your sql will not have to change in case pi ever changes its name or value|||http://www.straightdope.com/classics/a3_341.html

A classic I had to look up once more....

Composite Keys on Dimensions

I have a question about Composite Keys on Dimensions. I run the Dimension Health Checker that comes with BIDS Helper and it tells me I have many to many relationships happening, so I change key columns to multiple columns to get rid of the many to many, which is good.

Now, when I browse my cube, I pull in a Dimension property , say the lowest level of a heirarchy, and I see duplicates where before it was rolled up.

Is there an easy way to get rid of the many to many with composite keys yet still have the data rolled up to my name column?
i am guessing CustomRollupColumn is the answer?
|||

Instead of using the key as the keycolum you could use the name of the attribute e.g.

key_size name_size

1 big

2 big

3 small

4 medium

if your keycolumn is key_size you will get four values if the keycolumn is name_size you will only get three where keys 1 and 2 are pointed to by the name_size big.

Hope that helps

Matt

|||that is how it was set up, but with the attribute relationships and heirarchies, the dimension health checker comes back with many to many relationships, so i have to make the key composite to get uniques across..

the dimensions are slowly changing collapsed snoflake dimensions..so there are possiblities for dupes
|||

The composite keys only really need to exist in the DSV to join the tables together, do they not? Each individual attribute may not need composite keys. Think I must have missed something here, any chance of a more detailed example, perhaps two of the tables.

Cheers

Matt

|||i have one dimension.. called Source

there is a heirarchy Dept->Account->Campaign

since the dimension is slowly changing there are duplicates when campaigns change

for my Campaign i have the keycolumn as Campaign and Account, name column as Campaign as the BIDS dimension health checker said there were many to many relationships occuring. When i changed the keycolumn to the two columns, then reran the health checker, the warning/error went away...

before i had the composite key column, i could drag campaign and say revenue onto a pivot table and it would rollup to campaigns, now when i do that, it looks like there are duplicates because really it is rolling up to the keycolumn (Campaign and Account).

What i want to do is still roll up to the campaign name even though I have the key column as campaign+account
|||

Hi,

Ok i think i understand, it should look ok in the hierarchy? yes. But if you browse the campaign attribute, it looks like there are duplicates, this is as expected. Two solutions, remove the composite key and do not make an attribute relationship between the attributes - thus giving you a little yellow triangle warning in the hierarchy.

OR, rename campaign to campaign2, create another attribute Campaign and don't use a composite key on that one, just the key campaign. If you want to browse the campaign list, just use campaign attribute but if you want to use the hierarchy use campaign2. In effect creating four attributes:

-Campaign

-Campaign2

-Account

-Dept

A hierarchy

-Dept

--Account

Campaign2 (change its name to Campaign to reduce confusing the users)

And perhaps in the perspectives hide the individual attribute campaign2 so no one ever see it. Bit of a work around, but removes the duplicates.

Not to sure if there is another way of doing it. Maybe someone else has a better idea.

Hope that helps

Matt

|||yes , for now i just removed the composite key and left the little warning in the heirarchies, but i would like to fix that as i want to design my aggregations and get best performance

your workaround might just work as well, something I can probably try...I dont know if there is another solution or something else to try, but you would think there would be a way to roll it up on a column instead of the key, i dunno..

thanks for all your help on this

Complex File Import

I am trying to import a flat file of semi-colon delimited values into a
table. The table has many foreign keys that reference other tables. The
flat file has the data all explicitly stated instead of the ID numbers. I
want to make it so that if a new value is encountered, a new entry in that
related table is created. An example: The main table, CITTAS_Primary, has a
foreign key, DrayageID, that links to the table CITTAS_Drayage_Company, whic
h
has a foreign key, DrayageCompanyID, that links to the table CITTAS_Drayage.
I can parse the entries that exist in the table already, but I am unsure of
how to insert new values using an ActiveX Script Task. I am currently using
Execute SQL tasks to retrieve the recordsets from CITTAS_Drayage and
CITTAS_Drayage_Company and then place the resulting rowsets into global
variables. Here is the code that I have so far:
Function Main()
Dim Company, CompanyID
Company = DTSSource("Col003")
Dim Charge, ID
Charge = DTSSource("Col009")
ID = -1
Dim comps, chs
Set comps = CreateObject("ADODB.Recordset")
Set comps = DTSGlobalVariables("DrayageCompany").Value
Set chs = CreateObject("ADODB.Recordset")
Set chs = DTSGlobalVariables("DrayageCharge").Value
Dim i
For i = 1 To comps.RecordCount
If comps.Fields("DrayageCompany") = Company Then
CompanyID = comps.Fields("CompanyID")
Break
End If
comps.MoveNext
Next i
For i = 1 To chs.RecordCount
If chs.Fields("DrayageCost") = Charge And chs.Fields("DrayageCompanyID") =
CompanyID Then
ID = chs.Fields("DrayageID")
Break
End If
chs.MoveNext
Next i
If ID = -1 Then
'Insert code to insert new record
'Get ID for new cost/company pair
End If
DTSDestination("DrayageCompanyID") = ID
Main = DTSTransformStat_OK
End Function
I want it to be able to insert new companies into CITTAS_Drayage and new
cost/company pairs into CITTAS_Drayage_Company.
Also, for some reason, it is complaining about using the RecordCount
property of the recordset. I would think that it would work since it is
straight from SQL Server Books Online DTS collection. If anyone knows what'
s
going on with this, please enlighten me, because it amkes no sense to me.
Thanks in advance, everyone.
Chris Lieb
UPS CACH, Hodgekins, IL
Tech Support Group - Systems/AppsHi Chris
I am not sure of the example you talk about but
DTSGlobalVariables("DrayageCompany").Value may not be a recordset.
John
"Chris Lieb" wrote:

> I am trying to import a flat file of semi-colon delimited values into a
> table. The table has many foreign keys that reference other tables. The
> flat file has the data all explicitly stated instead of the ID numbers. I
> want to make it so that if a new value is encountered, a new entry in that
> related table is created. An example: The main table, CITTAS_Primary, has
a
> foreign key, DrayageID, that links to the table CITTAS_Drayage_Company, wh
ich
> has a foreign key, DrayageCompanyID, that links to the table CITTAS_Drayag
e.
> I can parse the entries that exist in the table already, but I am unsure o
f
> how to insert new values using an ActiveX Script Task. I am currently usi
ng
> Execute SQL tasks to retrieve the recordsets from CITTAS_Drayage and
> CITTAS_Drayage_Company and then place the resulting rowsets into global
> variables. Here is the code that I have so far:
>
> Function Main()
> Dim Company, CompanyID
> Company = DTSSource("Col003")
> Dim Charge, ID
> Charge = DTSSource("Col009")
> ID = -1
> Dim comps, chs
> Set comps = CreateObject("ADODB.Recordset")
> Set comps = DTSGlobalVariables("DrayageCompany").Value
> Set chs = CreateObject("ADODB.Recordset")
> Set chs = DTSGlobalVariables("DrayageCharge").Value
> Dim i
> For i = 1 To comps.RecordCount
> If comps.Fields("DrayageCompany") = Company Then
> CompanyID = comps.Fields("CompanyID")
> Break
> End If
> comps.MoveNext
> Next i
> For i = 1 To chs.RecordCount
> If chs.Fields("DrayageCost") = Charge And chs.Fields("DrayageCompanyID")
=
> CompanyID Then
> ID = chs.Fields("DrayageID")
> Break
> End If
> chs.MoveNext
> Next i
> If ID = -1 Then
> 'Insert code to insert new record
> 'Get ID for new cost/company pair
> End If
> DTSDestination("DrayageCompanyID") = ID
> Main = DTSTransformStat_OK
> End Function
>
> I want it to be able to insert new companies into CITTAS_Drayage and new
> cost/company pairs into CITTAS_Drayage_Company.
> Also, for some reason, it is complaining about using the RecordCount
> property of the recordset. I would think that it would work since it is
> straight from SQL Server Books Online DTS collection. If anyone knows wha
t's
> going on with this, please enlighten me, because it amkes no sense to me.
> Thanks in advance, everyone.
> --
> Chris Lieb
> UPS CACH, Hodgekins, IL
> Tech Support Group - Systems/Apps|||BTW... I was going to add..
Rather than mess around with the activeX script it may be quicker (and IMO
easier) to load into a staging table, sort out the missing FKs and then load
into the live table.
John
"Chris Lieb" wrote:

> I am trying to import a flat file of semi-colon delimited values into a
> table. The table has many foreign keys that reference other tables. The
> flat file has the data all explicitly stated instead of the ID numbers. I
> want to make it so that if a new value is encountered, a new entry in that
> related table is created. An example: The main table, CITTAS_Primary, has
a
> foreign key, DrayageID, that links to the table CITTAS_Drayage_Company, wh
ich
> has a foreign key, DrayageCompanyID, that links to the table CITTAS_Drayag
e.
> I can parse the entries that exist in the table already, but I am unsure o
f
> how to insert new values using an ActiveX Script Task. I am currently usi
ng
> Execute SQL tasks to retrieve the recordsets from CITTAS_Drayage and
> CITTAS_Drayage_Company and then place the resulting rowsets into global
> variables. Here is the code that I have so far:
>
> Function Main()
> Dim Company, CompanyID
> Company = DTSSource("Col003")
> Dim Charge, ID
> Charge = DTSSource("Col009")
> ID = -1
> Dim comps, chs
> Set comps = CreateObject("ADODB.Recordset")
> Set comps = DTSGlobalVariables("DrayageCompany").Value
> Set chs = CreateObject("ADODB.Recordset")
> Set chs = DTSGlobalVariables("DrayageCharge").Value
> Dim i
> For i = 1 To comps.RecordCount
> If comps.Fields("DrayageCompany") = Company Then
> CompanyID = comps.Fields("CompanyID")
> Break
> End If
> comps.MoveNext
> Next i
> For i = 1 To chs.RecordCount
> If chs.Fields("DrayageCost") = Charge And chs.Fields("DrayageCompanyID")
=
> CompanyID Then
> ID = chs.Fields("DrayageID")
> Break
> End If
> chs.MoveNext
> Next i
> If ID = -1 Then
> 'Insert code to insert new record
> 'Get ID for new cost/company pair
> End If
> DTSDestination("DrayageCompanyID") = ID
> Main = DTSTransformStat_OK
> End Function
>
> I want it to be able to insert new companies into CITTAS_Drayage and new
> cost/company pairs into CITTAS_Drayage_Company.
> Also, for some reason, it is complaining about using the RecordCount
> property of the recordset. I would think that it would work since it is
> straight from SQL Server Books Online DTS collection. If anyone knows wha
t's
> going on with this, please enlighten me, because it amkes no sense to me.
> Thanks in advance, everyone.
> --
> Chris Lieb
> UPS CACH, Hodgekins, IL
> Tech Support Group - Systems/Apps

Complex copy routine, mulitple tables and changing GUIDs

hello,

I have several tables that have guids as their primary keys and the tables are related as follows:

Table1 - primary key =ServiceNo (Guid), Filter Key =CampaignNo

Table2 - primary key = CostBasisNo (Guid), Foreign Key =ServiceNo (from Table1)

Table3 - primary key = UserId, Foreign Key =ServiceNo (from table1)

Table4 - primary key = SourceServiceNo (Foreign Key from Table1), MemberServiceNo(Foreign Key from Table1)

what I need to do is copy all records from Table1 where CampaignNo = @.CampaignNo and insert them into table1, this I can do easily but I will generate a new ServiceNo for each one and associated a new CampaigNo which is fine.

The problem comes in that I need to also copy the contents of Table2 = Table3 for all ServiceNos that have been copied from Table1 but insert the new Guid that will have been created when copying the rows in Table1

This is further compounded when I need to do the same to Table4 but this time I need to insert the newid's forSourceServiceNo and the relatedMemberServiceNo which all would have changed.

I haven't the first clue where to start with this task, do I need to use temporary tables, cursors? any help gratefully received, even if it's a pointer to the most efficient approach.

regards

DECLARE @.newCampaignNo uniqueidentifier

SELECT @.newCampaignNo=newid()

INSERT INTO Table1 (CampaignNo) -- and other columns as well, I guess
SELECT @.newCampaignNo FROM Table1 WHERE ServiceNo=@.oldCampaignNo

INSERT INTO Table2 (ServiceNo) -- and other columns as well, I guess
SELECT ServiceNo FROM Table1 WHERE CampaignNo=@.newCampaignNo

INSERT INTO Table3 (ServiceNo) -- and other columns as well, I guess
SELECT ServiceNo FROM Table1 WHERE CampaignNo=@.newCampaignNo

I suppose table4 will be quite easy as well, but you need to describe where the ServiceNo:s come from.

|||

Hi gunteman,

thank you for replying, I'm not sure that will do what I want.

In my db the CampaignNo will never equal the ServiceNo.

Maybe if I try and explain the problem another way and only use two related tables

Lets say I have:

Table1 where the primary key =ServiceNo, and other attributes including anon primary key = CampaignNo

My first task is to copy all the rows from Table1 where an input parameter called @.CampaignNo = CampaignNo,

So my statement would be something like:

INSERT INTO Table1 (ServiceNo, attribute1, attribute2,CampaignNo)
(SELECTNEWID(), attribute1, attribute2, @.CampaignNo from Table1 WHERE
CampaignNo = @.CampaignNo)

NEWID() of course will create a new uniqueidentifier.

now this works fine, I've tried this out using a temporary table and all is good.

my problem comes when I need to copy the contents of the related table, say Table2,

Lets say Table2 hasCostBasisNo (primary key), attribute1, attribute2,ServiceNo (foreign key from Table1)

now I need to copy rows from Table2 but where the ServiceNo = the old ServiceNo from Table1 and I guess this is where the list of service numbers again = @.CampaignNo.

I guess I could say something like

INSERT INTO Table2 (CostBasisNo, attribute1, attribute2, ServiceNo)
(SELECTNEWID(), attribute1, attribute2, ServiceNo from Table2 WHERE
?)

and this is where I hit the problem, how do I write the INSERT statement for Table2 to copy it's own records but only where the ServiceNo = the old ServiceNo from Table1 and then insert the associated new ServiceNo.

hope that's made the issue a little clearer, the other tables don't really matter cos if I can solve this the others will follow.

regards

|||DECLARE @.newCampaignNo uniqueidentifier

SELECT @.newCampaignNo=newid()

DECLARE @.conversion TABLE
(
oldServiceNo uniqueidentifier,
newServiceNo uniqueidentifier
)

INSERT INTO @.conversion (oldServiceNo,newServiceNo)
SELECT ServiceNo,newid() FROM Table1 WHERE CampaignNo=@.oldCampaignNo

INSERT INTO Table1 (ServiceNo,attribute1, attribute2, CampaignNo)
SELECT newServiceNo, attribute1, attribute2, @.newCampaignNo FROM @.conversion c,Table1 t WHERE c.oldServiceNo=t.ServiceNo

INSERT INTO Table2 (CostBasisNo, attribute1, attribute2,ServiceNo)
SELECT newid(), attribute1, attribute2, newServiceNo FROM Table1 @.conversion c,Table2 t WHERE c.oldServiceNo=t.ServiceNo

..and so on.|||

Yes, that does the job very nicely indeed - thank you, it's also given me a pointer to other tables I need to copy the contents of.

nice one!