At what point does it become more efficient to use a text field than an nvarchar field in SQL Server?

From what I understand, the TEXT datatype should never be used in SQL 2005+. You should start using VARCHAR(MAX) instead See this question about VARCHAR(MAX) vs TEXT UPDATE (per comment): This blog does a good job at explaining the advantages. Taken from it: But the pain from using the type text comes in when trying to query against it.

For example grouping by a text type is not possible Another downside to using text types is increased disk IO due to the fact each record now points to a blob (or file) So basically VARCHAR(MAX) keeps the data with the record, and gives you the ability to treat it like other VARCHAR types, like using GROUP BY and string functions ( LEN CHARINDEX etc. ) For TEXT you almost always have to convert it to VARCHAR to use functions against it But back to the root of your question regarding efficiency, I don't think it's ever more efficient to use TEXT vs VARCHAR(MAX) Looking at this MSDN article (search for "data types") TEXT is deprecated, and should be replaced with VARCHAR(MAX).

From what I understand, the TEXT datatype should never be used in SQL 2005+. You should start using VARCHAR(MAX) instead. See this question about VARCHAR(MAX) vs. TEXT.

UPDATE (per comment): This blog does a good job at explaining the advantages. Taken from it: But the pain from using the type text comes in when trying to query against it. For example grouping by a text type is not possible.

Another downside to using text types is increased disk IO due to the fact each record now points to a blob (or file). So basically, VARCHAR(MAX) keeps the data with the record, and gives you the ability to treat it like other VARCHAR types, like using GROUP BY and string functions (LEN, CHARINDEX, etc. ). For TEXT, you almost always have to convert it to VARCHAR to use functions against it.

But back to the root of your question regarding efficiency, I don't think it's ever more efficient to use TEXT vs. VARCHAR(MAX). Looking at this MSDN article (search for "data types"), TEXT is deprecated, and should be replaced with VARCHAR(MAX).

– smartcaveman May 12 at 3:54 @smartcavement - I have updated with more info, and also removed the link re: MAX vs. N. Typically when VARCHAR(MAX) is brought up, it indirectly (or directly) relates to when to use it vs. a set length. I thought maybe at the root, you may have been looking for that comparison.

– Jerad Rose May 12 at 4:05.

First of all don't use text at all. MSDN says: ntext, text, and image data types will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them.

Use nvarchar(max), varchar(max), and varbinary(max) instead. Varchar(max) is what you might need. If you compare varchar(n) vs varchar(max), these are technically two different datatypes (stored differently): varchar(n) value is always stored inside of the row.

Which means it cannot be greater than max row size, and row cannot be greater than page size, which is 8K. Varchar(max) is stored outsize the row. Row has a pointer to a separate BLOB page.

However, under certain condition varchar(max) can store data as a regular row, obviously it should at least fit to the row size. So if your row is potentially greater than 8K, you have to use varchar(max). If not, using varchar(n) will likely be preferable as it is faster to retrieve in-row data vs from outside page.

MSDN says: Use varchar(max) when the sizes of the column data entries vary considerably, and the size might exceed 8,000 bytes.

1 for sourcing official documentation – Merlyn Morgan-Graham May 12 at 4:18.

The main advantage of VARCHAR over TEXT is that you can run string manipulations and string functions on it. With VARCHAR(max), now you basically have an awesome large (unrestricted) variable that you can manipulate how you want..

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.