Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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:

left(isnull([fieldValue],''),500)

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
3  
An index on CHECKSUM(YourColumn) might better fit your needs. –  AlexKuznetsov 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))
)
go

create index IX_CompCol
on YourTable(YourCompCol)
go


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 
  AS CONVERT(NVARCHAR(450), COALESCE([fieldValue],'')) PERSISTED;
share|improve this answer
    
Why persisted? –  Martin Smith 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? –  Aaron Bertrand 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. –  Martin Smith Mar 15 '12 at 13:23
    
@MartinSmith noted. –  Aaron Bertrand Mar 15 '12 at 13:25

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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