Index in calculated field from a ntext field

[hnteknik]

For some reasons I have to use a ntext field for both small strings like "10" and large binalry files.

I need to sort the field to some extend to present the small strings on a sorted nice way - answers to " What country are you from" etc.

To trick the sorting I use a calculated field:

ORDER BY RSort - where Rsort is:

convert(varchar(4), RD.response) as RSort

It works but put a high load on the SQL server when the number of responses increases.

I though of making a non clustered index based on the calculated field, but is not sure that it will work as intended.

What do I do. The last thing would be to change the ntext to vchar(3800) or something like that.

[rmiao]

Did you try 'text in row' feature? Check it out in books online.

[hnteknik]

Thanks rmiao

could be a solution - I have to look it up.

[hnteknik]

Aahh something like that:

EXEC sp_tableoption 'rdtable', 'text in row', '24'