September 23, 2004 at 11:57 am
I've got a Data Warehouse team that's decided to use the database object ids to keep track of their SourceSafe SQL server code.
I remember a hideous application way back in the 7.0 early days that did just that, and it caused us all kinds of grief. You can never drop an object. You have to always remember to use Alter. Anyone have any thoughts on this? If you think it's a great idea, let me know why. And if you think it's not a good thing, please let me know that as well.
September 23, 2004 at 1:03 pm
Sounds like a bad idea to me. The ObjectID, as I understand it, is not guaranteed to be unique over time. In other words, a given ObjectID will be unique at any given point in time, however, if an object is dropped and then a new object is created, SQL Server might assign the ObjectID of the previously dropped object to the new one. This causes major problems in a warehouse situation were by definition, you want to track things over time. The same of course applies to the object names so in that case neither would be a good candidate for a primary key in the warehouse. They would both be potentially valuable attributes to store, but not as keys, unless you also included a set of temporal designation columns to the key. But since current products, SQL Server included, don't offer any true temporal functionality, you probably need to just go with a surrogate key.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
September 23, 2004 at 3:24 pm
I agree - this is a bad idea. You will be restricted from doing a lot of things that you might like to do at some point. Dropping and recreating objects is one thing. Porting to another DBMS is another.
Even upgrading to a new version of MS-SQLServer may pose problems, as I doubt whether Microsoft has any obligation to keep object_id's the same as part of an upgrade process. If any problems arise at all with the object_id, you have no way (as I know of) to set the object_id explicitly and mend the problem.
September 24, 2004 at 7:04 am
Yep, bad idea. You just know SOMEONE's going to forget and drop an object.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply