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 concret开发者_运维百科e 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.

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])

one way the object_Id might be persisted, unwittingly, is if some "clever" programmer has created an auditTrail table that uses objectids instead of table names (you decide if this is a clever idea or not). When data changes, the object_id() and identity columns are stored.

The question above, which I believe has not yet been answered, is whether that object_id value will in fact persist across things like restoring the database to another server, upgrading sql server versions, fail-over of database across different cluster hosts, etc.

whether it is a good idea to try using a persisted value of the object_id is another question (which I believe has been answered above with "NO; not a good idea"). If SSMS drops and recreates tables behind the scenes, that right there is sufficient reason to NOT use object_id

The object_id will change as SQL Server recreates tables internally. Instead (or in addition), store the database name.