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?

0

1 Answer 1

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

2
  • Excellent !. However as per requirement, these columns may hold records once per week and it may be within 8000k.So chances are slim that value does not fit in the record so it may be stored directly in data row.Now main queston , if it stay in data row then won't it increase data pages.Hence reading more number of pages.So why not keep them in separate table.I am not sure,please clear my doubts.
    – KumarHarsh
    Commented May 29, 2018 at 9:32
  • anybody can clear my doubts.
    – KumarHarsh
    Commented May 29, 2018 at 10:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.