How to save data in a row in SQL Server

Question

I am trying to figure out how to determine if data is stored in a VARCHAR(n) column in SQL Server 2008 in a row or out of a row. Does anyone know how to do this?

Also, is there a way to store data in a row if we want there?

Answer 1

To find out if there is a value in a row or outside a row, you can use DBCC PAGE

The way to make the VARCHAR(N) column in the row (rather than VARCHAR(MAX) ) is to make it part of the index cluster key. This, of course, limits the length of the field to a maximum key size of 900 keys.

Answer 2

Check out the large value parameter from the row in SQL Server if it is a VARCHAR (MAX) column. The documentation is worth reading, because setting the parameter does not immediately convert the data to a table.

 sp_tableoption N'MyTable', 'large value types out of row', 'OFF' 

Answer 3

to show how big the line can be earlier than the line store:

  select OBJECTPROPERTY(Object_id('TableName'), 'TableTextInRowLimit') 

point to the row store for the table:

  sp_tableoption 'TableName', 'text in row', 'ON'