Index varchar on MS SQL Server 2005

Question

I need to index a varchar field on my table in MS SQL Server 2005, but it's not clear to me how to do so. If I try to add a non-clustered index on the field, it says "Column 'xxxx' in table 'mytable' is of a type that is invalid for use as a key column in an index"

My table has an auto-increment int ID that is set as the primary key on the table. If I set this property as the index, and then add my varchar column as an "included column", the index goes through. But I'm not sure that's what I want - I want to be able to search the table based on the varchar field alone, and my understanding of indexes was that all indexed elements had to be provided to actually see a speedup in the query, but I don't want to have to included the int ID (because I don't know what it is, at the time of this given query).

Am I trying to do this incorrectly? Would the ID + my varchar as an included column accomplish what I am looking for?

Answer 1

Is your varchar(max)? I think those aren't allowed to be used in an index.

Otherwise, post your CREATE TABLE statement, normally there is no problem adding a varchar to an index.

Answer 2

I assume yours is a VARCHAR(MAX) column which, as the error says, is an invalida data type for an index. Suggestion: create a calculated column that is the hash value of the VARCHAR(MAX) column (e.g. using the HashBytes function) then create an index on the calculated column only. Then, in the search condition (e.g. WHERE clause) of your SQL DML you would use both the VARCHAR(MAX) search value itself plus a hash of your VARCHAR(MAX) search value on the respective columns in your table. It may be a good idea to encapsulate the hashing of search values in a 'helper' stored procedure.

Answer 3

No, the ID + varchar column would not work. That would work great for queries where you do a lookup on the ID and only select the ID or/and the varchar column - then you'd have a covering index and everything could be retrieved only by looking at the index.

I'm guessing you have a clustered index on your ID column as that's the primary key. Then you'd need to create a nonclustered index on the varchar column - which should be possible. The nonclustered index will automatically include the ID as well.

Also remember that the index will only be good for queryes like WHERE VarcharColumn = 'xyz' and WHERE VarcharColumn LIKE 'xyz%'.

It won't help for LIKE '%xyz%' and '%xyz' queries.