RFC: My BSON ObjectId in T-SQL

[Justin Dearing]

http://github.com/zippy1981/BSON-ObjectId.SQL

I have a user defined function that mostly implements a BSON ObjectId.
I have not figured out how to bitshift anything bigger than an 8 byte
integer in T-SQL, so I had to do casting and string manipulation to do
it. Due to these inefficiencies I assume implementing an ObjectId as a
CLR type by compiling the ObjectId C++ class as a managed DLL will
lead to better performance.

My practical goals are twofold. First I want to test thinkgs like Id
Colissions, and if ObjetId makes sense in a databsase for performance
reasons. Second, in an SOA system it would make sense to be able to
have one SQL backed web service that stores external keys that
reference mongo documents retrieved through another service. There are
of course other ways to solve this problem but I want to try this way.

I'm looking for any constructive feedback, but specifically:

1. A way to get the machine id from SQL
2. Efficiencies in the algorithm.
3, Recommendations for implementing the sequence section
4. signatures for other UDFs to implement.

Regards,

Justin Dearing

[dwight_10gen]

i don't know answers to your questiosn (haven't used TSQL a lot) but i
think it is a neat idea to gen the oid's in SQL. i would probably
lean to just storing them as hex Char(24) in the db which would keep
it nice and simple and easy to read the output.

[Justin Dearing]

As  far as "nice to read," binary(12) outputs to "0xnnnnnnnnnnnnnnnnnnnnnnnn" in SQL management studio, and I can use CAST() or CONVERT() if I need a string in a language that does not not output nicely. Also, my attraction to ObjectIs over GUIDs is they are 4 bytes smaller. Thats a good argument for using them as a primary key in SQL server. Using chars makes them 8 bytes bigger.

All this of course begs the question, why inspired you guys to reinvent the GUID wheel in the first place?

Justin

[dwight_10gen]

if it prints that nicely, that makes sense. didn't know that.

we wanted to make it 12 bytes instead of 16 that is why

one option for you if you want to interoperate is to use GUIDs and
store them as BSON BinData type? if tsql has GUID support that would
be an option?

another thought : if you knew which GUID generator is used with TSQL,
you might be able to gen one once and extract a machine id that way...

[Justin Dearing]

FYI As a .NET CLR User defined type as well now.


http://github.com/zippy1981/BSON-ObjectId.SQL/tree/master/dotNET/BSON-Sharp/

[Justin Dearing]

Ok in the pure T-SQL implementation I now fill up most of the sequence bits with the milliseconds from the timestamp. I think I can go a little more crazier with T-SQL, and fill up all the bits. 

http://github.com/zippy1981/BSON-ObjectId.SQL/commit/fa0c02080242cbb41249485efec567487ed9547b

Justin