NON-Random UUID keys in custom distributed DB

distributed-databasesreplicationuuid

Is there any reason other than security for using random (or even semi-random) UUIDs as primary keys to database records?

Is there any reason why I should not WANT to be able to use the UUID to lookup a record? In my case I think that would actually be a FEATURE.

I'm creating a distributed / replicated database with security builtin to it at the SQL level. To access a record, the client MUST first obtain a "grant" that allows the client to access records in "partitions". Each record has an indexed int field that will be checked with every SQL operation using a clause like 'WHERE partid IN (123, 234, 345)'. This is mostly to make queries fast but it is also used to for security / access control. To get those partition ids in your security context, you must first obtain a suitable grant.

So I don't care if someone knows the key of a record. They either have explicit permission to access it or they don't. Obscuring keys by making them random seems like a completely orthogonal characteristic. No?

The part about security and grants is really not critical. I was just trying to stress that I have a security strategy at the SQL level (described more here) and thus I don't (think I) need the UUIDs to be totally random. So there is no minimal reproducible example to post. It's just a conceptual question mostly. A UUID can be unique and not be random. I'm just trying to figure out if there are unforeseen issues with using non-random UUIDs are a primary key.

Best Answer

Yes, you can use a sequentially generated UUID as a primary key. SQL Server even has it built in:

CREATE TABLE dbo.keytest
(
    kt_guid uniqueidentifier NOT NULL
        CONSTRAINT keytest_pk
        PRIMARY KEY
        CLUSTERED
        CONSTRAINT keytest_kt_guid_df
        DEFAULT (NEWSEQUENTIALID())
);

Insert some rows, and SELECT them:

INSERT INTO dbo.keytest DEFAULT VALUES;
GO 10

SELECT kt.kt_guid
FROM dbo.keytest kt

Results:

╔══════════════════════════════════════╗
║               kt_guid                ║
╠══════════════════════════════════════╣
║ D9A1905A-ADBE-E911-9440-005056805C06 ║
║ DAA1905A-ADBE-E911-9440-005056805C06 ║
║ DBA1905A-ADBE-E911-9440-005056805C06 ║
║ DCA1905A-ADBE-E911-9440-005056805C06 ║
║ DDA1905A-ADBE-E911-9440-005056805C06 ║
║ DEA1905A-ADBE-E911-9440-005056805C06 ║
║ DFA1905A-ADBE-E911-9440-005056805C06 ║
║ E0A1905A-ADBE-E911-9440-005056805C06 ║
║ E1A1905A-ADBE-E911-9440-005056805C06 ║
║ E2A1905A-ADBE-E911-9440-005056805C06 ║
╚══════════════════════════════════════╝

In fact, in situations where good insert performance is desirable, it may be more effective to use sequential GUIDs instead of randomly generated ones. Sequential GUIDs make great keys since they are monotonically increasing, which reduces unnecessary page splits when inserting rows into the index.

Related Question