CODEWITHSUNDEEP
sqlsql-serversql-server-2005t-sqlnvarchar
smartcaveman
smartcaveman

Reputation: 42276

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

How long does an nvarchar field need to be before it is better to use a text field in SQL Server? What are the general indications for using one or the other for textual content that may or may not be queried?

Upvotes: 1

Views: 389

Answers (3)

Alex Aza
Alex Aza

Reputation: 78527

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.

Upvotes: 3

Jerad Rose
Jerad Rose

Reputation: 15513

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).

Upvotes: 7

M.R.
M.R.

Reputation: 4837

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..

Upvotes: 1

Related Questions