MSSQLTips

http://www.mssqltips.com/tip.asp?tip=1600

Great article ... I've found this out too and its important to think about up front in the development process.  Trying to change later can be difficult.

 There is a problem with this image - SQL Server actually sorts GUIDs by byte group from right to left.

Very interesting..I'm putting together a new distributed DB design using GUIDs as unique identifiers to ensure uniquness across many computers. We are still in early development, so design and implementation changes happen daily as I work through everything. I found this article interesting because it may represent some things I can do early to prevent some of the inherent problems with using GUIDs as identities. But there were a few points I was unsure about.

The author described page splitting that occurs, when discussing the defragmentation differences between using an incremental integer for an identity vs. a GUID.  He wrote:

Though you can defragment the NEWID() table, the random nature of the key generation will still cause page splitting and fragmentation with all future table INSERTs. However, page splitting can be minimized by specifying an appropriate FILL FACTOR.

Question 1 would be "how are tables defraged?" (I am more a programmer than a DBA for what it's worth) and 2. Describe "fill factor" - how would I implement that? What is it specifying?

<< There is a problem with this image - SQL Server actually sorts GUIDs by byte group from right to left >>

Yes, you're correct.  The image is meant to shows the differences in how the engine generates the value (i.e. the randomness of NEWID() vs NEWSEQUENTIALID())

You defrag tables by doing an clustered index rebuild/reorganize.  BOL will give you more info on this.

A lower fill factor will leave room for more items to be inserted, reducing page splits and improving performance of inserts.  Lower fill factors will have a negative impact on select performance though.  With an auto-increasing key (identity/seq guid) you can use a high fill factor - new items will be inserted at the end of the index. Fill factors are specified when the index is created - BOL will provide you with more info.

I found the conclusions at the end of the article a little bit biased to the identity usage. OK so far so good, IDENTITY has advantages. Doesn't it have any clear disadvantages? Does it really outperform GUIDs in performance?

I am not an SQL DBA but more a developer and I think GUIDs have undeniable advantages over IDENTITY. ie:

-I can create the key independantly from the data store. I know it is unique so I wouldn't bother to create data on my laptop totally disconnected and later insert to main db without any change in keys.

-Not needing db store to learn the key also means I wouldn't need roundtrips to server. Those roundtrips might sum to a big amount. Performance wise maybe it is better to use GUIDs then?

I wonder, if GUIDs are disadvantegous, why would ever SQL server itself use GUIDs in replication? SQL server team developers might do that with integer key fix ups, but they chose GUID. Probably they thought using GUIDs is cheaper than doing key fix ups?

(just thinking loud)

<<Question 1 would be "how are tables defraged?">>

 Depends on your version of SQL Server

2000: DBCC DBREINDEX or DBCC INDEXDEFRAG
2005: ALTER INDEX specifying either REBUILD or REORGANIZE

You could also drop and re-create the clustered index

<<2. Describe "fill factor" - how would I implement that? What is it specifying?>>

Creating indexes with a FILL FACTOR % tells the engine how full you want the index leaf pages (i.e. a FILL FACTOR of 80 means keep 20% of the leaf free for future inserts - or 80% full).  It minimizes splits (an intensive operation where 1/2 of the data on a page is moved to a new page) because the free space can accommodate any random row that can fit on the page.  It's used in high volume enviorments where page splitting becomes a problem.

GUIDs do have benefits over identity columns but they come with quite a large overhead - 16 bytes compared to 4bytes for an integer.  Some of the problems with uniqueidentifiers can be overcome by using sequential guids, but identity is far better from a performance point of view.

Bottom line: Identities should be your first choice. If an identity column doesn’t fit the bill, consider a GUID. 

Also, don’t take peoples word for it.  Construct your own performance test to see how they perform in comparison to each other.  I’ve done so myself and found the difference to be significant.  The difference between sequential guids and non-sequential guids was also VERY significant.

 <<I found the conclusions at the end of the article a little bit biased to the identity usage>>

In reality, it wasn't bias - they were statements in fact.  Using IDENTITY results in less database bloat.  Fewer data and index pages are generated (less churn for DML statements) as well as less bloat of non-clustered indexes if the GUID is the clustered index.    

The reason they probably chose GUIDs is due to their uniqueness across space and time.

Thanks for the followups to my questions!

aprato:

The reason they probably chose GUIDs is due to their uniqueness across space and time.

If uniqueness across space in time is a fundamental requirement of a given distributed database archicture, am I correct in saying that this requirement alone makes the decision for us, and requires the use of GUID over IDENTITY, despite any inherent performance issues that come with that decision?

M

pmh4514:

Thanks for the followups to my questions!

 

aprato:

The reason they probably chose GUIDs is due to their uniqueness across space and time.

If uniqueness across space in time is a fundamental requirement of a given distributed database archicture, am I correct in saying that this requirement alone makes the decision for us, and requires the use of GUID over IDENTITY, despite any inherent performance issues that come with that decision?

Yes - more or less.  You might also consider composite keys - using identity in conjunction with another column. You could also generate your own "unique" identifier based on some custom logic. You might even consider splitting identity ranges between tables. In this situation GUIDs would probably be the most attractive option IMO.

 <<Yes - more or less.  You might also consider composite keys - using identity in conjunction with another column. You could also generate your own "unique" identifier based on some custom logic. You might even consider splitting identity ranges between tables. In this situation GUIDs would probably be the most attractive option IMO.>>

I would concur with Wiseman.  GUIDs may be the better option if you're working in a distributed environment. 

I was referring to section starting with:

"It's evident that using IDENTITY to auto-generate key values offers a few advantages over the GUID approaches"

and you say:

"The reason they probably chose GUIDs is due to their uniqueness across space and time."

It sounds like a very good reason to choose a surrogate primary key to me so in the same manner I could say:

It's evident that using GUID to auto-generate key values offers a few advantages over the IDENTITY approaches

this statement is a fact too but I didn't see any mention and naturally thought it as biased. I didn't have a negative intention just meant I felt the article was not completed.

I didn't take it negatively at all.  I understand what you're driving at.