how to fake an objectid in sql server

written by Geoff Bowers on Friday, 15 August, 2008 @ 01:18 PM

We work with UUIDs a lot. If you have to work with application code bases that are cross platform and support multiple database servers its the most flexible primary key you'll find. But not all universally unique ids are the same. For example, ColdFusion uses an algorithm for generating them that is not the same as MS SQL Server.

If you are working with SQL Server and ColdFusion you may find you want to fake a CF UUID directly in your database. It's real useful if you find yourself migrating data from database schemas that use autonumbers (or similar) to one that uses CF style UUIDs instead (heh, like FarCry framework apps).

left(newid(),23) + right(newid(),12)

Pop that little number in your SQL INSERT statement for the primary key and you should be apples.

Well in truth its a little bit of a hack and the approach is not quite as "unique" as it should be. If you have a lot of records or want to make UUIDs from SQL as a permanent part of your application, you might want to consider a more sophisticated approach.

For those who want to dig deeper: