sql server - SQL NVARCHAR and VARCHAR Limits -

all, have large (unavoidable) dynamic sql query. due number of fields in selection criteria string containing dynamic sql growing on 4000 chars. now, understand there 4000 max set nvarchar(max), looking @ executed sql in server profiler statement

delare @sql nvarchar(max); set @sql = 'somemassivestring > 4000 chars...'; exec(@sql); go 

seems work(!?), query large throws error associated 4000 limit(!?), trims of sql after 4000 limit , leaves me syntax error. despite in profiler, showing dynamic sql query in full(!?).

what happening here , should converting @sql variable varchar , on it?

thanks time.

ps. nice able print out more 4000 chars @ these big queries. following limited 4000

select convert(xml, @sql); print(@sql); 

is there other cool way?

i understand there 4000 max set nvarchar(max)

your understanding wrong. nvarchar(max) can store (and beyond sometimes) 2gb of data (1 billion double byte characters).

from nchar , nvarchar in books online grammar

nvarchar [ ( n | max ) ] 

the | character means these alternatives. i.e. specify either n or literal max.

if choose specify specific n must between 1 , 4,000 using max defines large object datatype (replacement ntext deprecated).

in fact in sql server 2008 seems variable 2gb limit can exceeded indefinitely subject sufficient space in tempdb (shown here)

regarding other parts of question

truncation when concatenating depends on datatype.

1. varchar(n) + varchar(n) truncate @ 8,000 characters.
2. nvarchar(n) + nvarchar(n) truncate @ 4,000 characters.
3. varchar(n) + nvarchar(n) truncate @ 4,000 characters. nvarchar has higher precedence result nvarchar(4,000)
4. [n]varchar(max) + [n]varchar(max) won't truncate (for < 2gb).
5. varchar(max) + varchar(n) won't truncate (for < 2gb) , result typed varchar(max).
6. varchar(max) + nvarchar(n) won't truncate (for < 2gb) , result typed nvarchar(max).
7. nvarchar(max) + varchar(n) first convert varchar(n) input nvarchar(n) , concatenation. if length of varchar(n) string greater 4,000 characters cast nvarchar(4000) , truncation occur.

datatypes of string literals

if use n prefix , string <= 4,000 characters long typed nvarchar(n) n length of string. n'foo' treated nvarchar(3) example. if string longer 4,000 characters treated nvarchar(max)

if don't use n prefix , string <= 8,000 characters long typed varchar(n) n length of string. if longer varchar(max)

for both of above if length of string 0 n set 1.

newer syntax elements.

1. concat function doesn't here

declare @a5000 varchar(5000) = replicate('a',5000);  select datalength(@a5000 + @a5000),         datalength(concat(@a5000,@a5000)); 

the above returns 8000 both methods of concatenation.

2. careful +=

declare @a varchar(max) = '';  set @a+= replicate('a',5000) + replicate('a',5000)  declare @b varchar(max) = '';  set @b = @b + replicate('a',5000) + replicate('a',5000)   select datalength(@a),         datalength(@b);` 

returns

-------------------- -------------------- 8000                 10000 

note @a encountered truncation.

how resolve problem experiencing.

you getting truncation either because concatenating 2 non max datatypes or because concatenating varchar(4001 - 8000) string nvarchar typed string (even nvarchar(max)).

to avoid second issue make sure string literals (or @ least lengths in 4001 - 8000 range) prefaced n.

to avoid first issue change assignment

declare @sql nvarchar(max); set @sql = 'foo' + 'bar' + ...; 

to

declare @sql nvarchar(max) = '';  set @sql = @sql + n'foo' + n'bar' 

so nvarchar(max) involved in concatenation beginning (as result of each concatenation nvarchar(max) propagate)

avoiding truncation when viewing

make sure have "results grid" mode selected can use

select @sql [processing-instruction(x)] xml path  

the ssms options allow set unlimited length xml results. processing-instruction bit avoids issues characters such < showing <.