Index changes select reults concatenated string

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Index changes select reults concatenated string

Author

Topic

nr
SQLTeam MVY

12543 Posts

Posted - 2002-03-14 : 11:34:15

This was posted on another site.

CREATE TABLE #tblTest (
[GroupID] [int] NOT NULL ,
[Code] [char] (1) NOT NULL
)

Insert Into #tblTest
Select 1, 'a'
Insert Into #tblTest
Select 2, 'b'
Insert Into #tblTest
Select 1, 'c'
Insert Into #tblTest
Select 3, 'd'
Insert Into #tblTest
Select 2, 'e'
Insert Into #tblTest
Select 1, 'f'
Insert Into #tblTest
Select 3, 'g'
Insert Into #tblTest
Select 3, 'h'

Declare @str varchar(100)

Set @str = ''
Select @str = @str + code
from tblTest
where GroupID=1
order by code

Select @str

CREATE CLUSTERED INDEX [x] ON [#tblTest]([GroupID])
Set @str = ''
Select @str = @str + code
from tblTest
where GroupID=1
order by code

Select @str

The first should give the correct result acf
The second gives f
This is in v7 and v2000 sp1 - I think it is corrected in sp2.
It also happens for a non-clustered index.

I'll post what I found next.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

nr
SQLTeam MVY

12543 Posts

Posted - 2002-03-14 : 11:35:33

If you order descending it gives 'a'.
non-clustered index does the same.
Query plan looks ok.

Select @str = @str + code + ','
from (select top 100 percent code from #tblTest where GroupID=1 order by code) as a
order by code

works ok.

Select @str = @str + code
from #tblTest (index(0))
where GroupID = 1
order by code

works ok
whereas

Select @str = @str + code
from #tblTest (index(1))
where GroupID = 1
order by code

fails

so it is the clustered index seek that is causing the problem.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources