To read the article online, visit http://www.4GuysFromRolla.com/webtech/sqlguru/q013000-3.shtml

The SQL Guru Answers your Questions...


Today's question comes from Scott H.

Hi!

I am new to SQL Server and doing any database functionality. I would like to create my own unique id's and use characters in the field. Is there any problem doing this versus using the SQL Server to do it with an identity column (seed and increment value)? Is there anything wrong from a design perspective of having unique id's with letters and numbers in them as far as speed/efficiency goes?

In the column it mentions you have wrote some books? Do you have a beginner book? How about one on SQL 7.x?

Thanks!
Scott Hand

Scott,

I'll attempt to answer your question one part at a time...

I am new to SQL Server and doing any database functionality. I would like to create my own unique id's and use characters in the field. Is there any problem doing this versus using the SQL Server to do it with an identity column (seed and increment value)?

Well, it depends. If you're just creating unique id's for the sake of creating them, and they'll have no inherent meaning (a surrogate key), then it's best to let SQL Server do the work. You can use an IDENTITY field, or (in SQL 7.0) a uniqueidentifier (also known as a GUID) field.

However, it sounds like you're wanting to create primary keys that have some meaning outside of providing a unique value (a meaningful key). Which sort of leads into your follow-up question:

Is there anything wrong from a design perspective of having unique id's with letters and numbers in them as far as speed/efficiency goes?

Meaningful keys present a few challenges that you'll want to be aware of. First, they tend to change over time (because, after all, they are storing data that means something). If a meaningful PK is used as a foreign key in related child tables, then you'll need to update the FK values in the child tables as well to maintain referential integrity. Not a huge deal, but it means extra work for you. (And I believe that one of the marks of a good DBA is laziness :))

Also, meaningful keys can pose performance problems if they're quite large. Consider a PK that is someone's social security number. If you take out the dashes, a SSN is 9 characters. If you pack that into a char(9), it will use 9 bytes of storage in the base table and in any tables where it is used as a FK. Contrast that with an IDENTITY in an int field, which uses 4 bytes of storage. In this case, the difference is fairly minor, but you'll incur more I/O and your indexes on this field will be larger, which can slow access through those indexes.

In the column it mentions you have wrote some books? Do you have a beginner book? How about one on SQL 7.x?

My latest book is really geared towards DBA's that are already familiar with SQL Server. However, a friend of mine has updated the MCSE Test Prep book for SQL 7.0, and it's a pretty decent introduction to implementing database solutions w/ SQL Server. I contributed a few chapters in the 6.5 version of the book that made it through mostly unchanged, so I feel comfortable vouching for it :)

You can buy the book at: http://www.amazon.com/exec/obidos/ASIN/0735700044/

Hope this helps,
Sean


Article Information
Article Title: SQL Guru: Creating Non-Numeric Primary Keys
Article Author: Scott Mitchell
Article URL: http://www.4GuysFromRolla.com/webtech/sqlguru/q013000-3.shtml


Copyright 2017 QuinStreet Inc. All Rights Reserved.
Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers