The Downside Of Oversizing

Aaron Bertrand shows why you might not want to oversize VARCHAR columns by too much:

Now, whether you go by the old standard or the new one, you do have to support the possibility that someone will use all the characters allowed. Which means you have to use 254 or 320 characters. But what I’ve seen people do is not bother researching the standard at all, and just assume that they need to support 1,000 characters, 4,000 characters, or even beyond.

So let’s take a look at what happens when we have tables with an e-mail address column of varying size, but storing the exact same data:

This is a good argument against automatically using VARCHAR(8000) (much less MAX) when creating columns.

Related Posts

Physical Versus Read-Ahead Reads

Kendra Little explains which SQL Server diagnostic tools include read-ahead reads versus “regular” physical reads: SQL Server has more than one way to pull pages in from disk for your queries. SQL Server can do a physical read of an 8KB page, or an extent of 8 ¬†of those 8KB pages. SQL Server can also […]

Read More

Memory Grants Affecting Columnstore Load

Denzil Ribeiro explains how memory grant pressure can determine whether a columnstore bulk insert¬†skips the deltastore or not: We found that only at the beginning of the run, there was contention on memory grants (RESOURCE_SEMAPHORE waits), for a short period of time. After that and later into the process, we could see some latch contention […]

Read More

Leave a Reply

Your email address will not be published. Required fields are marked *

Categories

June 2017
MTWTFSS
« May  
 1234
567891011
12131415161718
19202122232425
2627282930