In Sql Server - object_id of object to be modified?

Question

I need to know if there is a database in Sql Server 2008 (I would also like to know what the difference is in 2005 and 2000), for some reason I would change the object_id for the table.

Ex. changes the table change to an identifier, discarding and creating, and under what circumstances he decides to change the table in such a way, if any, unlike any other way to change the table.

I could not find anything on the Internet that was very specific, so to speak, like a white article or an msdn article. I found exactly 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 180. I am also looking for a legitimate quote for any answer. I'm not interested in an experiment in front of a realistic Sql Server design if there are strange circumstances in which the behavior changes. For example, perhaps it crashes and is recreated to change the table, but only if it is empty; but if it has data, it will not change the identifier ... etc. I see how someone can come up with an answer that seems plausible, but incomplete, and not the whole story, by doing this.

Sorry in advance for the long, perhaps insufficiently descriptive and picky nature of this issue.

Answer 1

Object_id is allocated during object creation. The only time it changes is if the object is recreated.

Some of the things that SSMS does behind the scenes are actually about dropping and re-creating tables (in some cases, perhaps even unnecessarily). If you add a column to the middle of the table, for example, it needs to be discarded and recreated - hence the new object_id.

In any case, you should never permanently stop object_id from the database anywhere, just request it every time using object_id([objectname])

Answer 2

At one end, the object_Id object can be saved, involuntarily, if some "smart" programmer created an auditTrail table that uses object names instead of table names (you decide whether this is a smart idea or not). When data is changed, object_id () columns and identifiers are stored.

The question, which, it seems to me, has not yet been answered, is whether this object_id value will actually be stored in such things as restoring a database to another server, updating SQL Server versions, overflowing the database on different nodes of the cluster, and etc.

Is it possible to try to use the stored value of object_id, this is another question (which, it seems to me, was answered with the inscription "NO, not a good idea"). If SSMS rolls and recreates tables behind the scenes, this right is reason enough to NOT use object_id

Answer 3

Id_id will change as SQL Server recreates the tables inside. Instead (or optionally) save the database name.