6

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

Tudoran Bogdan
  • 63
  • 1
  • 1
  • 3
  • 4
    nvarchar(max) is 2GB; if you really need to store more than that, you should split it and store to multiple rows – OzrenTkalcecKrznaric Oct 30 '12 at 09:55
  • `nvarchar(max)` allows as much as `NTEXT`. Presumably you believe that the max is `8,000` bytes? – Martin Smith Oct 30 '12 at 09:58
  • 1
    `NVARCHAR(MAX)` = 2 GB of data - that's **1 billion** characters. That's Tolstoj's *War and Peace* more than 100 times over ! And that's *not big enough for me* - what on earth are you storing in your database????? – marc_s Oct 30 '12 at 09:59
  • the issue is that I have to return for a certain case ~4500 rows in a asp page and it freezes... – Tudoran Bogdan Oct 30 '12 at 10:11

1 Answers1

14

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

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569