how to fake an objectid in sql server
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:
- ColdFusion Vs. SQL UUID by Rob Gonda
- ColdFusion UUID and MSSQL newID() by Michael Sharman
Enjoy!
Comments
Claude on 16 Aug 2008 01:50 AM
Why don't you just use the SQL GUID in your app? GUID is a valid data type in CF.
Geoff Bowers on 16 Aug 2008 02:47 AM
We need to support mySQL, Postgres and Oracle.
Jake Munson on 16 Aug 2008 04:39 AM
"its the most flexible primary key you'll find" One problem with UUID primary keys is that they're slower than integers. Any kind of text field is a lot slower than integers, in fact. But it only matters if you have huge amounts of data. But I have to ask...what's wrong with a plain 'ol incrementing integer primary key?
Geoff Bowers on 16 Aug 2008 12:27 PM
Nothing wrong with the old integer, but it doesn't work for us. The FarCry framework treats each record as an object that can be acted on regardless of its origin in the model, so we need to guarantee a unique id across all tables. UUIDs are a good trade off.
Jason Barnes on 01 Sep 2008 07:56 PM
In a database of less than a million rows the performance difference between an int pk and varchar(35) is negligble. Also I know the above solution is the combination of two ms sql uid's, and there is an argument for creating a udf to do it as a single uid and simply string manipulating it, however I would be curious to know the probability of that combination ever actually occurring more than once in the same database :P. It'd be worth it happening just to know it actually happened (and to prompt you to buy a lottery ticket)
Frank Brea on 28 Oct 2008 10:36 AM
I never knew you could do this awesome