I wonder whether using varchar(word*n) would be more efficient compared to using varchar(word*n-1). If yes, why? A 64 bit computer word equals 8.

  • 1
  • Size can matter (for estimating memory grants), but "rounding" columns to power-of-2 sizes isn't typically going to bring any improvement, because the unit of operation for an RDBMS is a row (or rather a whole page of rows), not a column. It's far more important to size the column correctly to business needs (i.e. don't have a FirstName VARCHAR(4000) field). There are rare cases where it might matter, but even then you'd sooner expect a difference between CHAR(33) and CHAR(32), since an engine where it really mattered could simply pad a smaller value. – Jeroen Mostert Sep 26 '18 at 13:24
  • An example of where column size actually matters is when an aggregate is computed in batch mode in SQL Server: this requires columns of 8 bytes or less (so it fits a machine word). But (as of writing) strings are excluded from this altogether, and there is no kind of optimization for "round" column sizes beyond this, so this would not influence your column lengths in general. For business purposes, VARCHAR(30) would make a lot more sense as a "round" column in any case. – Jeroen Mostert Sep 26 '18 at 13:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.