SQL Server - index on a computed column?

Assuming you have your fields in this format:

00Data0007000000Data00110000Data0015

, you can do the following:

• Create a computed column: ndata AS RIGHT(REVERSE(data), LEN(data) - 4)

  This will transform your columns into the following:

  ataD00ataD000000ataD0000

• Create an index on that column

• Issue this query to search for the string Data:

  SELECT  *FROM    mytableWHERE   ndata LIKE N'ataD%'        AND SUBSTRING(ndata, LEN(N'ataD') + 1, LEN(ndata)) = REPLICATE('0', LEN(ndata) - LEN('ataD'))

  The first condition will use an index for coarse filtering.

  The second will make sure that all leading characters (that became the trailing characters in the computed column) are nothing but zeros.

See this entry in my blog for performance detail:

• SQL Server: leading wildcard match using an index

Update

If you just want an index on SUBSTRING without changing your schema, creating a view is an option.

CREATE VIEW v_substring75WITH SCHEMABINDINGASSELECT  s.id, s.data, SUBSTRING(data, 7, 5) AS substring75FROM    mytableCREATE UNIQUE CLUSTERED INDEX UX_substring75_substring_id ON (substring75, id)SELECT  id, dataFROM    v_substring75WHERE   substring75 = '12345'

Add a calculated column to your table and create an index on this column.

ALTER TABLE MyTableAdd Column CodeHead As LEFT(Code,Len(Code)-4)

Then create an index on this.

CREATE INDEX CodeHeadIdx ON MyTable.CodeHead

Can you re-phrase your filter criteria in terms of a LIKE 'something%' statement? (This is applicable to an index)