Storing varchar(max) & varbinary(max) together - Problem?

Question

I have an app that will have entries of both varchar(max) and varbinary(max) data types. I was considering putting these both in a separate table, together, even if only one of the two will be used at any given time.

The question is whether storing them together has any impact on performance. Considering that they are stored in the heap, I'm thinking that having them together will not be a problem. However, the varchar(max) column will be probably have the text in row table option set.

I couldn't find any performance testing or profiling while "googling bing," probably too specific a question?

The SQL Server 2008 table looks like this:

Id
ParentId
Version
VersionDate
StringContent - varchar(max)
BinaryContent - varbinary(max)

The app will decide which of the two columns to select for when the data is queried. The string column will much used much more frequently than the binary column - will this have any impact on performance?

Answer 1

See this earlier answer and this answer.

text in row option is deprecated and applies the the text, ntext, image data types

varchar(max) by default stores the text in the row up to the limit and then outside the row above the limit unless large value types out of row option is set, in which it's always stored out of row - which would now mean you're storing the data out of the table and then out of that table, too ;-).

If you are already storing them in this separate table, that might not strictly be necessary unless you need the one-to-many relationship which your other columns suggest - since the size of the existing data in your parent row may force these elements out of row. With the data logically in a separate table, you do get more options, however.