Which is the uniqueness level of object_id in SQL Server? I mean, is this value unique per database, per instance, per server,... Also, what happens when I restore the database in another server or instance? And what happens with the object_id if I create the database from a script instead of a backup?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
The object_id column is unique per database. Two objects in separate databases can have the same object_id, however separate objects in the same database have always different object_id values.
Every time you drop and create an object, a new object_id value is assigned automatically and there is no way to influence which value is chosen. However, if you alter an object using the
Because there is no way to influence the value chosen for object_id, there is also no way to script out a database in an object_id preserving fashion, so if you re-create a database using a t-sql script there is no correlation between the previous object_id values and the new ones.
A database backup on the other hand copies the actual data pages one to one, so object_id values will stay unchanged.
In general it is a good idea to stay away from the object_id value in a production system. You cannot use it to detect a change nor a no-change situation. The only thing it should be used for is to filter or join system tables and DMVs. You can also use it to conditionally drop an object like this: