Datatype for Primary Key

I'd like to use uniqueidentifier in my database as Primary Key
Is this a good idea?
How does it effect performance?
Plz help
ApogeeDo you mean a GUID? If so I would think this is quite a long field for a
primary key to be based on.
If you do use it, and you are generating a random one every time, I would
make sure the index isn't clustered, because you won't be inserting to the
bottom of the table.
"Apogee" <developer@.bitefish.net> wrote in message
news:exmBzrbUDHA.1680@.tk2msftngp13.phx.gbl...
> I'd like to use uniqueidentifier in my database as Primary Key
> Is this a good idea?
> How does it effect performance?
> Plz help
> Apogee
>|||What disadvantages would this have? (not clustered)
Apogee
"Ryan Breakspear" <r.breakspear@.removespamfdsltd.co.uk> wrote in message
news:eeHKXxbUDHA.1692@.TK2MSFTNGP11.phx.gbl...
> Do you mean a GUID? If so I would think this is quite a long field for a
> primary key to be based on.
> If you do use it, and you are generating a random one every time, I would
> make sure the index isn't clustered, because you won't be inserting to the
> bottom of the table.
> "Apogee" <developer@.bitefish.net> wrote in message
> news:exmBzrbUDHA.1680@.tk2msftngp13.phx.gbl...
> > I'd like to use uniqueidentifier in my database as Primary Key
> >
> > Is this a good idea?
> > How does it effect performance?
> >
> > Plz help
> >
> > Apogee
> >
> >
>|||I don't know a huge amount about this subject but I believe using a
clustered index, the data is actually stored in the order of the index. A
clustered index is therefore the fastest type. A non clustered is a normal
index, which contains the information you have included in your index, and
also a link to where the actual record is stored.
If you insert lots of records in the middle of a clustered index, the server
will have to do a certain amount or re-jigging of the data to keep it in the
actual order of the primary key.
I would suggest using a clustered index if you are using an auto increment
primary key, and a non clustered index if you are inserting random values.
If the tables are small, or without much activity, this is largely
irrelevant though.
Ryan
"Apogee" <developer@.bitefish.net> wrote in message
news:Oi88I%23bUDHA.2200@.TK2MSFTNGP11.phx.gbl...
> What disadvantages would this have? (not clustered)
> Apogee
>
> "Ryan Breakspear" <r.breakspear@.removespamfdsltd.co.uk> wrote in message
> news:eeHKXxbUDHA.1692@.TK2MSFTNGP11.phx.gbl...
> > Do you mean a GUID? If so I would think this is quite a long field for
a
> > primary key to be based on.
> >
> > If you do use it, and you are generating a random one every time, I
would
> > make sure the index isn't clustered, because you won't be inserting to
the
> > bottom of the table.
> >
> > "Apogee" <developer@.bitefish.net> wrote in message
> > news:exmBzrbUDHA.1680@.tk2msftngp13.phx.gbl...
> > > I'd like to use uniqueidentifier in my database as Primary Key
> > >
> > > Is this a good idea?
> > > How does it effect performance?
> > >
> > > Plz help
> > >
> > > Apogee
> > >
> > >
> >
> >
>|||I don't like using a GUID for an artificial primary key. It takes up 4 times
as much space as an int (16 bytes vs 4 bytes), and as the primary key is
referenced in other tables and indexes, this can add up to a large amount of
unnecessary space in your database, negatively impacting performance. You
can store more than 2 billion rows in a table when you have a IDENTITY
column starting at 1 with an INT datatype, and that is enough for most
applications. If it isn't you can always use a BIGINT (8 bytes) datatype.
Using GUIDs also makes debugging more difficult than using identity, because
humans are better at remembering 1-2-3 than at remembering 32 character
hexadecimal strings. And with IDENTITY the inserts are generated in order,
which can help debugging as well.
--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"Apogee" <developer@.bitefish.net> wrote in message
news:exmBzrbUDHA.1680@.tk2msftngp13.phx.gbl...
> I'd like to use uniqueidentifier in my database as Primary Key
> Is this a good idea?
> How does it effect performance?
> Plz help
> Apogee
>

Datatype <uniqueidentifier>

When creating table, now we usually include the colunm(Id) whose datatype is uniqueidentifier.

My first question is why choosing datatype as uniqueidentifier instead of int[identity(1,1)] if no replication needed.

My 2nd question is for the uniqueidentifier column, which index is appropriate for the column, cluster or non-cluster?

And the third question is how to set the default for the uniqueidentifier.which function is better, NewID() or NEWSEQUENTIALID()?

Thanks in advance.

uniqueidentifier is a type of GUID which means that it is unique. It is larger than int style fields and unless you are using NEWSEQUENTIALID() the new values are going to be random (so don't cluster on it unless you use that - or your insertion point will be random in your index). Even with NEWSEQUENTIALID() the values will not normally be consecutive, just increasing.

Generally I would use an identity column for a single table with no multi-source issues (uniqueidentifier is good if you are taking records from various places and merging them). It is smaller (4bytes for int, 8bytes for bigint as opposed to 16bytes for uniqueidentifier), and it is much easier to type in a SQL statement. It is also easier for the processor and memory access to deal with as a value.

As noted it is not a good idea to cluster on an random uniqueidentifier. Even if it increasing why do you want to cluster on it. Generally you do not query by a range of GUIDs. As clustering determines the grouping of the records on disk it is normally better to cluster by something that matches your common querying - to minimise the amount of disk access to return the record. Single record access (if you do retrieve by GUID) is not really affected by clustering as you are only after one record on one datapage.

|||

Here is some additional information that will help you resolve your question. (Generally, avoid GUIDs in situations where Replication is not involved.

GUID -Identity and Primary Keys
http://sqlteam.com/item.asp?ItemID=2599

GUID -Is not Always GOOD
http://bloggingabout.net/blogs/wellink/archive/2004/03/15/598.aspx

GUID -The Cost of GUIDs as Primary Keys
http://www.informit.com/articles/article.asp?p=25862&rl=1

GUID -Uniqueidentifier vs. IDENTITY
http://sqlteam.com/item.asp?ItemID=283

|||

1. Identity is better to use if you never planned for replication. It is one of the biggest datatype (16 Bytes) in the sql server. If you are going to use to identitfy your row then better use it Identity (int/smallint/bigint,1,1) is more enough. But GUID allocates 16 Bytes on each row.

2. Creating a index on GUID is really bad idea unless its required. Since the datatype is huge the indexes will occupy more space and the manipulation also slow.

3. Setting default value ColumnName UniqueIdentifier Default NEWID()