Store VarChar(Max) [Notes] Column on Same or Separate Table

by Kirk Saunders   Last Updated May 22, 2020 21:06 PM

Disclaimer

I had and idea for a table design a little while ago that made sense to me at the time. In recent history I have realized I have a habit of "over engineering" or spending too much time trying to "over optimize" solutions. I am assuming that this is one of those instances where I am over thinking the solution and will create extra overhead with little to no actual gain.

Question

Assume for an object (a Shipment table for example) there is some kind of NOTE varchar(MAX) data element we want to keep track of. The NOTE column has the opportunity to have data pushed into an overflow and dramatically increase the size of the row (thus limiting the number of rows that can be saved on the page). As I understand it, this then negatively impacts the execution time of various operations on the table as a whole.

Is there ever an instance where we should push that column into a separate stand alone ShipmentNote table instead of keeping it as a column on the Shipment table? The theory being, if we push the NOTE column into a separate table it saves the pages on the Shipment table which allows all operations on the Shipment table to perform better. Because the row size is smaller and now you can fit more rows onto the same page.

(See schema examples below):

ER Diagram

The main use case where this might be a good idea is if:

  • The Note column is regularly over 8000 characters (which I think is when we start using the extra paging)
  • The Note column only returned in SELECT operations and is rarely or never used as part of a JOIN or WHERE
  • The remaining columns on Shipment are going to be queried independent of Note on a regular basis (IE: most of our operations, will not utilize Note, be that JOIN or WHERE conditions happening on other columns in `Shipment)

The disadvantages I am seeing (outside of this may not actual make noticeable improvements in working with the Shipment table outside of the Note column):

  • It now becomes impossible (or at least requires a trigger or something else) to ensure that there is always some kind of value for NOTE (IE: because it is now on a separate child table we can't ensure that NOTE is NOT NULL for every row in Shipment
  • Any operation utilizing NOTE will now take extra effort, because of the need to do a JOIN to make sure we are working with the correct record


Related Questions



Using field size much larger than necessary

Updated May 01, 2019 14:06 PM

varchar automatically changes to varbinary

Updated August 06, 2019 04:06 AM


Rich text: VARCHAR, TEXT or BLOB in MySQL

Updated April 28, 2015 21:02 PM