How to create an index for a string column in sql?

Question

I have a table with 3 columns: a list id, name and numeric value. The goal is to use the table to retrieve and update the numeric value for a name in various lists.

The problem is that sql refuses to create an index with the name column because it's a string column with variable length.

Without an index selecting with a name will be inefficient, and the option of using a static length text column will be wasting a lot of storage space as names can be fairly long.

What is the best way to build this table and it's indexes?

(running sql server 2008)

Answer 1

If your string is longer than 900 bytes, then it can't be an index key, regardless of whether it is variable or fixed length.

One idea would be to at least make seeks more selective by adding a computed column. e.g.

CREATE TABLE dbo.Strings
(
  -- other columns,
  WholeString VARCHAR(4000),
  Substring AS (CONVERT(VARCHAR(10), WholeString) PERSISTED
);
CREATE INDEX ss ON dbo.Strings(Substring);

Now when searching for a row to update, you can say:

WHERE s.Substring = LEFT(@string, 10)
AND s.WholeString = @string;

This will at least help the optimizer narrow its search down to the index pages where the exact match is most likely to live. You may want to experiment with that length as it depends on how many similar strings you have and what will best help the optimizer weed out a single page. You may also want to experiment with including some or all of the other columns in the ss index, with or without using the INCLUDE clause (whether this is useful will vary greatly on various factors such as what else your update query does, read/write ratio, etc).