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

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.

Answer 3

You don't need to include the varchar field in the primary key for it to be indexed. To create an index just modify the table in Management Studio click on the Manage Indexes and Keys button and click Add to add a new index. Then select the VARCHAR field. There should be no problem.

Answer 4

Setting a column as primary key per default creates a clustered index, so you don't have to create another INT+VARCHAR index.

What you're looking for is an index on your VARCHAR alone - without +INT, since your primary key is implicitly included - after all SQL Server should be able to locate the actual row when performing index lookups. There is a restriction though, I believe the overall size of index columns should be < 900 byte (at least it was with SQL Server 2000). How long is your VARCHAR?

Answer 5

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.