Performance of non-numeric indexes

Question

If I use a nvarchar(n) column as a clustered index on a SQL Server database, am I going to suffer a significant performance hit compared to a numeric (int) index? Also how does the performance of compound indexes compare?

Answer 1

Sql doesn't really care whether your index is numerical or not but there are some things you need to consider depending on what is in the column and how you are using the table.

Generally you want to keep your indexes as small as possible so nvarchar(4000) (upto 8000 bytes) would really suck but a varchar(3) (upto 3 bytes) would be smaller than int (4 bytes). Also you want to (where possible) have your index insert inserted into the end of the index this keeps the index from fragmenting and causing you performance issues.

Compound indexes can greatly improve performance if the queries you are running against the table only contain the columns in the index. This means that the actual table is never even touched as the index satisfies the query.

see Sql server index basics for an overview on indexes.

It might be more helpful if you gave more specific details about the table itself and how you want to use it?