Are GUIDs a viable substitute for integer Identities/Primary Keys?

Hi arpost I've been rolling this around my head for a few days, I think with ROW_NUMBER it won't be possible (as you've found) to fully parallelise loading.  I did think about a control table where a load could insert a "start" and "end" value, where start is the last end value inserted + 1.  Then "end" for that partcular batch would be the rowcount that would be inserted into the destination table.  Then ROW_NUMBER could be used with start.  Problem is you'd never know if 2 loads hit the table at the same time and grabbed the same "end" number.

I've been testing GUIDs and a NEWID value converted to varchar takes up 36 bytes, rather than int which takes up 4 bytes (2 in my screenshot as i'm using a smallint).  If a SELECT NEWID (newsequentialid not supported) is inserted into a varchar column, then direct lake would work.  In terms of space, this obviously takes up more space but the overall footprint would depend on how unique the dimensions are.  E.G I added a NEWID to a dimension table with 400 rows in and it increated the underlying parquet file size of the dimension from 11KB to 25KB (hardly anything to write home about).  I then tested with a fact table (1.1 billion rows) and added the newid value from the dimension table, it took the overall fact table (which is just a bunch of INTs) from 16MB to 24MB.  Again nothing to massive.

So I guess it's going to be a case of seeing whether using NEWID in a varchar column is going to have much of a size and performance impact with your data.  INTs will always be faster, but perhaps in your case it may be neglible performance.

I can't see IDENTITY/SEQUENCE on the roadmap for Fabric Warehouses either, but it's a requested feature so I can see it being available at some point in 2024/25.

What's new and planned for Synapse Data Warehouse in Microsoft Fabric - Microsoft Fabric | Microsoft Learn