Object IDs: Unique Instance-Wide or Database-Wide?

, ,

The versatility of T-SQL’s object_id() function, which accepts one-part (object_name), two-part (schema.object_name) and three-part (database.schema.object_name) object names as its first argument, could seem to imply that object identification numbers (object IDs) are global across a Microsoft SQL Server instance. After all, if object_id() accepts a multi-part object name which uniquely identifies the specified object relative to the server instance, wouldn’t the object ID returned by that function also uniquely identify the same object across the server instance?

While this train of thought is logical, it’s also inaccurate—and that’s a good thing. If object IDs were unique instance-wide, object IDs wouldn’t be static. Suppose database ABC from Instance1 is restored on Instance2. Instance2 already hosts another database which uses object IDs that conflict with those used by database ABC. The restore process would need to resolve this conflict by reassigning object IDs. Object IDs in either ABC or the existing database would be changed. Can you imagine the confusion this would cause?

Instead, object identification numbers—object IDs—are “unique within a database” (MSDN reference).  An object ID is scoped to a database and always needs to be interpreted in that context.

The object_id() function parses the passed-in object name string to determine the applicable database where it then looks up the relevant object by its name. To correctly interpret the returned ID, we need to know the associated database. We use the same arbitrary one-to-three part object_name string with parsename() and db_id() to obtain this information.

DECLARE @Name SYSNAME = 'TestDatabase.dbo.SomeTable';

SELECT DB_ID = COALESCE(DB_ID(PARSENAME(@Name, 3)), DB_ID());
SELECT OBJECT_ID = OBJECT_ID(@Name);

Note: If an object name does not contain a database name (e.g. if @Name = ‘MyTable’), it is to be interpreted in the context of the local database. parsename() returns NULL when this is the case and when there is an argument error. We assume that NULL indicates former and so use coalesce() to replace NULL with the current database ID. If catching argument errors is important, we can check for them by examining object_id()’s return value.

Leave a Reply

Your email address will not be published. Required fields are marked *