Strange--About SELECT @s=@s+name FROM ***

Question

Post reply

Strange--About SELECT @s=@s+name FROM ***

Viewing 10 posts - 16 through 24 (of 24 total)

You must be logged in to reply to this topic. Login to reply

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 1

Bhuvnesh

SSC Guru

Points: 59351

September 6, 2010 at 6:36 am

#1216905

i also caught the same as chris , varchar(10) is playing the trick , but why ? , dont know:unsure:

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 2

ColdCoffee (9/6/2010)

i increased the VARCHAR to 12 and voila, i get the desired result "always"...Hmm..

yes .. but size should work according to select len('10_samjack') which is 10 which means varchar(10) should give us "10_samjack" but its giving "10_jack"

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

There's more:

DECLARE @s1 varchar(10), @s2 varchar(10)



-- '10__Jack'

SET @s1 = '10__'

SELECT @s1 = @s1 + Name--, @s2 = 'x'

FROM (SELECT ID = 1, Name = CAST('Sam' AS VARCHAR(10)) UNION ALL SELECT 2, 'Jack')  tb ORDER BY id

SELECT @s1

-- '10__Sam'

SET @s1 = '10__'

SELECT @s1 = @s1 + Name

FROM (SELECT ID = 1, Name = CAST('Sam' AS VARCHAR(10)) UNION ALL SELECT 2, 'Jack') tb ORDER BY 1 desc

SELECT @s1

-- '10__SamJac'

SET @s1 = '10__'

SELECT @s1 = @s1 + Name

FROM (SELECT ID = 1, Name = CAST('Sam' AS VARCHAR(10)) UNION ALL SELECT 2, 'Jack') tb ORDER BY Name DESC

SELECT @s1

-- '10__SamJac'

SET @s1 = '10__'

SELECT @s1 = @s1 + Name

FROM (SELECT ID = 1, Name = CAST('Sam' AS VARCHAR(10)) UNION ALL SELECT 2, 'Jack') tb

SELECT @s1

-- '10__SamJac'

SET @s1 = '10__'

SELECT @s1 = @s1 + Name

FROM (SELECT TOP 2 ID = 1, Name = CAST('Sam' AS VARCHAR(10)) UNION ALL SELECT 2, 'Jack') tb ORDER BY ID

SELECT @s1

-- '10__Jack'

SET @s1 = '10__'

SELECT @s1 = @s1 + Name, @s2 = 'x'

FROM (SELECT ID = 1, Name = CAST('Sam' AS VARCHAR(10)) UNION ALL SELECT 2, 'Jack') tb ORDER BY id

SELECT @s1

If you check out the plans, the first two queries give a first step (constant scan) row size of 20B, compared with the queries which give the expected result, which have a first step row size of 16B.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ColdCoffee SSC-Dedicated Points: 39971 More actions · Answer 4

ColdCoffee

SSC-Dedicated

Points: 39971

September 6, 2010 at 7:28 am

#1216926

Are we in for a Connect Item ?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

It's looking promising. I'd like to hear a few opinions from others first.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

ColdCoffee (9/6/2010)

Are we in for a Connect Item ?

Nope, it's an unsupported use of variable assignment which yields unpredictable results and has already been covered on the forum here.

The examples we've come up with merely support this fact.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ColdCoffee SSC-Dedicated Points: 39971 More actions · Answer 7

Chris Morris-439714 (9/6/2010)

ColdCoffee (9/6/2010)

Are we in for a Connect Item ?

Nope, it's an unsupported use of variable assignment which yields unpredictable results and has already been covered on the forum here.

The examples we've come up with merely support this fact.

Oh, thats news, to me atleast 😛 Thanks Morris, learnt a new thing today 🙂

HelloFOFO Old Hand Points: 349 More actions · Answer 8

thanks.

«Inside SQL Server 2005:T-SQL»says:

"SET @var = value" is the right way to assign value to variables;

when u use "SELECT @var=col",the results may diff,

because u dont know how query optimizer executes ur SQL,espically when u use ORDER BY clause.

1?assign value first,and then sort

the result will be "10_jack"

2?sort first, and then assign value

the result will be "10_samjack"

thank u again...

Madhivanan-208264 SSCertifiable Points: 7516 More actions · Answer 9

Note that @s1 is declared as varchar(10) whereas @s2 as varchar(20). Use varchar(20) for @s1 and see the result. It gives the correct result

DECLARE @TB TABLE (id INT,name varchar(10))



INSERT @TB SELECT 1 ,'jack'

UNION ALL SELECT 2 ,'sam'

UNION ALL SELECT 6 ,'micle'

UNION ALL SELECT 7 ,'Jop'

UNION ALL SELECT 7 ,'Jop'

UNION ALL SELECT 12, 'Nill' 

DECLARE @s1 varchar(20),@s2 varchar(20) --len diff

--SQL 1

SELECT @s1='10_'

SELECT @s1=@s1+NAME FROM ( SELECT TOP 2 id,name FROM @TB ORDER BY ID ) a ORDER BY ID DESC

SELECT @s1 

--SQL 2(the same with SQL 1,just replace @s1 with @s2,the result diffs)

SELECT @s2='20_'

SELECT @s2=@s2+NAME FROM ( SELECT TOP 2 id,name FROM @TB ORDER BY ID ) a ORDER BY ID DESC

SELECT @s2 

--SQL 3(use 2 variable together,the same results)

SELECT @s1='10_',@s2='20_'

SELECT @s1=@s1+NAME,@s2=@s2+NAME

FROM ( SELECT TOP 2 id,name FROM @TB ORDER BY ID ) a ORDER BY ID DESC

SELECT @s1,@s2

Madhivanan

Failing to plan is Planning to fail

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

Madhivanan-208264 (9/7/2010)

Note that @s1 is declared as varchar(10) whereas @s2 as varchar(20). Use varchar(20) for @s1 and see the result. It gives the correct result

It's not as simple as changing the size of the variable. Depending upon how you lay out the data retrieval query and the variable assignment, even a VARCHAR(20) receiving variable may contain only one of the two names expected.

There is no "correct result" for this type of query because it's unsupported, undocumented and discouraged. When an expected result is obtained it is by accident, not by design.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden