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 |
|
Rocko
Starting Member
26 Posts |
 Posted - 2007-02-23 : 16:16:10
|
| Hello Friends,I have a table that will store approximately 10 million records. I plan my primary key to be an INT type. The primary number will be 7 digits number like 1234567. However about 3 percent of the records have duplicate primary numbers. The only difference is a letter from another column. I found two possible scenarios:1. Create main and child tables. That way we will store the duplicate numbers in a separate table. But will have primary key type INT for both tables.2. Create only one table and change the primary key from INT to CHAR. The number will be combination of values from two columns like 123456A. That way we don’t need a child table.My question is which solution is better from performance point of view?Thanks in advance.Rocco |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-02-23 : 21:25:33
|
| Why not make both columns part of the primaty key?This is really a business question rather than a technical one. What should the primary key be for the entity?2. Would be a bad solution as it is combining multiple values into a single column.1. Would might be bad but it depends on what the data represents.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
Rocko
Starting Member
26 Posts |
Posted - 2007-02-23 : 23:13:20
|
The reason for not combining both columns as a primary key is that I want to perform full text search later on. That require unique single column as a primary key. I was wondering also as you suggest above if the primary index is combination of two columns. When you have unique index as combinations of columns do you have to include them all in the WHERE clause? For example the index is combination of columnA (INT) and columnB (CHAR) if we say … WHERE columnA = 123456 Do we need to include columnB as well or it doesn’t matter?Thanks in advance.Rocco |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2007-02-24 : 02:44:45
|
| why not use an identity for your PK, and then apply a unique key constraint on your two columns.-ec |
|
|
|
|
|
|
Performance question