Is it possible to decouple row size estimation from maximum column width?

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.

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.

StackExchange Database Administrators Q#58999, answer score: 6