Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Can you explain why you care if the object_id values stay the same or change depending on how a copy of the database is created? You should treat these like IDENTITY values - meaningless surrogates used by the system that you shouldn't know or care about. –  Aaron Bertrand Jul 21 '13 at 14:21
    
I need the shortest value to unique identify the columns and tables of the same database amongst many computers. That's why I was interested in a number like the object_id, instead of the names, but I've seen cases where the id was modified, so I wasn't sure to use it. –  Guillermo Gutiérrez Jul 22 '13 at 7:14
    
And now I know I shouldn't use it... –  Guillermo Gutiérrez Jul 22 '13 at 7:15

1 Answer 1

up vote 9 down vote accepted

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 ALTER statement, the object_id stays the same.

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: IF(OBJECT_ID('dbo.objectname') IS NOT NULL) DROP... For anything else you should consider that value as off limits.

share|improve this answer
    
To add to Sebastian's last comment, there are many functions that take object id as parameters; for those you use the OBJECT_ID() function. For example, OBJECTPROPERTYEX( OBJECT_ID('Production.Product'), 'TableHasPrimaryKey' ) –  Greenstone Walker Jul 19 '13 at 3:17

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.