4

Is it safe to use OBJECT_ID() as a reference in your own tables, with the assumption that the value will always refer to the same database object no matter what?

The msdn states

All metadata functions are nondeterministic. This means these functions do not always return the same results every time they are called, even with the same set of input values.

so that would indicate it's not safe, but I couldn't find any (other) hard facts that it shouldn't be done, and, to be fair, no examples of people actually doing it, which in itself might be evidence not to do it.

But the value OBJECT_ID() is (or seems to be) used in the system tables and views as keys from one object to another, so there must be some form of integrity and reliability.

For the record: I don't necessarily need it, but it just popped into my mind as an option while designing my system, and got curious.

11

Do not rely on object_id for your application.

My experience is that once an object is created its object_id will be stable for as long as that object exists. The difficulty is in knowing the meaning of "as long as it exists."

If the object is dropped and re-created it will get a new object_id, even if the exact same statement is used both times, creating an object with the same name.

If the creation script runs against different databases (say DEV and TEST) it will generate different object_ids.

If the database is restored old object_id values may re-appear.

Most risky is that stability of object_ids is not documented and therefore is not guaranteed. A patch, upgrade, security patch or anything else may change IDs for existing objects if MS determines this is something they want to do for any reason whatsoever at all.

MS can use object_id in their internal metadata because they know all the places that can invalidate these values and can write their code accordingly. They do not publish these details for us, as application programmers, to consider.

If you need to reference objects in a data-driven way, store their names. Use the correct datatype (SYSNAME) and don't forget to store the object's schema, too. If a numeric value must be passed around there are some options. An identity column could be added. Values will be different between environments and when objects are removed and re-added. Really this is no better than object_id. Better may be to calculate a hash of the schema and object names. If the hash algorithm used is communicated broadly every consumer should be able to generate it on demand. If you have authority over the objects' names (which seems likely since you're writing them) you could simply choose the corresponding ids and publish them. Don't forget the unique constraint on the name and id columns!

| improve this answer | |
  • Yeah, that's what I thought, and was hesitant enough to first ask, thanks. In essence, the target is to switch out an nvarchar(30) (representing a unique table) for an integer, the performance improvements are significant. But with some minor scaffolding, it's easy to provide my own variation of "object_id()", that both sides can refer to, and is stable. Just wanted to be sure, before reinventing the wheel, and avoid having this nagging little doubt for the rest of the applications' lifetime ;) – Willem van Rumpt Nov 22 at 12:25
  • 2
    I'd recommend using SYSNAME or the equivalent NVARCHAR(128) when referring to object name if you intend to keep any of that logic around. – LowlyDBA - John McCall Nov 22 at 17:58
  • 1
    Using the sysname type for columns that denote object names, column names, etc is desirable since the underlying definition of a sysname may change at Microsoft's whim. Although it is currently synonymous with an nvarchar(128), that might change in future, and using sysname would allow any newly created table to inherit the then-current definition. – Max Vernon Nov 23 at 13:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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