Binary As Primary/Foreign Key?

Question

Binary As Primary/Foreign Key?

fw2183

Old Hand

Points: 314
More actions
April 3, 2008 at 6:45 pm

#188655

Is it ok performance wise to use a Binary data type as a primary and foreign key?
Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 1

Well, my initial response would be, no, poor choice. On further reflection, I'll fall back on the usual answer, it depends.

Are you using natural keys in your table design and the binary is the unique identifier for that object? If so, it could be the right thing to use. You should do tests to ensure that it will work well with your data, system & queries.

If you're looking to use the binary as an artificial key, then I'd say no. Use integers or ordered GUIDs instead.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

fw2183 Old Hand Points: 314 More actions · Answer 2

Thanks for the comments. The binary value would be unique in the child table. In digging further I found that the bitwise and is used on integers not binary so I think I can acomplish what I was after (a bit mapped field) with a smallint.

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 3

I'm a bit confused as to what you really want or need...

A bitmapped number suggests that there is some meaning involved, and a key with a meaning is a poor candidate for a key....

/Kenneth

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 4

Uh-oh. Heads up! Celko incoming!

😉

But seriously, that's getting into the natural vs. artifial key argument. If the he is following the natural key approach and the smallint field (bitmapped, honestly, that can be a weak way to store data in a relational database, but I digress) is the unique identifier for the table, then that makes it a candidate key which can become the primary key.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 5

..nah, I have longer hair than Joe 😛

But seriously, 'good' or 'poor' aren't traits whether it's a key or not (primary, alternate, candidate etc).

Anything that serves as a unique identifier for the row is a key. Artifical or natural doesn't matter, it's still the same - a key.

My point is, that if we pick a primary key with some inbuilt meaning, or intelligence (could be artificial as well as a natural) we also add a greater risk that something will force a change of these 'smart' keys further down the road. New business rules, change in organizations etc.. All those little things that we don't want messing with our data. If such a thing happens, and we find that we need to update n amount of PK's all over the place, then that was a 'poor' candidate for a key. 🙂

/Kenneth

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 6

No disagreement here. Now, we need to talk about this bitmap field. Shouldn't that simply be another table & foreign key?

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 7

... it depends..? 😛

But probably, though we don't know, since the op hasn't really explained what it's all about.

Though it feels that nowadays a bitmap is overly complex, and 'saving space' usually isn't what it used to be, there may still be some valid reason for it..

/Kenneth