I've been looking over the database schema and I couldn't help but notice that all PK/FK's are all varchar(36) fields. Can anyone help me understand why you would store GUID's into a varchar(36) field instead of using the purpose built uniqueidentifier datatype?


I've been racking my brain for a few hours trying to think of a good reason why you would want to incur the added overhead and double the storage requirements of using the varchar datatype over a uniqueidentifier... Hopefully I'm overlooking something.



Any insight would be much appreciated.

Kim,

pbeaul is referring to the new sql 2005 datatype, "uniqueindentifier", which basically acts like and indentity in the sense that it is autogenerated, but instead of generating an int it generates a Guid for you.

pbeaul,

Based on what I have seen, the reason for this may be do to the way they designed the BVC2004 to BVC5 migration tool. While I don't agree with their choice on the way the tool was designed, it did/does simplify the migration code. Essentially, if you create a new product in bvc5, it assigns it a guid and stores the "sku" in a seperate field. The migration tool, for whatever reasons, imports a bvc2004 product by the sku and stores that sku in both the sku field and the field where the current guid for the product is stored. Why they did it this way, I don't know.

Apart from that, the only other reasoning I can think of for their design choice, is there is a chance that at the beginning of development they were contemplating support for additional dbs other than sql 2005 (2000 maybe??) and by designing putting the generation of the guid in the .net code, the software would not be dependent on sql 2005.

Most likely the reason is option #2.

[quote="bvuser"]
Kim,

pbeaul is referring to the new sql 2005 datatype, "uniqueindentifier", which basically acts like and indentity in the sense that it is autogenerated, but instead of generating an int it generates a Guid for you.
Actually, UniqueIdentifier have been around since MS SQL 7.0, they did add a few new GUID functions/features to MS SQL 2005 but the datatype itself is not new.

[quote="bvuser"]

pbeaul,


Based on what I have seen, the reason for this may be do to the way they designed the BVC2004 to BVC5 migration tool. While I don't agree with their choice on the way the tool was designed, it did/does simplify the migration code. Essentially, if you create a new product in bvc5, it assigns it a guid and stores the "sku" in a seperate field. The migration tool, for whatever reasons, imports a bvc2004 product by the sku and stores that sku in both the sku field and the field where the current guid for the product is stored. Why they did it this way, I don't know.

Apart from that, the only other reasoning I can think of for their design choice, is there is a chance that at the beginning of development they were contemplating support for additional dbs other than sql 2005 (2000 maybe??) and by designing putting the generation of the guid in the .net code, the software would not be dependent on sql 2005.

Most likely the reason is option #2.



UniqueIdentifier fields by default do not automatically assign themselves a GUID value (like Identity int fields would). So the developer must still generate a GUID and pass it to the DB... instead of passing SomeGuid.ToString() you pass the GUID object itself.



I remember reading about a test repository somewhere on this site and I was wondering if anyone could point me to it. I've looked around my local files but I wasn't able to find it... I'm fairly certain this would cause a moderate performance hit, specially on where clauses and join's...



I was all but sold on BVC5 until I started digging in the DB, it leaves a little to be desired... Hopefully my performance assumtions are all wrong because I could live with inefficient key storage.