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)

How long is the string column? What is the actual error? What is the purpose of the index, to satisfy seeks better? — Aaron Bertrand
– Aaron Bertrand, Commented Feb 26, 2012 at 18:00
Any index in SQL Server can be a maximum of 900 bytes per index entry - if your string column is longer than that, you cannot index it. Show us your exact, complete table structure! — marc_s
– marc_s, Commented Feb 26, 2012 at 18:00

Aaron Bertrand · Accepted Answer · 2012-02-26 18:02:52Z

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).

Tomek · Accepted Answer · 2012-02-26 18:18:14Z

5

A regular index can't be created on ntext or text columns (i guess your name column is of that type, or (n)varchar longer than 900 bytes). You can create full-text index on that column type.

edited Feb 26, 2012 at 18:18

answered Feb 26, 2012 at 18:07

Tomek

3,2792 gold badges24 silver badges23 bronze badges

1 Comment

Aaron Bertrand Over a year ago

I'm not sure why you think "name" must be using a deprecated data type. Also have you tried creating an index on a varchar(max) column? A hint: this is not a valid index. You can add a varchar(max) column as an INCLUDE column, but this is rarely wise and won't help the search anyway.

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related