September 12, 2005 at 11:59 am
Hi,
I want to have the primary key column of a table to be of type VARCHAR. Usually, I use a column with identity set to yes as my primary key field. But in this particular table, the primary key must contain couple of characters that comes from somewhere else. I thought I will add a unique number at the end of the characters to make sure they are unique. But I am not sure how to do this.
One thing I can think of is to generate a randon number and append it to the string. And check for this primary key in the table. If this key already exists then generate a new random number and try again.
But is there any easier way accompolish this?
Thanks
September 12, 2005 at 12:18 pm
see if uniqueidentifier can help you
Amit Lohia
September 12, 2005 at 3:16 pm
How about a composite 2 column key, your varchar plus an int identity ?
September 12, 2005 at 3:43 pm
That would work. But it will be easier to work with one column. I guess I will go with 2 columns if I couldn't figure out anything else.
September 12, 2005 at 4:02 pm
Varchar values use typically 36 different combinations per each byte instead of 256 possible. So you use only 14% out of each page in your index. Your index (and column as well) going to be 6 times bigger and 2..3 times slower than the same capacity index on int column.
You cannot avoid this varchar column and index on it, but don't make it PK because you will multiply by 6 sizes of all columns refer this PK (and indexes on it). Let it be 1 varchar column and int (binary) column as key for this column.
Of course, if you have only 10k rows and don't care about performance...
_____________
Code for TallyGenerator
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply