What is the biggest data type to use as a local variable in a stored procedure?

sql-server text asp-classic procedure nvarchar
Tudoran Bogdan picture Tudoran Bogdan · Oct 30, 2012 · Viewed 15.9k times · Source

I have the next issue:

--DECLARE @TEST NVARCHAR(MAX)
--DECLARE @TEST2 NVARCHAR(MAX)

DECLARE @TEST NTEXT
DECLARE @TEST2 NTEXT

NVARCHAR(MAX) is to small for the amount of text in need to put when executing a stored procedure, also, TEXT, NTEXT and IMAGE data types are invalid for local variables, what can I do to sidestep this issue and store the oversized text like.

Thanks in advance

Answer

Remus Rusanu picture Remus Rusanu · Oct 30, 2012

NVARCHAR(MAX) is to small for the amount of text in need to put when executing a stored procedure

Well, bad news: this is the largest data type available! 2GB of storage, there just isn't anything that can hold more than that. In fact all large types have the same size: VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX): they all have 2GB max size (As a side note the deprecated legacy types have exactly the same max size). Only FILESTREAM can exceed this size, but you cannot declare a variable as FILESTREAM.

So this really begs the question: what the heck are you doing in a stored procedure to add +2GB of data in a variable? You cannot possible have a justified reason for this, so you should reconsider your approach. Use the disk, Luke, not the RAM! Consider a @table variable or a #temp table...

Related questions

SQL Server Text type vs. varchar data type

I have variable length character data and want to store in SQL Server (2005) database. I want to learn some best practices about how to choose TEXT SQL type or choose VARCHAR SQL type, pros and cons in performance/footprint/function.

Read More
Using varchar(MAX) vs TEXT on SQL Server

I just read that the VARCHAR(MAX) datatype (which can store close to 2GB of char data) is the recommended replacement for the TEXT datatype in SQL Server 2005 and Next SQL SERVER versions. If I want to search inside a …

Read More
nvarchar(max) vs NText

What are the advantages and disadvantages of using the nvarchar(max) vs. NText data types in SQL Server? I don't need backward compatibility, so it is fine that nvarchar(max) isn't supported in older SQL Server releases. Edit: Apparently the …

Read More