Primary Key - string or integer
in [Server Configuration]
|
Prev: A Question about SQL Server 2008 Installation
Next: Long execution time for UPDATE tbl SET @var = col = {expression} under SQL2000
From: Alan T on 9 Mar 2010 22:16 > 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. 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?
From: Eric Isaacs on 11 Mar 2010 23:37 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
From: TheSQLGuru on 12 Mar 2010 09:28
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(a)yahoo.com.au> wrote in message news:OFk$gAAwKHA.6140(a)TK2MSFTNGP05.phx.gbl... >> 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. > > 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? > > > > |