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.

In other words, is it possible to declare a column VARCHAR(n) and have the estimated size be something other than n/2?

An example use-case might be storing URLs - you expect most to be relative and less than 20 characters or so (eg "About/AboutUs"), but want to support the occasional long URL with lots of query string parameters.

share|improve this question
add comment

1 Answer 1

up vote 6 down vote accepted

No.

I don't believe this is possible.

In the case that the average length is greater than that calculated by the heuristic but still less than 4,000 bytes it is possible to massage this upwards with a CAST but even then you might need some hacking about with the query syntax to encourage the CAST to happen before any memory consuming operator that depends on the estimated row size.

In the case that it is less the CAST to a shorter size obviously won't work without the possibility of truncating data.

The possible down side of a mis-estimate is that the memory grant may be calculated based on incorrect information.

I've wondered before why the statistics on string columns don't keep track of this. Maybe in the scale of things it simply isn't regarded as objectively that important.

share|improve this answer
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.