Automatic index on a table

Question

I was trying to find out if any index is created on a table, automatically. I used this link to find indexes on Employees table which has 4 columns, namely empid, empName, salary and dept. The query shows that table already has an index - I don't have keys on the table. This link says that non-clustered indexes are not created automatically on a table. So what is the type of index that is already present on Employees table ? Also, index name is shown as null.

Create statement and query used to find indexes on Employees table are as follows :

 Create table Employees( 
      empid int,
      empName varchar(100),
      salary int,
      dept varchar(10)
    )

   select * from sys.indexes
   where object_id = (select object_id 
                     from sys.objects where name = 'Employees')

A primary key would add a unique index, to enforce the uniqueness. empid is probably a primary key — LukStorms, Aug 19 '18 at 09:00
I don't have primary key on the table. If it is of any help, index has been created on heap. — Kanu Priya, Aug 19 '18 at 09:03
Can you share the create statement of the table, as well as the actual query you've used to find the indexes? — Zohar Peled, Aug 19 '18 at 09:15
Added the queries. Also if it is of any help, the object explorer in SQL Server Management Studio, doesn't show any index on the table. — Kanu Priya, Aug 19 '18 at 09:31
Yes, but second query throws a row from sys.indexes table, when I try to find index on the table. Is the query showing index - Index name is null ? — Kanu Priya, Aug 19 '18 at 09:42
This should also mean that no indexes are created automatically on a table ? — Kanu Priya, Aug 19 '18 at 09:45
It's not the name of index - it's the name of *object* (field `object_id`), i.e. your database name. No index is created automatically until you tell so. Be aware that starting from SQL Server 2014 you can create inline indexes. — JohnyL, Aug 19 '18 at 09:50
What is the value of `type_desc` in your query results? I would expect that to be `HEAP`, which is the table itself because it has no clustered index. It's not really an index even though it's exposed in the `sys.indexes` catalog view. — Dan Guzman, Aug 19 '18 at 11:02

score 2 · Accepted Answer · answered Aug 19 '18 at 11:40

This is the output from the sys.indexes query in your question:

+------------+------+----------+------+-----------+-----------+---------------+----------------+----------------+----------------------+-------------+-----------+-------------+-----------------+----------------------------+-----------------+------------------+------------+-------------------+-------------------+---------------------------+--------------+
| object_id  | name | index_id | type | type_desc | is_unique | data_space_id | ignore_dup_key | is_primary_key | is_unique_constraint | fill_factor | is_padded | is_disabled | is_hypothetical | is_ignored_in_optimization | allow_row_locks | allow_page_locks | has_filter | filter_definition | compression_delay | suppress_dup_key_messages | auto_created |
+------------+------+----------+------+-----------+-----------+---------------+----------------+----------------+----------------------+-------------+-----------+-------------+-----------------+----------------------------+-----------------+------------------+------------+-------------------+-------------------+---------------------------+--------------+
| 1746105261 | NULL |        0 |    0 | HEAP      |         0 |             1 |              0 |              0 |                    0 |           0 |         0 |           0 |               0 |                          0 |               1 |                1 |          0 | NULL              | NULL              |                         0 |            0 |
+------------+------+----------+------+-----------+-----------+---------------+----------------+----------------+----------------------+-------------+-----------+-------------+-----------------+----------------------------+-----------------+------------------+------------+-------------------+-------------------+---------------------------+--------------+

Note the type_desc value is "HEAP", meaning a table without a clustered index. So the returned row is the table itself rather than an index. The documentation for sys.indexes states (my emphasis):

Contains a row per index or heap of a tabular object, such as a table, view, or table-valued function

Automatic index on a table

1 Answers1