Using a GUID instead of auto incremented integer

  • Thread starter Thread starter John Morgan
  • Start date Start date
J

John Morgan

I would appreciate advice on using a GUID instead of auto incremented
integer in a SQL server database which will be accessed through
ASP.NET using ADO.NET.

I have read somewhere that GUIDs are preferred to auto increment.

My main reason for using a GUID is that it will be easy to know the
value of the field (used as a primary key) immediately after using a
SQL INSERT statement. However there seem to be obvious disadvantages
in the size of the database field required ( 25 digits/characters for
my purpose). Also, though I am not expert in SQL Server I know there
is the ability to retrieve a new auto increment number using
@@IDENTITY which I suppose reduces my need for a GUID approach,

Best wishes, John Morgan
 
nGen was built specifically to support GUID types. It will generate the code
necessary to use GUIDs, and will bring back the GUID when it's inserted. Far
better then the other choices out there, most of which totally skipped the
section in the docs about UniqueIdentifiers.

Check out: http://www.nAlliance.com/Products/nGen

KS
 
Thank you John for your reply.
My main need was to ascertain whether there were disadvantages to
using GUIDs over and above the obvious ones such as increased storage
requirements, more complicated to read.

I do not currently see replication being required in the database I am
dealing with, nevertheless I interpret your reply as saying that the
GUID approach is a useful one regardless of whether replication is
involved. I regard it as particularly helpful to know what the ID is
at INSERT time and your example is helpful,

Best wishes, John Morgan
 
I've asked this same question before, and the only drawbacks mentioned then
were the ones you already mentioned...

1) field size
2) complexity of typing a query for a particular row in query analyzer (but
can still be done).

I've heard some DB admin experts claim that if the database is indexed
properly, a query for a record by the GUID PK shouldn't be any less
performant than other numeric datatypes. It just takes more memory space to
hold it. I don't see that as a problem with the price of hard drive space
and RAM.

Are you sure your application will NEVER require replication? (think
scalability) and NEVER exceed 1/4 to 1/2 of the available key values of an
int? (again scalability) If there is uncertainty in those answers, then you
should seriously consider GUIDS.

On the other side of the table, if you have relatively few records, how
expensive would GUID's really be? For a few thousand records, I doubt you
would see any performance difference, and a very small memory difference.
If you aren't "hurting" the database by using GUID's, then use them. They
are much simpler for the developer to use. In my opinion the developers
ease of use is more important than the administrators. After all the easier
to code, the less likely database errors will occur, and thus need a DB
admin to troubleshoot database errors!

I still have learned how to write code with both kinds of keys, and the code
generator in my signature link can work with both.
 
Back
Top