TSQL indexes in search fields, should I include an identifier
Let's say I have a typical customer table;
Id Int Identity Primary Key,
FirstName varchar(255),
LastName varchar(255),
Phone VarChar(30)
Thus, the primary key creates a clustered unique index on Id.
To speed up searches, I want to add indexes to the name fields.
My question is whether I should add an id to these indexes or should all of these indexes bind to the primary key.
For example:
Create Index IX_Customer_FirstLastName On Customers(FirstName, LastName, Id)
... is that overkill?
Is the following more or less effective?
Create Index IX_Customer_FirstLastName On Customers(FirstName, LastName)
TIA
source to share
It doesn't make sense to add ID
to your other index. It would be helpful if you had a large number of lines with the same combinations firstName
lastName
and you already knew ID
...
But if you already knew ID
for the records you are looking for, you can simply search using your clustered index.
In addition, each nonclustered index already contains a value for the clustered index so that it can search for a row using the clustered index after it has been found using the nonclustered index. You do not need to include ID
in your non-clustered index, if you want to make it to the closing index fields ( firstName
, lastName
, ID
)
Final note: if you create a single index with firstName
and lastName
, that index will only be used for searches that include the firstName
leading edge of your index. If you plan to perform searches using only lastName
or simply firstName
, create two indexes, one for each column.
source to share