3

I have a table named FinTrans in an Microsoft SQL Server 2012 server with approx 10K-30K records at any given time and about 2K records are inserted and deleted each day.

The table has 3 nvarchar(max) columns that hold possible error messages (3 different types of messages, hence the separate columns) for a transaction. Whenever the contents of the table are listed, these nvarchar(max) columns are also read. The columns are nvarchar(max) as their contents may go beyond 4K, however chances are slim (perhaps with one record every week).

Under these circumstances is it better to store this column in a separate table with 1:1 relationship to the FinTrans table, or shall I just leave the column where it is?

Hannah Vernon
  • 70,928
  • 22
  • 177
  • 323
Daniel
  • 143
  • 4

1 Answers1

5

is it better to store this column in a separate table with 1:1 relationship to the FinTrans table, or shall I just leave the column where it is?

Leave it. That's tiny, and not worth adding complexity. Also, even if both the table and the LOBs were larger, the answer is still to leave it. Large LOBs will be moved off of the row, and you can set a configuration option for all the LOBs to be stored off-row. You can also configure the table for the LOBs to be stored on a separate filegroup. So there's no reason to change the logical model here.

See:

large value types out of row

1 = varchar(max), nvarchar(max), varbinary(max), xml and large user-defined type (UDT) columns in the table are stored out of row, with a 16-byte pointer to the root.

0 = varchar(max), nvarchar(max), varbinary(max), xml and large UDT values are stored directly in the data row, up to a limit of 8000 bytes and as long as the value can fit in the record. If the value does not fit in the record, a pointer is stored in-row and the rest is stored out of row in the LOB storage space. 0 is the default value.

From sp_tableoption

David Browne - Microsoft
  • 49,000
  • 3
  • 53
  • 102