justin justin - 11 months ago 48
SQL Question

In Sql Server is the object_id of an object subject to change?

I need to know if in a database in Sql Server 2008 (would also want to know what the difference is there for 2005 and 2000 then) would have a changed object_id for a table for any reason.

Ex. does altering a table change that id by dropping and creating and under what circumstances it chooses to alter a table that way if any as opposed to any other way of altering a table.

I haven't been able to find anything on the web that's very concrete so to speak, like a white paper or msdn article. I found just this conversation: http://sql-server-performance.com/Community/forums/p/5918/167487.aspx

At first the conversation is basically "no" and then it seems to do a 180. I'm also looking for a legitimate citation for any answer. I'm not that interested in experimentation in front of a real Sql Server design tidbit in case there are strange circumstances in which the behavior changes. For example, maybe it drops and recreates to change a table but only if it is empty; but if it has data it will not change id... etc. I can see how someone might come up with an answer that is seemingly correct but incomplete and not the whole story by doing that.

Apologies in advance for the long winded, possibly insufficiently descriptive, and picky nature of this question.

Answer Source

The object_id is allocated at the time of object creation. The only time it will change is if the object is recreated.

Some of the things that SSMS does behind the scenes actually involve dropping and recreating tables (in some cases, possibly even without having to). If you add a column to the middle of a table for example, it needs to drop and recreate - hence a new object_id.

In any case, you should never persist an object_id from the database anywhere, just query it again each time using object_id([objectname])