Tell me more ×
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.
up vote 2 down vote favorite

I have a var char column in SQL Server 2008 which is usually less than 100 bytes but in some cases can be huge up 5607 bytes. I need to index it, and I can't because the max index size in SQLServer is 900 bytes.

Is there any way in the world I can add any sort of an index to this column? For example, create an index on the first 900 bytes?

Thanks

share|improve this question

1 Answer

up vote 4 down vote accepted

If you need an index so seeks on the first n characters work well (where n <= 900 bytes), you can create a computed column of LEFT(column, n) and index that. If you want an index for exact matching, sorry, there isn't a way that I know of to do that unless you use a checksum or similar. Can you explain the actual business problem? What strings > 900 bytes do you feel would benefit from such an index, and why?

share|improve this answer
There are a range of queries that group by this column or use this column in a where clause. Good idea on the computed column. Thanks. – dublintech Jun 18 '12 at 16:13
@dublintech - If you need to GROUP BY, an index on the HASHBYTES of the whole column will suit you perfectly. If you need to search, either use a Full-Text Index or limit yourself to searching the first 900 bytes of the column. – Nick Chammas Jun 18 '12 at 16:14
@dublintech - If you are GROUP-ing BY them that means they are repeated? Can you extract these out into another table. Give them a surrogate integer primary key then reference that in your main table (though that still gives you the original problem moved to the other table seems more efficient than repeating large strings) – Martin Smith Jun 18 '12 at 16:17

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.