Using auto-increment clustered key and guid primary key together

Question

My boss is the db administrator in a new software project and he came up with that design where every table will have both a auto-increment clustered key and a guid primary key (or possibly composite guid keys on some rare cases). Every relations between tables will still use pk as references, making the clustered field never used in views, procedures, functions or any code at all.

I'm no db guru, but I wonder if in this scenario it's even worth having a clustered index at all? I mean, won't records systematically be added to the end of tables? If so then why would I need this field?

• Ewan: Hes crazy, guid all the way
• Thomas Stringer: What's your boss' justification for having two different and separate unique identifiers for data rows? I can't think of any reason for this. And why GUID? There are a few corner cases where GUID is desired over sequential numbers, but they are extremely few and far between.
• Crono: @ThomasStringer He was told (and so was I) that guid clustered indexes weren't performant. He also believes that clustered indexes are still mandatory so that other indexes can rely on it (that I don't know enough about to argue).
• Crono: @ThomasStringer as for using guids, this is mainly for the ability to uniquely identify something throughout the whole system, and eliminating the need to use functions like SCOPE_IDENTITY() to retrieve newly created record key.
• Brian: @Crono: You may want to also investigate the use of Sequential GUIDs, which fix some of the performance problems of standard GUIDs...on condition that they're mostly generated by the same machine. The other alternative is a Comb Guid, which is performant even when used on multiple machines...but has less native support.

Answer 1

Advantage:

1. GUID is unique so in the long run if you run into this scenario where you can join using just Guid rather than PK+other field. ex:there sales, order, adjustment, and there is stock, instead of join using PK and transaction type, you can just join using Guid since it 99.9...% guaranteed unique.

2. By using GUID you can generate GUID from code so it remove a need to do callback to get new PK.

3. By having auto-increment you can get latest transaction just by ordering auto-increment instead of transaction date(stackoverflow using this method)

• Crono: #3 is of little interest in our scenario as most of our tables have a CreatedOn datetime field. Still, +1.
• Brian: An identity column int key is also unique within the table, so #1 is not normally valid.
• kirie: @Brian Identity column only unique across 1 table, while GUID unique across database. hence the example I gave to explain the use.
• Brian: @kirie: It still makes no sense to me. I guess you could have only an objectGuid foreign key that mapped to multiple tables, but that's horribly confusing, very inefficient, and doesn't work well unless the tables you are joining to are similar. A database with that sort of design should probably be a document database, not a relational database.
• kirie: @Brian It don't have to be similar, you just have to know that StockTable would contain transaction from sales,order,adjustment and just join every PK to SourceId, where when using int as PK you must have TransactionType to join using PK=SourceId and TransactionType. It is really small addition of course, and there's a lot of disadvantage when using GUID too, but since this just answering OP question I omit all the disadvantage.

Answer 2

Well as already stated in the comments of the other question, this is nonsense if you won't query using the id at all. If you use just the GUIDs then leave out the clustered index and just setup the no clustered index on the GUIDs.

But never use the GUIDs as clustered index unless you want to have a stress test on your I/O subsystem. ;-)

• Crono: We seem to think alike. Out of curiosity, what do you think of the other answers?

Answer 3

It is not unusual to have several keys in a table and there's nothing fundamentally wrong with that. If a table has more than one key then the choice of "primary" key is unimportant - or only as significant as you want to make it.

Your boss is correct that it is usually a good idea for every SQL Server table to be clustered. Guids don't make for good clustered indexes and where there is no decent candidate for clustering it sometimes make sense to add an incrementing integer key for clustering. What I think is wrong in this case is the suggestion that you do this for every table. Key selection should be based on actual requirements and practical considerations for each table. One size fits all solutions are really no help.

• Crono: It's done for every table because there's another table which holds multilingual resources for data throughout the database. These resource sets are glued together with their respective datarecord's guid.