3

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?

Improve this question

2 Answers 2

Reset to default
7

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);
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Thanks! What I neglected to mention though is that the table in question is part of a 3rd party app, so changing the table itself is strictly verboten.
I'm not sure though that the FK will benefit from the indexed view. Queries and joins may benefit from it, but the FK enforcements itself might ignore your indexed view resulting in poor performance for the FK enforcement validations.
Fair enough, I guess I'll just have to try it out.
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.

Improve this answer

1 Comment

There is no goo relational reason, it's a decision which was made before my time I'm more or less stuck with. There is no actual FK constraint to worry about. It's already in an entirely separate database on the same server. I have a stored procedure which takes 40 minutes to run I'm mainly concerned with. Putting an index on that field just seemed excessively large (~400mb). Disk space isn't really that much of a concern though, so if there aren't any other performance benefits to speak of, I'm just wasting my time.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.