Disadvantages of using uniqueidentifier (Tell your Tale)

Dear SQL...

I'm about to useuniqueidentifier andNEWID() for my new DB,

The DB may grow wild and I want to super safely create connections between tables,
so it would not just rely on numbers (that can B changed) as used with IDENTITY...

If U had some BAD experience with this or if U have any tips I can use please let me know

+

Should I take any special steps when usingindex on it ?I found a GREAT post that helped a lot:
Seeded(int) Identity columns vs. UniqueIdentifiers(GUIDs)

But still -what about the INDEX ?

1. Should I put an index on a uniqueidentifier column at all ?
2. Will the index consume big SQL Server space ? (what proportion)

OR

Is it a good idea to save it as achar string instead of uniqueidentifier ?
(will it help to manage the index better N faster)|||Hi,

I love GUIDs as surrogate primary keys. They solve a lot of problems and make coding simpler. And for most apps, the slight performance degradation is nothing compared to network latency and other factors.

No, don't bother indexing them separately. There's nothing to order; order doesn't matter at all.

Don|||Don, so what will happen if I'll have a big table ?
I guess that as the table gets bigger selecting one record will take longer...

Am I wrong ? - will SQL Server not just go throgh *ALL* records to find one ?

+
What is your opinion about saving the GUID as a string of chars ?
(will allow using the speed of an index ?)|||No, it won't necessarily. That's why I said aseparate index. If you use it as the primary key, SQL Server indexes it for you. That's necessary for it to be a primary key. I see that I said that poorly. Hope this clarifies it.

But also to clarify, you're going to use it as a primary key, right? You didn't say that in your posts, but you referenced the Seeded(int) Identity columns vs. UniqueIdentifiers(GUIDs) article, which suggests you are.

Nope on saving it as a string of characters. You'll just be circumventing the performance optimizations Microsoft built in.

Don|||Thanks 4 your educated answers :-)

I am going to use it for my Primary keys...

ALTER TABLE [dbo].[MyTable] ADD
CONSTRAINT [DF_MyTable] DEFAULT (newid()) FOR [MyTable_ID],
CONSTRAINT [PK_MyTable] PRIMARY KEY NONCLUSTERED
(
[MyTable_ID]
) ON [PRIMARY] ,
CONSTRAINT [IX_MyTable] UNIQUE NONCLUSTERED
(
[MyTable_ID]
) ON [PRIMARY]
GO

(am I right on the NONCLUSTERED ?)

About saving the GUIDs as a string of chars:
I thought that maybe I can also add a prefix (like Date()) to each GUID
so that I can create an effective CLUSTERED index instead of NONCLUSTERED
(using a string of chars or uniqueidentifier this is really my last dilemma on this issue...)

I may also need to create "replications" of this database (which I know none on...)
should I use uniqueidentifier (and not a string of chars) to allow replication ?

Thanks in advanced, Yovav.|||> If you use it as the primary key, SQL Server indexes it for you

yes. and it indexes it as clustered if you don't specify otherwise.

if you're using a guid as a primary key (i regularly do... the benefits far outweight the cost, especially when used in oo applications where you're rarely joining tables anyway), you want to make sure it's NOT clustered. otherwise when you go to insert a new record, data may have to be moved around, as guids could come in any sequence.

i would suggest using a guid as a pk. make it non-clustered. pick something else as your clustered index - normally whatever you would want to order by as default.