Is there a need to index the short string code for a faster query?
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.