Re: [SQL Server Identity and Nhibernate.... what is the latest story?

[Harald Mueller]

// off-topic for NHib, but might be interesting ...

> I personally don't understand why people try to use guid PKs everywhere
...
> They might be appropriate
> in some cases (maybe you have some data that needs to interface with a
> external system?) but don't use them in all of your tables!

We used them everywhere. Why? In our scenario, we sell a software to many small companies (thousands) with their own installations and must live with the fact that LATER, any two of the databases are going to exchange records (ANY records - we do not know today which data need to be exchanged one year in the future).
So we need world-wide unique identifiers from the outset.
Moreover, we want (from experience) to be able to (rarely, but still ...) switch from "table-per-concrete-class" to "table-per-hierarchy", so we also need database-wide (well, actually "inheritance-tree-wide", but that's hard to enforce) unique identifiers.

Guids with NEWID() did all that out of the box.

> I wouldn't think twice about getting rid of them.

Still, we painfully accepted that guids are large and clumsy. We are in the process of going to bigints, but this requires a massive infrastructure that provides unique ids to the installations ("system ids"); and these ids form now part of the bigint PK. Some research had to go into the sizes of the "system id" part vs. the "local object id".

One interesting aspect: For testability and development, we can build a system with a few commands. So, a typical developer "builds" 10...20 systems per day; and in unit test suites, many more are built. Therefore, the system ids required are not thousands (number of our customers), but something like 10000 per day = millions over the years. The infrastructure therefore can distinguish "short-lived systems" from "lasting systems" and can reuse system ids of the former ones.

Yet, from a business perspective, GUIDs ("globally unique ..."!!) seem like the right choice to our problem ...

So much for a story from the trenches ...

Regards
Harald M.

[costa]

@Harald: From what you say it sounds an interesting project and it looks like your use of GUIDs is justified and it has been given a lot of thought.

What I am against is the indiscriminate use of guid fields as a first choice of surrogate primary keys, without analyzing the problem at hand and without weighing the pros and cons. Just because Microsoft uses GUIDs in their tables as PKs or because it's cool and they make you look smart, it doesn't warrant their use.