Why do System Catalog Object_ids differ for the same object based on the view?

Question

Tables have been created in our SQL Server 2008 R2 database on a local server, and also setup in our SQL Server Express 2008 that installs with Visual Studio 2010 on development machines, using the following script (edited a bit for brievity):

CREATE TABLE dbo.Projects

    (

    Id UNIQUEIDENTIFIER NOT NULL,

    Name NVARCHAR(64) NOT NULL

    ) ON [PRIMARY]

GO

ALTER TABLE dbo.Projects ADD CONSTRAINT

    PK_Projects PRIMARY KEY CLUSTERED 

    (

    Id

    ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]



GO

CREATE TABLE [dbo].[ProjectProperties]

    (

    Id UNIQUEIDENTIFIER PRIMARY KEY,

    ProjectPath NVARCHAR(MAX) NOT NULL DEFAULT '.',

    )

GO

ALTER TABLE [dbo].[ProjectProperties] ADD CONSTRAINT

    FK_ProjectProperties_Projects FOREIGN KEY

    (

    Id

    ) REFERENCES dbo.Projects

    (

    Id

    ) ON UPDATE NO ACTION 

     ON DELETE CASCADE 

GO

I query the System Catalog via its views using the following scripts with the associated results (object_ids differ for each copy of the database, but the issues are the same):

SELECT object_id, name

FROM sys.indexes AS si

GO

object_id  name
354100302  PK__ProjectP__3214EC0717036CC0

SELECT object_id, name

FROM sys.key_constraints AS kc

GO

object_id  name
402100473  PK__ProjectP__3214EC0717036CC0

SELECT object_id, name

FROM sys.tables AS st

GO

object_id  name
354100302  ProjectProperties

So my questions are as follows?

Why do the object_ids for the Primary Key constraint differ between the sys.indexes and sys.key_constraints views?
Why is the object_id for sys.indexes reporting the object_id for the table, if the object_id reported from sys.tables is assumed to be correct?

I'm still fairly new to SQL (~2yrs), so this may be something that's obvious to most SQL gurus, but I just don't understand it.

Tom H Tom H · Accepted Answer · 2011-05-04T20:27:27

The constraint and index are not the same thing, so they have different object_id values. The index is simply created automatically to implement the constraint. (As a side note, I'd explicitly name the constraint).

The indexes are associated to the table, which is why the object_id is the same. If you added additional indexes to the same table then you would see that they also have the same value, but the index_id column increments.

The parent_object_id in sys.key_constraints will also match your table object_id. I'm not sure why the people at Microsoft decided to use object_id in sys.indexes for the table, but parent_object_id in sys.key_constraints, except perhaps that a constraint is considered an object by itself, while an index is not.

Why do System Catalog Object_ids differ for the same object based on the view?

3 Answers