Setting up primary key and clustered index on table

[hedgracer]

I have the following table named MDF:

Record_ID varchar(1) Checked
Firm varchar(1) Checked
Office varchar(3) Checked
Account varchar(5) Checked
Acct_Type varchar(2) Checked
SubType varchar(2) Checked
Expiration_Date varchar(8) Checked
Trade_Date varchar(8) Checked
Buy_Sell varchar(1) Checked
Broker varchar(5) Checked
Spread_Code varchar(1) Checked
Tracer_Number varchar(20) Checked
Class varchar(1) Checked
SubClass varchar(1) Checked
Sales_Series varchar(5) Checked
Quantity numeric(18, 0) Checked
Description varchar(50) Checked
Exch varchar(2) Checked
Future_Code varchar(2) Checked
Type varchar(2) Checked
Curr_Code varchar(2) Checked
Cmt_One varchar(3) Checked
Cmt_Two varchar(3) Checked
Cmt_Three varchar(3) Checked
Exec_Brkr varchar(5) Checked
Oppo_Brkr varchar(5) Checked
Grs_Comm money Checked
Clrng_Fee money Checked
Exch_Fee money Checked
NFA_Fee money Checked
Brkg_Fee money Checked
Other_Fee money Checked
AT_GrsComm varchar(3) Checked
AT_ClrngFee varchar(3) Checked
AT_ExchFee varchar(3) Checked
AT_NFAFee varchar(3) Checked
AT_BrkgFee varchar(3) Checked
AT_OtherFee varchar(3) Checked
Ovn_Qty numeric(18, 0) Checked
Day_Qty numeric(18, 0) Checked
Scratch_Qty numeric(18, 0) Checked
Spread_Qty numeric(18, 0) Checked
Ovn_GrsComm money Checked
Day_GrsComm money Checked
Scratch_GrsComm money Checked
Spread_GrsComm money Checked
Buy_Qty numeric(18, 0) Checked
Sell_Qty numeric(18, 0) Checked
Trade_Price varchar(20) Checked
FU_ClrngFee varchar(3) Checked
FU_ExchFee varchar(3) Checked
FU_NFAFee varchar(3) Checked
FU_BrkgFee varchar(3) Checked
FU_OtherFee varchar(3) Checked
Data_Table varchar(3) Checked
Date_Upload varchar(8) Checked
Reg varchar(50) Checked
Dept varchar(50) Checked
Future_RH varchar(50) Checked
Option_RH varchar(50) Checked
Memberships varchar(50) Checked
CTI varchar(50) Checked
GIO_CD varchar(50) Checked
GIO_F varchar(50) Checked
TCALC varchar(50) Checked
PSYMBL varchar(50) Checked
PLEVEL varchar(50) Checked
Unchecked

This table is update daily with about 78,000 rows of data (all columns are filled). I would like to create a primary key on this table to enable a clustered index but this table does not have any columns or groups of columns that feature unique values. I thought of using a rowid column but this must populate every day with unique data. Does anybody have any suggestions for me? Any help is appreciated. Thanks.

Dave

 

[SQLSister]

I would suggest a redesign to normalized tables.

http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx

If you have no unique natural key then an identity or GUID is really the only choice. Do you keep the records for each day or replace them?

By the way you don't need a PK to build a clustered index, any index can be a clustered index. YOu just can only define one per table.



"NOTHING is more important in a database than integrity." ESquared