Is there a need to index the short string code for a faster query?

We have a large number of tables for setup forms.
In each form, there is a need to fill in the Code and the Description.

The Code consists of a varchar(10) datatype.

Now, the thing is, every time a user creates new data or updates the data, there is a need to check whether the Code conflicts with the others or not.

In LINQ, this code checking is inevitable:

string ExistingCode = "ITEM01";
int ExistingKey = 1;

var recordWithConflictedCode = (from a in db.MyTable
                               where a.Code == ExistingCode && 
                               a.Key != ExistingKey).ToList();

if(recordWithConflictedCode.Count >= 1)
    return BadRequest("Duplicated Code found");

Now, since this involves a string comparison on every process, I think it makes sense to index the column Code to make the query faster.

Is this a common practice for you guys?

Use the DBMS for its intended purpose; that is create a constraint on the table so that it fits the business rules.

Designing your table so it looks like:

USE tempdb;
CREATE TABLE dbo.MyTable
(
    [Key] int NOT NULL
    , Code varchar(10) NOT NULL
         CONSTRAINT uq_MyTable_Code
         UNIQUE 
);

This will ensure every row inserted into MyTable has a unique Code column. Now you can add a try ... catch statement around your inserts in .Net code to inform the user when a duplicate is detected.

The SQL Server engine will automatically create a unique index on the table to support the constraint. You could just manually create a unique index on the table to satisfy the same requirements; however adding it as a unique constraint in the way detailed above self-documents the business rule.

StackExchange Database Administrators Q#161699, answer score: 5