Search code examples
sqlt-sqlsql-server-2008query-tuning

Create Index on partial CHAR Column

I have a CHAR(250) column being used as a foreign key to a varchar(24) column.

In MySQL I recall that I could create an index specifying column(24) in order to create an index on the leftmost 24 characters. This doesn't appear to be possible on MS SQL Server.

My question is this:

Is it possible to use an indexed view on SQL Server 2008 to index a substring of that column, and if so, would it have any side-effects on the table's performance?

Solution

  • You can create a persisted computed column, then index it, see Creating Indexes on Computed Columns

    alter table add newcolumn as cast(oldcolumn as varchar(24)) persisted;
create index table_newcolumn on table (newcolumn);