CODEWITHSUNDEEP
sql-serversql-server-2008-r2sqldatatypes
maycil
maycil

Reputation: 764

SQL Server text, ntext, image data type problem

Microsoft announced that: "ntext, text, and image data types will be removed in a future version of Microsoft SQL Server." And advises to use nvarchar(max), varchar(max), and varbinary(max) instead.

My question: I have a data type bigger than 8 kb. How can I insert this data to sql? Which data type will solve my problem?

MSDN document about data types

Upvotes: 4

Views: 4131

Answers (4)

codingbadger
codingbadger

Reputation: 44042

You would use either nvarchar(max), varchar(max) or varbinary(max)

If you insert data greater than the 8KB row limitation the data is moved off-row to another page in a ROW_OVERFLOW_DATA allocation unit.

The following MSDN article explains this process in further detail.

I believe the maximum for varchar(max) is 2GB.

Upvotes: 0

gbn
gbn

Reputation: 432667

Don't be confused by the "varchar" bit.

It basically means you can use most functions on varchar(max) like you would on varchar(50)orvarchar(1000)orvarchar(8000)`.

  • The (max) datatypes support 2^32-1 bytes
  • The non-max types support upto 8000 bytes

So you can have varchar(50) or varchar(1000) upto varchar(8000) but for storing longer strings you'd use varchar(max).

The "max" or "50"/"1000"/"8000" bit simply determines internal storage + maximum string length.

There a few minor differences, such as indexing, of course.

Upvotes: 0

Oded
Oded

Reputation: 499372

Any of nvarchar(max), varchar(max), and varbinary(max).

nvarchar(max) and varchar(max) are for textual information (with nvarchar having unicode support).

varbinary(max) is for binary data (images, files etc).

In all cases, it is documented that using MAX indicates that the maximum storage size is 2^31-1 bytes.

Upvotes: 3

Mitch Wheat
Mitch Wheat

Reputation: 300789

If it is text data then either varchar(max) or nvarchar(max) (supports unicode). varchar(max) has a maximum storage size of 2^31-1 bytes (Ref.)

If it is binary data then use varbinary(max).

Upvotes: 2

Related Questions