So I have this database in SQL Server 2005. My queries are running slowly. After checking with the query optimizer, it seems that the problem comes from one specific column, where I search for a specific value. That table is basically a custom-built dictionary of English words, and I'm searching for a specific word. It seems to scan the entire table looking for that word, which is too slow. The solution, IMO, is to build an index.
Problem is, the column is of type nvarchar(max). I made it that way to accommodate words of any possible length. SQL Server apparently only allows indexes on columns with a maximum size of 900 bytes. so I need to convert the column to nvarchar(900).
alter table Dictionary add Word900 as cast(Word as nvarchar(900))
alter table Dictionary drop column Word
I see three problems with this:
1) Won't it be wasteful of memory space? I mean, won't all words be fixed to 900 bytes, even if they take less than that?
2) How will that affect my queries? Will I need to pad each word I'm searching for with spaces to get them up to 900 bytes to compare with the Word900 column?
3) Obviously, words that are more than 900 characters in length will be truncated. How do I know if there are any? Is there a way to search the Word column to return the longest word?
As a bonus question: do you know of some other, undocumented way of making an index on an nvarchar(max) column?
Thanks in advance for your help.