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?

  • 3
    An index on CHECKSUM(YourColumn) might better fit your needs.
    – A-K
    Mar 15 '12 at 13:35

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.


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 
  • Why persisted? Mar 15 '12 at 13:21
  • 2
    @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? Mar 15 '12 at 13:23
  • 3
    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. Mar 15 '12 at 13:23
  • @MartinSmith noted. Mar 15 '12 at 13:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.