UNIQUE identifier - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

UNIQUE identifier

Author

Topic

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-12-09 : 13:02:29

Greetings

From your experience what have you found to be a good unique identifier candidate?


	UI_String    UNIQUEIDENTIFIER      DEFAULT NEWID(),		
	UI_Binary   BINARY(10)      DEFAULT NEWID(),
	UI_VARBinary   VARBINARY(10)      DEFAULT NEWID(),

If you don't have the passion to help people, you have no passion

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-12-09 : 13:09:09

What are you using it for? PK?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-12-09 : 13:55:58

NEWSEQUENTIALID ()

And I know that's where you were going with this Tara

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-12-09 : 14:01:13

I would rather not use it as PK. I want to use it for uniqueness purpose and something that can be indexed and will be good for performance. This is what the dev manager wants to use not the IDENTITY column though that is unique also

If you don't have the passion to help people, you have no passion

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-12-09 : 14:10:58

For performance purposes, I think identity will be fine. Better than a GUID.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-12-09 : 17:18:41

quote:
Originally posted by russell

NEWSEQUENTIALID ()

And I know that's where you were going with this Tara

Yep. If you have to use a GUID and if you are going to make it clustered, then please use a sequential one.

BUT! Don't use GUIDs!

I'm in training with Kimberly Tripp this week, and she directed us to this excellent article of hers that really shows the degradation of GUIDs vs. identities: http://www.sqlskills.com/BLOGS/KIMBERLY/post/Disk-space-is-cheap.aspx

I always knew you should avoid GUIDs, but it's nice to have benchmarking to prove it (so I can send to the developers who want to use them).

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-12-09 : 17:34:19

quote:
This is what the dev manager wants to use not the IDENTITY column though that is unique also

Identity IS NOT UNIQUE. Unique/primary key constraints enforce uniqueness. If you go the identity route make absolutely certain you have such a constraint on it.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-12-09 : 17:38:21

quote:
Originally posted by yosiasz

This is what the dev manager wants to use

Have the dev manager read the link I posted from Kimberly. He will change his mind. Use an identity column, not a GUID. And enforce uniqueness with a constraint (a PK specifically) as Rob mentioned.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-12-09 : 17:47:29

I've trained with Paul and Kimberly a few times. They're the best. I'm sure you're having a good time!

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-12-10 : 11:01:41

wow you always learn something new! this is an awesome post by Kimberley, thanks for sharing Tara! must read for every sql developer, dba etc!!!!

If you don't have the passion to help people, you have no passion

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-12-10 : 11:05:37

ok the reason we want to use GUIDS is that we have a dam system that gets data from different domains. the dam still needs to keep a link to the original source, hence the idea to use GUIDS, that is the only reason we want to use GUIDS to have some uniqueness. if we used identity you can see where it would have limitations. you see what I am trying to get @?

Rob thanks sir, I am using constraints on all my PKs

If you don't have the passion to help people, you have no passion

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-12-10 : 12:55:23

If you are going to use a GUID, then make it sequential. If you can't make it sequential (it's a perf hit too), then don't put a clustered index on the GUID. Also reduce your fillfactor, perhaps to 70%, to allow room for the random inserts and rebuild the index regularly.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-12-10 : 13:18:51

Tara

This is awesome!

1. I will make it sequential.
2. I will most definitely avoid putting a clustered index on that GUID
3. Reduce fill factor to 70% on that GUID or all indexes?

I read the following

Reads (higher fill factor)
Writes (lower fill factor)

So I was thinking of designing my fill factors based on that. Not using clustered on PK is a new revelation to me!

If you don't have the passion to help people, you have no passion

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-12-10 : 13:34:15

3. Just the GUID, but you should evaluate FILLFACTOR for all of your indexes.

We use 100% fillfactor for ALL of our indexes currently. We did extensive analysis on reducing it to help with inserts/page splits of badly chosen clustered indexes, and the performance degradation of reads was too high as compared to the small performance gain of the inserts.

Now I may redo that analysis as I discussed it with Paul Randal today. He indicated that my analysis should not have resulted in what we saw and that we need to look at our indexes. He thinks that we likely didn't have the right indexes in place.

I am definitely going to change the fillfactor on my one system where GUIDs are in use as the PK (and the clustered index!). Unfortunately, the design wasn't chosen by the database team, so we are stuck with it for now.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources