What does OBJECT_ID do in SQL Server?

Question

I'm trying out the usage of OBJECT_ID and found the following entry in MSDN:

"Returns the database object identification number of a schema-scoped object."

What is database object identification number and what is schema scoped object. Seems like I'm more confused than initial.

Answer 1

The OBJECT_ID is used to identify the object uniquely in the system base tables.

It is the primary key of the sys.sysschobjs base table used by the sys.objects metadata view and appears in many other of the metadata views. e.g sys.partitions. If you aren't querying these views then the object_id isn't very useful except perhaps when using in a check for object existence as below.

IF OBJECT_ID('T', 'U') IS NULL
     /*Table T does not exist or no permissions*/

A schema scoped object is one that belongs to a schema (e.g a table, view, stored procedure). The MSDN article gives an example of a non schema scoped object in DDL triggers.

It is an error to try and specify a schema when creating these

CREATE TRIGGER dbo.SomeTrigger /*Will Fail*/
ON DATABASE 
FOR DROP_SYNONYM
AS 
PRINT 'SomeTrigger'

Non schema scoped objects metadata is still stored in sys.sysschobjs but does not show up in the sys.objects view.