Sql-server – Computed column with less size than the “referring column”

indexsql serversql-server-2008-r2

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?

Best Answer

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.

Related Question