Ask a Question
Ask Question Login
Corporate Training
  1. Community
  2. SQL Server
  3. Question
SQL Server

What are the best practices concerning varchar sizing in SQL Server?

Asked by Aashish Chaursiya Jul 12, 2021 1.6K views 2 answers
Share

About this question

I'm trying to understand the best way to decide how big varchar columns should be, both from storage and performance perspectives.

Performance

From my research, it seems that varchar(max) should only be used if you really need it; that is, if the column must accommodate more than 8000 characters, one reason being the lack of indexing (though I'm a little suspicious of indexing on varchar fields in general. I'm pretty new to DB principles though, so maybe that's unfounded) and compression (more a storage concern). In fact, in general people seem to recommend only using what you need, when doing varchar(n)....oversizing is bad, because queries must account for the maximum possible size. But it's also been stated that the engine will use half the indicated size as an estimate of the average actual size of the data. This would imply that one should determine, from the data, what the average size is, double it, and use that as n. For data with very low but non-zero variability though, this implies up to a 2x oversizing over the maximum size, which seems like a lot, but maybe it's not? Insights would be appreciated. Storage

After reading about how in-row vs. out-of-row storage works, and keeping in mind that actual storage is limited to actual data, it actually seems to me that the choice of n has little or no bearing on storage (besides making sure it's big enough to hold everything). Even using varchar(max) shouldn't have any impact on storage. Instead, a goal might be to limit the actual size of each data row to ~8000 bytes if possible. Is that an accurate read on things?

Context

Some of our customer data fluctuates a little, so we generally make columns just a little wider than they need to be, say 15-20% bigger, for those columns. I was wondering if there were any other special considerations; for example, someone I work with told me to use 2^n - 1 size (I have found no evidence that's a thing though....)

I'm talking about initial table creation. A customer will tell us that they are going to start sending us a new table and send sample data (or just the first production data set), which we look at and make a table on our end to hold the data. We want to make the table on our end to handle future imports as well as what's in the sample. But, certain rows are bound to get longer, so we pad them.

The question is how much, and are there technical guidelines?

Can I increase SQL server varchar max size?

Your answer

2 Answers

Midlands Slingshot Latest answer

Answered on Nov 2, 2025

Geometry Dash is too difficult and frustrating at times; the lack of checkpoints causes many to give up. I think adding an easier mode for beginners would help expand the community.

Was this helpful?

More SQL Server discussions

Learn & Explore

Free tutorials and interview questions from industry experts — learn the skill, then get ready to prove it.

Latest SQL Server Blogs

Guides, tips and career advice on SQL Server from JanBask experts.