Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
lionofdezert
Aged Yak Warrior
885 Posts |
 Posted - 2009-03-20 : 02:29:58
|
| I need to create a surrogate key, What ll be the best choice1-Identity (Which is smaller in size and easy to handle)OR2-GUID(16 Bit in size and need more attention from developers)WHAT IS THE BEST CHOICE AND WHY ???(No replication possible in futur for the said table) |
|
|
pootle_flump
1064 Posts |
Posted - 2009-03-20 : 07:24:39
|
| There is no simple best choice, it depends on your needs. I personally would say if you have no specific need of the additional properties of a GUID (i.e. globally unique) then I don't think it really brings anything much extra to the party.Your developers should never need to pay attention to whether or not a surrogate key is int or GUID. Why do you think they would? |
 |
|
|
lionofdezert
Aged Yak Warrior
885 Posts |
Posted - 2009-03-20 : 07:59:42
|
| http://furrukhbaig.wordpress.com/2007/08/18/guid-vs-identity/ |
|
|
|
pootle_flump
1064 Posts |
Posted - 2009-03-20 : 08:43:06
|
| That is not a good analysis of the debate, and is incorrect in some parts. But it does get the broad issues. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-03-20 : 09:55:06
|
| Fragmentation is really high on GUID compared to identity Primary key. |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2009-03-20 : 10:20:32
|
| ...for which reason the SequentialGUID was created.________________________________________________If it is not practically useful, then it is practically useless.________________________________________________ |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2009-03-20 : 10:28:21
|
| There was a lot of misinformation in that blog post..."The key issues with GUID is its very large (16 bytes) and contain alpha-numerics."Wrong. It is simply displayed as alpha-numeric. GUIDs are not strings."Again it will cause very high fragmentation and page split on data pages as well as index pages"Can be mitigated by using a SequentialGUID."while sql 2005 has also introduced sequential guids but we are generating guids on client side"If you are generating surrogate keys on the client side, you have to use GUIDs. You can't generate integer Identity values with any degree of efficiency."Always remember whenever client passes GUID value to be searched to database its always rendered as string and thats the last thing you want any database to do."Stored procedures and dynamic queries are fully capable of accepting GUID datatypes as parameters, without any implicit or explicit conversion.________________________________________________If it is not practically useful, then it is practically useless.________________________________________________ |
|
|
|
pootle_flump
1064 Posts |
Posted - 2009-03-20 : 10:44:28
|
| That is much of the point - Identity Vs GUID is a misnomer. GUIDs are data types, Identity is a column property. Identity columns are not immune to fragmentation, GUIDs do not inherently cause it.I would still like you to expand on "need more attention from developers". It sounds like your developers are doing something they shouldn't - it should not matter one jot to them whether or not you use GUIDS over identities. |
|
|
|
|
|
|
Identity or GUID ???