Take the 2-minute tour ×

I have a column with data type nvarchar(max) and I wold like to index this. However, since it's too big it's not possible. So I figured I could create a persisted computed column based on that column with the formula:


However this column also gets a data type of nvarchar(max) so I can't create an index for it. Is it possible to index it somehow without using a full-text index?

share|improve this question
An index on CHECKSUM(YourColumn) might better fit your needs. –  A-K Mar 15 '12 at 13:35

2 Answers 2

up vote 7 down vote accepted

Just do a simple cast in your computed column:

create table YourTable
    -- your other columns....
    YourCompCol as cast(left(isnull(fieldValue, ''), 500) as nvarchar(500))

create index IX_CompCol
on YourTable(YourCompCol)

Nota Bene

The maximum key length is 900 bytes, and nvarchar(500) has a maximum length of 1000 bytes. In other words, an INSERT or an UPDATE could potentially fail.

share|improve this answer

You won't be able to index any more than 900 bytes, so with NVARCHAR, that means the first 450 characters. The important point is that using LEFT on its own does not set the destination data type, you need to do that explicitly using CAST or CONVERT, e.g.

ALTER TABLE dbo.Whatever ADD ComputedColumnName 
share|improve this answer
Why persisted? –  Martin Smith Mar 15 '12 at 13:21
@MartinSmith It sounded like the purpose was to index it. I don't recall ever wanting to index a computed column but not persist it. Also, the OP specifically said they wanted it persisted. Why would I NOT want to persist it in this case? –  Aaron Bertrand Mar 15 '12 at 13:23
persisted is not a requirement to index it and it will just bloat the data page IMO by increasing the length of each row by up to 900 bytes. –  Martin Smith Mar 15 '12 at 13:23
@MartinSmith noted. –  Aaron Bertrand Mar 15 '12 at 13:25

Your Answer


