| Author |
Topic |
|
dactilifera
Starting Member
1 Post |
 Posted - 2007-05-30 : 05:36:03
|
| Hy! I am a beginner in SQLServer and I have to design a database with a lot of tables (30-50 tables). I have to choose a primary key for every table between an autoincrement and a GUID. If I choose autoincrement after an insert statement (from the client) I will have to go back to the server to get the value of the primary key which is a waste of time. On the other hand if I choose a GUID I can create the guid from the client and send him with the other values of the new row to the server and I have not to go back to the server. I am aware of the disk space of a guid field so which is the best for me: GUID or AUTOINCREMENT? Thank you! |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-05-30 : 05:51:45
|
| both have pros and cons.google for this:guid vs identity primary keyand read first few results_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp |
 |
|
|
AndyB13
Aged Yak Warrior
583 Posts |
Posted - 2007-05-30 : 06:02:53
|
If you are only concerned about having the new ID value returned to the client you can use SCOPE_IDENTITY() within your insert stored procedure (thats presuming you are using stored procedures and not inline sql) to return the value to the client in one call to the server. That will also save you creating the GUID on the clientAndyBeauty is in the eyes of the beerholder  |
|
|
|
peterlemonjello
Yak Posting Veteran
53 Posts |
Posted - 2007-05-30 : 09:58:35
|
| GUID's have fragmentation implications too. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2007-05-30 : 12:04:00
|
also in 2000 you can use Gert Draper's xpguid.dll to generate sequential GUIDs so your pks don't fragment. google for xpguid.dll.
www.elsasoft.org |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2007-05-30 : 13:33:02
|
| "I have to choose a primary key for every table between an autoincrement and a GUID"Why are those the only valid choices for a pk?If I choose autoincrement after an insert statement (from the client) I will have to go back to the server to get the value of the primary key which is a waste of timeNo, you can get it with scope_identity() and get it as output variable or return result when you do your insert.In fact going back once more, is the wrong way to do it.I am aware of the disk space of a guid fieldYou probably only need to worry about the tables in your database that will contain millions of rows.rockmoose |
|
|
|
Vinnie881
Master Smack Fu Yak Hacker
1231 Posts |
Posted - 2007-05-30 : 20:54:08
|
| This debate has gone on numourus times. A Guid is vary rarly a good option for a primary key (It takes long to generate compared to other methods, and it says nothing about the row, but I have heard of scenerios in regards to very large mergers where that unique GUID is used to ensure it's uniqueness across db's). The best primary keys seem to be derrived from a unique combination of data in the row that will identify it. A Identity field vary rarly should be used by itself for a primary key due to it also says nothing about the row it's in. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-05-31 : 04:46:04
|
| "unique GUID is used to ensure it's uniqueness across db's"Not forgetting that since they dropped the use of the Network Card ID, supposedly so as not to intrude on Personal Privacy, they are no longer guaranteed unique. However, accidentally getting a Dupe is very long odds so I suppose that is regarded as good enough.Kristen |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-05-31 : 04:51:02
|
| GUID are only guaranteed to be unique within the current database.Peter LarssonHelsingborg, Sweden |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-05-31 : 05:32:19
|
| I'm probably wrong, but I'm not sure they are guaranteed even for that are they? There is no longer any NIC ID that makes part of them. Although I suppose the new incrementing GUIDs in SQL2k5 will be using a formula that includes some sort of "local" part. Would they still be guaranteed unique if you move the database to another server?Kristen |
|
|
|
peterlemonjello
Yak Posting Veteran
53 Posts |
Posted - 2007-05-31 : 09:40:31
|
| Here's a quote from paulrandal:What datatypes are not recommended as index keys? Why not?"The worst type for an index key is GUID, because of its randomness."http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=53085&whichpage=2&SearchTerms=guid%2Cindex |
|
|
|
|
GUID vs. autoincrement primary key