-4

Possible Duplicate:
SQL primary key, INT or GUID or..?

What is the best practise for using unique identifiers in sql server tables?

I have seen articles regarding using guid's or int's with pros and cons for each.

I am looking to go with the following principal, for external identifiers, guid's should be used but for internal identifiers, it best to go with int's.

I am looking for feedback on this principal and guidelines as to the best approach.

Community
  • 1
  • 1
amateur
  • 40,217
  • 59
  • 181
  • 303
  • 3
    Possible duplicate of [SQL primary key, INT or GUID or..?](http://stackoverflow.com/questions/4557422/sql-primary-key-int-or-guid-or), [SQL Server - Guid VS. Long](http://stackoverflow.com/questions/1171201/sql-server-guid-vs-long), and a few others – LittleBobbyTables - Au Revoir Nov 02 '12 at 18:43
  • One thing to consider along with the purely technical arguments is whether your db is used for adhoc querying. If it is then the concept of humans understand numbers not guids can be very important. – Rich Andrews Nov 02 '12 at 18:50

2 Answers2

0

I agree with your principle. Exposing ints in a URL make web visitors guess, like changing the number to see what the reaction of the website will be. Also an int in a URL sort of shows the size of the database. I don't want that. I like Guids in a URL.

Roger
  • 909
  • 2
  • 10
  • 17
0

I like clustered identity columns for my PK for all tables.

  1. They are simply easier to use because I am a human.
  2. They require less storage (remember indexes use your PK as well)

I use a guid columns additionally if I need to create a non-guessable identifier, but I still have my identity column.

There has only been one compelling argument I've ever seen for using guids as a clustered pk and it was limited to VERY high insert scenarios (hundreds per second as I recall). In this scenario, you don't want one hard drive head doing all the disk writes, so GUIDs would force it to not simply add to the end of the table and write all over the place.

UnhandledExcepSean
  • 11,653
  • 2
  • 32
  • 49
  • It normally is, but the article I read was about extremely high insert operations. Basically, there were so many inserts, that the hard drive could hardly keep up with inserting, so the solution was to use a guid so the inserts would be spread across drives. – UnhandledExcepSean Nov 02 '12 at 19:10
  • Whoops, deleted my comment. I guess that could make sense in situations where you span multiple drives. Seems like that might be offset by a penalty with jumping around on an individual drive from time to time though. I'm not an expert on server administration or anything, just kinda curious. – Chad Nov 02 '12 at 19:14
  • Yeah, I still don't think I would ever start off with using a GUID as my clustered index though. This would only be something I would even consider if I was having terrible insert performance with high volume and had eliminated other things causing the inserts to be slow (triggers for example) – UnhandledExcepSean Nov 02 '12 at 19:17