SQL Server, UniqueIdentifiers or Integer ID's? Which is better? Best practice?

Question

I had to create an application once that had potential new records coming in for the same table from multiple distributed databases, that merged together with manual replication.

I started with Integer ID's, but quickly realized I had to use UniqueIdentifiers to avoid conflicting ID values.

So I got into the habit of ALWAYS using UID's in tables vs. integer ID's. And later I met an expert SQL programmer who said I was doing the right thing for larger systems, and that it was a good habit to develop.

But I still see a lot of other SQL database developers still using integer ID's, even by expert, long-time SQL developers.

So what's the "best practice"?

When is it preferable to use integer based ID's vs UID's?

Answer 1

There's not a single "best practice."

UIDs work better for multiple databases, as you've found out.

Integers index more efficiently.

At one place where I work, we are in the process of converting from (string) UIDs to integers, because the database size (tens of millions of records) and rate and complexity of queries causes significant CPU load due to the inefficiencies in indexing Strings compared to integers.

UniqueIdentifier is a 16-byte number, so it's not a whole lot worse than integer.

Answer 2

If money isn't an merchandise, i might lean in the direction of sq. Server - besides the uncomplicated database engine, you apart from mght get extra constructive peripheral kit which comprise Integration centers, Reporting centers, activity Agent, etc.

Answer 3

Unique Identifiers of course. Let SQL server do the work of generating recordIDs instead of your app trying to randomize or increment a number. see @@Identity

Answer 4

Depends on how lazy you are. UIDs win for me.