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?
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 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 AS CONVERT(NVARCHAR(450), COALESCE([fieldValue],'')) PERSISTED; 
persisted? 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.