Varchar index on MS SQL Server 2005

Question

I need to index the varchar field in my table in MS SQL Server 2005, but it is not clear to me how to do this. If I try to add a non-clustered index to the field, it says: "The column" xxxx "in the table" mytable "has a type that is not valid for use as a key column in the index"

My table has an int identifier with an automatic increment, which is set as the primary key in the table. If I set this property as an index and then add my varchar column as an "included column", the index passes. But I'm not sure what I want - I want to be able to search for a table based only on the varchar field, and my understanding of indexes was that all indexed elements had to be provided in order to actually see the acceleration in the query, but I don't want include the int identifier (because I do not know what it is at the time of this request).

Am I trying to do it wrong? Will my varchar id + as an included column do what I'm looking for?

Answer 1

Is your varchar(max) ? I think they cannot be used in the index.

Otherwise, send your CREATE TABLE statement, usually there is no problem adding varchar to the index.

Answer 2

I assume yours is the VARCHAR (MAX) column, which the error says is the invalida data type for the index. Suggestion: create a computed column that is the hash value of the VARCHAR (MAX) column (for example, using the HashBytes function), then create an index only for the computed column. Then, in the search condition (for example, the WHERE clause) of your SQL DML, you must use both the VARCHAR (MAX) value and the hash of your VARCHAR (MAX) value in the corresponding columns in the table. It might be a good idea to encapsulate the hash of search values ​​in a helper stored procedure.

Answer 3

No, the ID + varchar column will not work. This is great for queries in which you are viewing the identifier, and just select the identifier and / or varchar column - then you will have a coverage index, and everything can be restored only by viewing the index.

I assume that you have a clustered index in the ID column as the primary key. Then you will need to create a non-clustered index in the varchar column, which should be possible. A non-clustered index also automatically includes an identifier.

Also remember that the index will only be useful for queries such as WHERE VarcharColumn = 'xyz' and WHERE VarcharColumn LIKE 'xyz%'.

This will not help for LIKE queries "% xyz%" and "% xyz".

Answer 4

Setting the column as the primary key by default creates a clustered index, so you do not need to create another index INT + VARCHAR.

What you are looking for is an index only for your VARCHAR - without + INT, since your primary key is implicitly included - after SQL Server can find the actual row when doing an index lookup. However, there is a limitation, I believe that the total size of the index columns should be <900 bytes (at least that was with SQL Server 2000). How long is your VARCHAR?

Answer 5

You do not need to include the varchar field in the primary key to index it. To create an index, simply edit the table in Management Studio, click the "Manage Indexes and Keys" button, and click "Add" to add a new index. Then select the VARCHAR field. There should be no problem.