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

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 @.sYurity, 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 wrote:
> Hi
> Can anybody explain why this select returns different results in SQL 200 a
nd
> 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|||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
> 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 wrote:
>|||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
> 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 wrote:
>|||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|||Agree with you. Just faced with lots of code which uses the fact that under
SQL 2000 it usually works fine.
Thank you

> 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
>|||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 wrote:
> Agree with you. Just faced with lots of code which uses the fact that und
er
> SQL 2000 it usually works fine.
> Thank you
>|||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".
> 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 wrote:
>