Take the 2-minute tour ×
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.

We have some tables in our database that have a data type of varchar(8000) where the longest actual value in that column is 75. When we place an index on the column we get the expected warning that the maximum permissible index length is 900 bytes.

Does the actual data type impact the effectiveness of this index? That is, since the actual data is far below 900 bytes, will the index still work?

For what it's worth, I am trying to get the data type modified as this is a ridiculous limit for the column's purpose, but I'm not sure how vehemently to argue.

share|improve this question
1  
What is the argument for keeping the column at 8000 bytes, when clearly that length is not needed? –  Aaron Bertrand Mar 15 '12 at 13:40
4  
One argument for your pro modification case. As index rebuilds require a sort operation wonder whether this will have an impact there as well. –  Martin Smith Mar 15 '12 at 13:42
    
Great share, Martin! –  JHFB Mar 15 '12 at 14:15
1  
Related dba.stackexchange.com/questions/3160/… –  Leigh Riffel Mar 15 '12 at 17:08
    
By "placing an index on the column" I'm guessing you mean a non-clustered index? –  DForck42 Mar 15 '12 at 17:12
show 1 more comment

1 Answer 1

up vote 3 down vote accepted

The very first thing to ensure is what the requirements for the size of the data is. If it will ever be above 900 then you can't create this index since an insert of more than 900 characters will fail.

Here's an example.

As far as size of the index goes, no, the size of the varchar data type won't affect how big the index is. Because the data type is varchar, it will only take up the size of the actual data. If the data changes a lot, though, you will get fragmentations depending on your index fill factor because the allocation for the data will have to grow if more characters are added.

share|improve this answer
    
Thanks, DForck - that's precisely what I needed to know! –  JHFB Mar 15 '12 at 18:37
add comment

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.