sp_tableoption In-row behavior of tables in SQL Server

Question

There is this SQL command sp_tableoption that is used to:

Sets option values for user-defined tables. sp_tableoption can be used to control the in-row behavior of tables with varchar(max), nvarchar(max), varbinary(max), xml, text, ntext, image, or large user-defined type columns.

What is In-row behavior?

Answer 1

Thanks to @Prdp I have done some research

TL;DR; If you have a table that has big (long text columns) and you are NOT using select * all the time you are better of setting text in row to some not to large value.

exec sp_tableoption N'MyTable', 'text in row', '260'

In SQL data is held in pages default 8Kb, more records per page - faster your lookups will be (260 will allow 31 records per page which is just under 5 folds (log2(31) = ~4.95)).

Side note: following SQL will get you the average length of row for table:

declare @table nvarchar(128)
declare @sql nvarchar(max)
set @table = '[Schema].Table'
set @sql = 'select AVG((0'
select @sql = @sql + ' + isnull(datalength(' + name + '), 1)' 
        from sys.columns where object_id = object_id(@table)
set @sql = @sql + ') )  from ' + @table 

PRINT @sql -- in case you will fail to set table variable properly ;)
exec sp_executesql @SQL 

What in-row means is that actual potentially long record will be kept with the other rows in that page in expectation that when it is read like select * it will be as fast as it can be.... so in situation where you do everything with * you should put this setting to large number ~7k

If there is value for this setting when row size reaches this threshold of bytes it will be moved and pointer will be put instead - which in turn will increase number of records per page = make lookups faster...

To check what is it set to run following query:

select name, text_in_row_limit from
sys.tables

Hope this saves you some time.