6

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.

4

1 Answer 1

4

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.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.