What do you thing how slow will that be if string is used as primary key
field type?

I know most of the primary key defined as integer type (eg.auto-increment).

We are using string as primary key field type: VARCHAR(36).
We use the GUID type string.

If you're using a GUID, use the built-in data type of
uniqueidentifier. It's a 16 byte value whereas INT is a 4 byte
value. You'll take a slight hit, but UNIQUEIDENTIFIER is basically a
numeric value, as you're comparing bit for bit, whereas with character
data you have upper and lowercase characters to contend with that you
won't with a UNIQUEIDENTIFIER data type.

http://msdn.microsoft.com/en-us/library/ms187942.aspx

If you have the need for a GUID, UNIQUEIDENTIFIER is the data type you
should be using. Do not use VARCHAR(36).

-Eric Isaacs

There will be performance difference. You have to test to see how much, but INT will be faster. INT data type takes 4
bytes for storage and on the other side VARCHAR(36) can take up to 40 bytes (up to 36 bytes for data + 2 bytes to keep
track of the length + 2 bytes that each row that has any variable length column takes).

--
Plamen Ratchev
http://www.SQLStudio.com

As stated earlier, varhcar(36) will take up to 40 bytes of storage, that is
up to 10 times the size of an int. Furhtermore, please note that primary
keys tend to create a unique clustered index. Generally speaking GUIDs are
not good candidate for clustered keys, as their values are random by nature.
If you should still want to use a clustered index, keep in mind that each
nonclustered index in the same table also contian the clustering key, that's
right, your 36 byte varchar. So, as you have figured out now, there are
quite a few pitfalls using strings or GUIDs as primary keys.


Ole Kristian Bangås

"Alan T" <alan_NO_SPAM_pltse (AT) yahoo (DOT) com.au> wrote

GUIDs are awful for a bunch of reasons and you should spend the effort to
engineer them out of your system if at all possible.

--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net


"Alan T" <alan_NO_SPAM_pltse (AT) yahoo (DOT) com.au> wrote

Hi,
At the moment I am migrating our old desktop database to MySQL as it should
be a generic question no matter MySQL or MSSQL Server.

There is NOT really a primary key in the old database tables so I started
using the GUID function from programming tools.
But strip off the opening and closing braces.
eg
003FBAB9-C0E9-49BA-8922-661101DA03D8

In old tables, the fields will be something like for employee table (master
table):
EmpNo EmpName
AP001 Andy Penn



I defined the primary key as VARCHAR(36) for each table to make like
EmpID EmpNo EmpName
003FBAB9-C0E9-49BA-8922-661101DA03D8 AP001 Andy Penn


The reason of NOT using auto-increment because of difficulties when I faced:
1) data migration
I need to link master table to detail table, if I use auto-increment:
When I migrate 1 master record, I need to fetch the new auto-increment
integer from master table, there is another database retrivel trip.

Employee (master table) --> Emplyee Orders (detail table)

Such as
while still more old employee records
import old employee record into new employee table
get this new primary key from new employee table (ie. EmpID)
import employee orders with this new EmpID as foreigh key
end;

if I generated 36 character string in my migration utility:

while still more old employee records
generate 36 character string as EmpID
import old employee record into new employee table with this new EmpID
import employee orders with this new EmpID as foreign key
end;

What do you think?

I think you're going to find it hard to maintain and difficult to
program. Concatenating the keys together to generate a single primary
key will leave you with values you can't index and can't compare
directly to values in the other tables. I would opt for INT primary
keys, but you would be much better off with composite primary keys
(three separate fields as the primary key) than a concatenated primary
key of three key values into one field. I would use INT first, GUID
second, and composite as a last resort, but concatenating the key
values to generate a primary key would not be a design I would
recommend.

-Eric Isaacs

I think you should listen to what I told you and refactor your database.
:-) GUIDs (or worse, varchar(36) storage of GUIDs) is horrendous for
database performance. There are many reasons for this that go beyond a
simple forum post. You can push the "I believe" button or you can engage me
as a consultant for further advice/discussion. :-)))

But hey, if you have a small amount of data and not much concurrent access
have at it. Anything will work in that world.

--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net


"Alan T" <alan_NO_SPAM_pltse (AT) yahoo (DOT) com.au> wrote