Menu
I believe when I create an identity column it gets indexed automatically, but I'm not 100% sure.
Should I create an index for an identity column, or is it created automatically?
508
A unique index is automatically created when you define a primary key or unique constraint: Primary key: When you define a primary key constraint on one or more columns, SQL Server automatically creates a unique, clustered index if a clustered index does not already exist on the table or view.
In general, you should create an index on a column in any of the following situations: The column is queried frequently. A referential integrity constraint exists on the column. A UNIQUE key integrity constraint exists on the column.
A Unique index will be created automatically when you define a PRIMARY KEY or UNIQUE KEY constraints on the specified columns.
Primary key columns are typically great for indexing because they are unique and are often used to lookup rows.
create table test (Id int identity) go sp_help test The object 'test' does not have any indexes, or you do not have permissions. No constraints are defined on object 'test', or you do not have permissions.
As a general practice you would create a unique index on your identity column, this speeds up lookups.
Usually you would like your identity columns to be 'clustered indexes' as well (Id int identity primary key is the shortcut notation), meaning table is layed out on disk in the same order your identity column is. This optimizes for inserts, as the page being inserted into tends to be in memory. In some cases, when you are doing ranged lookups very frequently on other data in the table, you may consider clustering other columns instead, as SQL Server only allows you one clustered index per table.
65
If you create a column with a PRIMARY KEY constraint, a clustered index will be created by default (assuming this is a new table and no such index is already defined).
Being an IDENTITY field has nothing to do with it.
So, to answer you question - if you are going to query/sort with this field and it is not a primary key, you should define an index on it (rule of thumb, always test for your scenario).
40
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
