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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-04-17 : 21:26:58
|
| Eugene writes "Hello,Can somebody tell me what appends, performance wise, if I have a table with no clusters on it and this table's primary key is concatenated (Customer_key + Product_key) ?I am presently building the structure of a relationnal database used in a transactionnal web site. In this database, there is a table called CustomerProductT that creates a link between the customerT table and the ProductT table. This CustomerProductT table gets a lot of INSERTS, and the fields (customer_key combined with product_key) are often called in SELECT clauses. So I was wondering what the best way was to structure this table.Since there are a lot of inserts and selects in this table, sould I (1) put one clustered surrogate key (let's say CustomerProduct_key) as the only key of the table, or should I (2) use the concatenated key (Customer_key and Product_key) as the main key of the table? Logically, I tought that the concatenated key was the best choice since I always call both (customer_key and Product_key) in my select clauses. But If I take the concatenated key, should I (2A) put the CLUSTER on or sould I (2B) leave the table NON-CLUSTERED? The Inserts into this table do not follow the clustering.Logically I tought my best bet what to put the concatenated key (Customer_key, Product_key) with no cluster on the table. So I was wandering, what appends, performance wise, if I have a concatenaded, non-clustered key in a table that as a lot of selects an inserts. That you." |
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2002-04-17 : 21:51:00
|
Eugene,When you say "concatenated" do you mean that it is a Composite Key (2 fields as the key) or you have concatenated the data into a single field?I am hoping that your mean "Composite"Based on the assumption that it is a Composite Key...The Primary Key should be non-clustered egConstraint PK_CustomerProduct Primary Key NonClustered (CustomerID, ProductID) Your Inserts, Update and Deletes should be faster, the Selects will be slower although by a degree that is not of concern.HTHDavidMTomorrow is the same day as Today was the day before. |
 |
|
|
|
|
|
|
|
relationnal architecture : Non-clustered concatenated key table