Sql-server – Indexes with the same object_id but different names

indexsql servert-sql

When I run

SELECT
  count(object_id) AS count,
  object_id,
  min(name) as name1,
  max(name) AS name2
FROM
  sys.indexes
GROUP BY object_id HAVING COUNT(*) > 1 ORDER BY count

I will get a list of indexes having the same object_id, but not necessarily with the same name.

Should not all indexes with the same object_id have the same name?

Best Answer

The Object_ID belongs to the object the index belongs to, such as an indexed view or a table.

When you look at the object_id in the sys.indexes documentation

object_id - ID of the object to which this index belongs.

If you want to uniquely identify the index you need the Object_ID + index_id columns or the index_id for a specific object_id.

To verify this, you can add the OBJECT_NAME() function to your query

SELECT
  count(object_id) AS count,
  object_id,
  OBJECT_NAME(object_id),
  min(name) as name1,
  max(name) AS name2
FROM
  sys.indexes
GROUP BY object_id HAVING COUNT(*) > 1 ORDER BY count;
Related Question