Create Index on partial CHAR Column

Question

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?

Answer 1

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

Answer 2

I hope you have a good relational reason for doing this. I'm guessing the first 24 characters of the vendor-provided table actually constitute a discrete attribute and should have been in a separate column in the first place.

So...

Create a view of the vendor's table. Index it if you like. I doubt you can point a FK constraint at the view, but you certainly can write a trigger to the same effect. A trigger checking against an indexed view will be very fast, at the cost of a slight increase in update times on the view's base table.

HTH.