(I don't believe this question is a duplicate of this question from 8 years ago, as I'm not asking about the advantages of oversized columns, I'm asking about the behaviour demonstrated in the linked article below.)
This recent (2017) article from SQLPerformance.com demonstrates how varying the maximum length
n for a
varchar(n) column affects query plan row-size estimates and sorting-buffer size estimates that can lead to subpar performance and memory allocation warnings.
In it, the author claims (emphasis mine):
From here we see that, the bigger the column definition, the higher the estimated row and data size. In this simple query, the I/O cost (0.0512731) is the same across all of the queries, regardless of definition, because the clustered index scan has to read all of the data anyway.
But there are other scenarios where this estimated row and total data size will have an impact: operations that require additional resources, such as sorts.
When I read that claim (in bold) I was surprised because I thought that SQL Server would get fairly accurate row-size estimates from its sampled
STATISTICS objects maintained on those same tables. Especially given the
SELECT AVG(LEN(email)) query in the article shows that no column has a value exceeding 77 characters.
The article also explicitly performs an
ALTER INDEX ALL ON dbo.Table REBUILD - which this DB.SE posting says will will also automatically update
(though I'm surprised that the word "statistics" doesn't appear anywhere in the SQLPerformance article at all - so maybe in the author's case the statistics weren't updated at all due to some machine-configuration and they didn't notice?)
Does SQL Server only use the
varchar column length limit for row-size estimates? If not, then why does the SQLPerformance article describe the same?