Strange PK Problem ... - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Strange PK Problem ...

Author

Topic

Goesta.Stoeckelmann
Starting Member

4 Posts

Posted - 2007-10-18 : 08:24:34

Hi,

i need to append several column's varchar's to one varchar with a single statement (recursive function or with temptables is too slow) and did that with a simple

select @cTest = @cTest + vVal from [dbo].[test] where vVal IS NOT NULL order by iPos asc

This works fine as soon as i put a PK on any other column in that table. I've attached a sample whitch shows the problem. The question is, what the PK on a non uses column to do with he results of the above statement??? (SQL-2005)

Pleas help ...

regards,

Gösta

**********

declare @cTest varchar(max)

CREATE TABLE [dbo].[test](
[iId] [int] NOT NULL,
[iPos] [int] NOT NULL,
[vVal] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

insert into [dbo].[test] values (1, 3, 'C')
insert into [dbo].[test] values (2, 1, 'A')
insert into [dbo].[test] values (3, 2, 'B')
select * from [dbo].[test]

select @cTest = ''
select @cTest = @cTest + vVal from [dbo].[test] where vVal IS NOT NULL order by iPos asc
print (@cTest)

drop table [dbo].[test]

-- All the same except Primary Key on iId

CREATE TABLE [dbo].[test](
[iId] [int] NOT NULL,
[iPos] [int] NOT NULL,
[vVal] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED
(
[iId] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

insert into [dbo].[test] values(1, 3, 'C')
insert into [dbo].[test] values(2, 1, 'A')
insert into [dbo].[test] values(3, 2, 'B')
select * from [dbo].[test]

select @cTest = ''
select @cTest = @cTest + vVal from [dbo].[test] where vVal IS NOT NULL order by iPos asc
print (@cTest)

drop table [dbo].[test]

-- Why does it deliver only 'C' not 'ABC' with primary key set to column that's not used?

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2007-10-18 : 09:46:33

Hi,

the problem is with the Order by Clause. if u comment the order by and execute the script it ll give CAB, but i dont know why is so.

If u change the Length of varchar(MAX) to VARCHAR(8000) then also it works fine.

Goesta.Stoeckelmann
Starting Member

4 Posts

Posted - 2007-10-18 : 09:53:47

Hmm...

looks like there's a bug in SQL-2005... Good finding with the varchar type - that helps!

Thanks & regards,

Gösta

Kristen
Test

22859 Posts

Posted - 2007-10-18 : 10:08:44

I have a workaround if you need more than 8,000 characters

Goesta.Stoeckelmann
Starting Member

4 Posts

Posted - 2007-10-18 : 10:38:32

Thanks for your help, 8000 will be enough but plz let's see, maybe interesting ...

regards,

Gösta

Kristen
Test

22859 Posts

Posted - 2007-10-18 : 10:39:53

it will take me time to convert from what I am using it for, so I'll leave it for when it is needed.

Kristen

Goesta.Stoeckelmann
Starting Member

4 Posts

Posted - 2007-10-18 : 10:42:54

Ok, np ...

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-10-18 : 17:33:19

You can also get around this by using XML.

SELECT TOP 1
	(SELECT TOP 100 PERCENT  '' + T2.vVal FROM test AS T2 ORDER BY T2.vVal FOR XML PATH(''))AS T
FROM Test

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources