0

I keep reading stuff like this:

The text in row option will be removed in a future version of SQL Server. Avoid using this option in new development work, and plan to modify applications that currently use text in row. We recommend that you store large data by using the varchar(max), nvarchar(max), or varbinary(max) data types. To control in-row and out-of-row behavior of these data types, use the large value types out of row option.

So what should we do if we have a varchar(max) field that we want to limit to 16 chars in row?

Thanks!

EDIT. When I say "in row", I mean the VARCHAR/TEXT strings are stored directly in the data row, not as a pointer (with the string data stored elsewhere.) Moving the data out of the row will increase table scan performance if the data moved out of the row is not part of the "where" clause.

EDIT. The text I quoted, says this:

To control in-row and out-of-row behavior of these data types, use the large value types out of row option.

Sure enough:

https://msdn.microsoft.com/en-us/library/ms173530.aspx

But on that page it says this:

The text in row feature will be removed in a future version of SQL Server. To store large value data, we recommend that you use of the varchar(max), nvarchar(max) and varbinary(max) data types.

So the question remains.

EDIT. It appears we will still have the ability to use this table option:

large value types out of row. A value of 1 means 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. a value of 0 means 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.

However, we seem to be losing the option to keep the data in the row when it is small. It will be either all in or all out. Is there any other way to do this?

johnnycrash
  • 5,184
  • 5
  • 34
  • 58
  • "in row" refers to the # of bytes stored in the row. If the data were larger than 16 then it would be stored out of row. – johnnycrash Aug 28 '15 at 21:54
  • Well Personally i never use VARCHAR(MAX) because its cannot be index among other things. Why not use VARCHAR(8000)? – Holmes IV Aug 28 '15 at 23:13
  • 1) The field will never be indexed. 2) I want to keep the row size as small as possible. This text data is very rarely queried. varchar(8000) would keep all the text in row. Larger rows = slower normal queries. – johnnycrash Aug 28 '15 at 23:25
  • @johnnycrash I think you misunderstand the documentation you included in your question. Of the two options one is always out-of-row and the other emulates the previous behavior. It's not always in or always out as you suppose. – gordy Aug 28 '15 at 23:42
  • What option will allow us to set set a char count limit. Above that count, the data is stored out of row. below or at that count data is stored in the row. – johnnycrash Aug 28 '15 at 23:59
  • There is not an option that would allow you to specify the threshold at which a value would be stored out-of-row, it is always 8000 or as space permits in the roughly 8000 byte page – gordy Aug 29 '15 at 01:31
  • @gordy, make that the answer and I will give it to you. – johnnycrash Sep 01 '15 at 18:40

2 Answers2

0

You are correct, there is no configuration able to do the same as the old 'TEXT_IN_ROW'.

You either let SQL Server store up to 8Kb in the page or store information of any size out of row.

Dennes Torres
  • 396
  • 2
  • 10
0

So what should we do if we have a varchar(max) field that we want to limit to 16 chars in row?

CREATE TABLE dbo.T1 (SomeCol VARCHAR(MAX) CHECK (LEN(SomeCol)<=16));
Alan Burstein
  • 7,770
  • 1
  • 15
  • 18