Difference between nvarchar(4000) and nvarchar(3999) in SQL Server

[Yuriy]

Hi

Can anybody explain why this select returns different results in SQL 200 and
SQL 2005?

=========
if exists (select * from sysobjects where name = 'test') begin
drop table test
end
GO
if exists(select * from sysobjects where name = 'vw') begin
drop view vw
end
GO
create table test (a int, b int)
GO
create view vw as select * from test
GO

declare @s nvarchar(4000) set @s = ' select '

select @s = @s + cast(',
'+ cast(c.name as nvarchar(100))+ ' ' + cast(REPLACE(c.name ,'CM_','')
as nvarchar(100)) as nvarchar(4000))
from syscolumns c join sysobjects o on c.id = o.id
where o.name = 'vw'
order by c.colid

print @s
===========

and if you change nvarchar(4000) to nvarchar(3999) it returns the same on
both 2000 and 2005?

==========

if exists (select * from sysobjects where name = 'test') begin
drop table test
end
GO
if exists(select * from sysobjects where name = 'vw') begin
drop view vw
end
GO
create table test (a int, b int)
GO
create view vw as select * from test
GO

declare @s nvarchar(4000) set @s = ' select '

select @s = @s + cast(',
'+ cast(c.name as nvarchar(100))+ ' ' + cast(REPLACE(c.name ,'CM_','')
as nvarchar(100)) as nvarchar(3999))
from syscolumns c join sysobjects o on c.id = o.id
where o.name = 'vw'
order by c.colid

print @s

[Gert-Jan Strik]

Yurity, what do you mean?

Both queries return the same result for me on both platforms. I see no
difference (on either platform) between the 3999 varchar and 4000
varchar version.

The result I get is:
select ,
a a,
b b

However, if the you select from a table with @localvar = @localvar +
<some column>, and the selection includes more than one row, then the
result is undefined.

Apart from the result listed above, SQL Server might easily return
select ,
b b,
a a

or even
select ,
a a,
a a,
b b,
b b

In other words: I wouldn't use it.

Gert-Jan

[Yuriy Solodkyy]

Hi

Under SQL server 2005 I get

select ,
b b

(note that "a a" is absent)

in case of 4000 and I get correct result

select ,
a a,
b b

in case of 3999.

Can anyone else reproduce my problem?

Meanwhile, I do not agree with you about unspecified case, because there
is "order by" specifying the order.

Yuriy

[Yuriy Solodkyy]

It is interesting that changing nvarchar(4000) to nvarchar(3999) changes
execution plan. Can you post your execution plan of the select stement here?

Thank you

[Razvan Socol]

Hello, Yuriy

I was able to reproduce the problem you mentioned, on SQL Server 2005
Express Edition (9.00.1399), in any database other than master.
However, in the master database, it gives the expected results. It's
not clear to me (yet) what is the difference that makes SQL Server to
execute the query in the usual way in the master database, but not in
the other databases.

However, the result of such a query is undefined, as documented by KB
Article 287515:
"The correct behavior for an aggregate concatenation query is
undefined."

If you insist on using aggregate concatenation, my suggestion is to use
it on a single table (not using any JOIN-s) and not to use any other
expressions (either in the SELECT clause or in the ORDER BY clause).
For example:

[...]
declare @t TABLE (colid smallint PRIMARY KEY, x nvarchar(4000))

insert into @t
select colid, cast(',

'+ cast(c.name as nvarchar(100))+ ' ' + cast(REPLACE(c.name
,'CM_','')
as nvarchar(100)) as nvarchar(4000))
from syscolumns c join sysobjects o on c.id = o.id
where o.name = 'vw'
order by c.colid

declare @s nvarchar(4000) set @s = ' select '

select @s = @s + x FROM @t order by colid
[...]

Razvan

[Yuriy Solodkyy]

Agree with you. Just faced with lots of code which uses the fact that under
SQL 2000 it usually works fine.

Thank you

[Gert-Jan Strik]

Yes, that must be a real disappointment. I would never have guessed that
the latest version of SQL Server would only use the last row...

Maybe it is an option to lower the compatibility level. Maybe if you run
in "SQL 2000" mode the behavior is the same on SQL 2005.

Gert-Jan

[Yuriy Solodkyy]

It was initially found in level 8 DB. Option (fast 1) helps in this case,
but anyway it is necessary to stop using such selects. Meanwhile, the same
code is promoted by Ken Handerson's book "The Guru's Guide to Transact-SQL".