Hi there,

I need to create unique primary keys in my tables but I don't care about the
format of these keys. They're used simply to identify the record and serve
as foreign keys in other tables (users never see them). I've always relied
on autoincrement keys for this in the past (normally stored as integers) but
now I see SQL Server has a "uniqueidentifier" type which is just a GUID by
default (apparently you can change this though). It's been a long time since
I've done any SQL Server programming so:

1) How exactly would I create an integer field that serves as an
autoincrement key (what do I set in VS which is where I'm managing my DB for
now)
2) Why would (or should) I use a "uniqueidentifier" instead of 1 above. If
an autoincrement key is incremented for each new record and will never be
reused (and my tables will never approach 2+ billion records), what's the
point of a "uniqueidentifier" in this case (if any). It just takes up extra
space and buys me nothing in return (compared to the smaller 32 bit integer)

Thanks in advance.

Show quote Hide quote CREATE TABLE Table1
(
        Table1ID int IDENTITY(1,1) PRIMARY KEY
)

If the database is spread over many servers, the GUID makes more sense, as
it is statistically guaranteed to be unique.

If you have clients that are detatched and can create new records, the GUID
makes more sense.

Peace and Grace,


Show quoteHide quote

Thanks. I should be able to (now) find the equivalent in VS itself.

That's a good reason but not applicable in my case

Also N/A.

Thanks for your help!

You wanna create the field in codes? in GUI?

In my opinion, an autoincrement key will be a bottleneck if there are
many requests at the same time.
Because the key has to be added in order. GUID has no this problem,
there is no relations between two GUID number.

Good luck.


Maybe contain nuts.

You wanna create the field in codes? in GUI?

GUI. The tables are statically created and the schema never changes.

I'd have to check that. Perhaps you're right but I honestly have my doubts
(about any noticeable performance hit). GUIDs would appear to be worse since
they're longer and are more time-consuming to generate :)

Thanks for your help.

You have no idea what a key is at all.  It is a subset of the
attrbutes of an entity that uniquely identify each occurence of the
entity.  You should care very much about it

That is an exposed physical locator.  It is like a 1950's pointer
chain; you (the user) have to maintain it manually and it has nothing
whatsoever to do with the logical data model.  It lets you avoid
learning RDBMS at the expense of data integrity.

autoincrements look just like record numbering on a magantic tape
file, so you can stay in your comfort zone.

No, it sounds like you have been doing 1950's COBOL file sytrem
programming with T-SQL and have never actually written a proper
schema.  Get a book on basics and read it.  Look at most of the
posters in the newsgroups.  They do not post DDL -- often because they
have no idea what it is -- and invent their own pseudo-code.  They
have no idea what a spec is, but post requests for ways to implement
an approach they have locked into -- and it is usually a non-
relational approach at that.

What we have are some really lazy programmers who want to use the
newsgroups to do their job or homework for them.  Even worse, they
want to get an instant college education which is not possible in a
short reply.  The questions they ask can most often be answered by (1)
RTFM, well BOL (2) "Try it and see" (3) a quick Google search in the
newsgroup to which they are posting.

What they get instead from most replies is a kludge to get rid of
them.  If this was a woodworking newsgroup and someone posted "What is
the best kind of rocks to pound screws into fine furniture?" are you
really helping them when you say "Granite! Use big hunks of granite!"
I am the guy who replies with "Your question is bad.  Don't you know
about screwdrivers?"  And I like to remind them that it takes six
years to become a Journeyman Union Carpenter in New York State.  Not
Master, Journeyman.

yui (_nospam@_no_spam.com) writes:
One possibly advantage with GUIDS is that these can be generated client-
side, and you don't have to make a round-trip to get the value. This can
make things easier if you need to insert, say, both Orders and OrderDetails.

On the other hand a GUID is 16 bytes, four times an integer. GUIDs can
also lead to performance problems with fragmentation if you don't deal
with them correctly.

In short, as long you don't have any problems with integer keys, stick to
them, and only use GUIDs if you find that you actually need to.

Can you elaborate on "don't deal with them correctly". What control do you
have over fragmentation without defragmenting the DB itself (during some
maintenance period in most cases)

This is how I see it. Thanks for your insight.

yui (_nospam@_no_spam.com) writes:
A good start is to not make the index on the GUID column clustered.

If you make it clustered, you should give it a low fillfactor, 30-50%.
When new rows will be added in the gaps, and there will be no page splits,
until you start to fill up the gaps. Then it's time for an index rebuild
with a low fillfactor again.

An index on GUIDs with a default fillfactor will see lots of page splits,
which leads to poor INSERT performance and also fragmentation.

Show quote Hide quote Thanks for the info. It's something worth investigating (to reduce
fragmention in general) but I'll be sticking with integer keys in any case.
It serves my purpose. Thanks again.

yui (_nospam@_no_spam.com) writes:
Just to make it clear: the stuff with fillfactor etc that I discussed
pertains to GUIDs. If you use monotonically integer keys, there will
not be issues with pages splits etc as you would get with GUIDs.

"uniqueidentifier" field can help to keep id "secret"

e.g. when you have

site.com/userprofile.aspx?id=100
site.com/userprofile.aspx?id=101
site.com/userprofile.aspx?id=102

then everyone knows how many items you have because of 100, 101, 102

This

site.com/userprofile.aspx?id=6F9619FF-8B86-D011-B42D-00C04FC964FF

comes in handy if you like to hide that information.

As from the database point of view, uniqueidentifier is useful if
you're consolidating records from multiple databases into one table.
On another hand, uniqueidentifier has long values, uses more space and
it is recommended to use the IDENTITY when global uniqueness is not
required, or when having a serially incrementing key is preferred.

That's a good point though not applicable in my case (but useful to consider
for next time)

Merging DBs isn't an issue in my case but if it's occuring during some
maintenance period only, integer keys can easily be resequenced. The issue
only becomes significant IMO when dealing with a distributed DB system whose
keys must be kept unique at all times (which is what you're probably
alluding to). Based on your input and others, I should be ok with
autoincrement (integer) keys. Thanks for your feedback.

Show quote Hide quote Glad to help

Is Delegation Necessary?
session state time out
Using if on ASPX-side instead of in code behind
restrict number of users
Calling Application_Start After Worker Process is Recycled
Page refresh - Alternative to using frames
á ÇáÅÓáÇã Úä ÇáÅÑåÇÈ¿
Redirect To Login Page - Forms Authentication
Trap Data Tier Errors?
How can the Login control stop authenticating?