What is the impact of large varchar data types on indexing?

Question

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.

Answer 1

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.