September 4, 2010 at 6:27 pm
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(10),@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
Anybody knows how to explain this ?
Maybe,it concerns to the Exec Plan,but I don't know how SQLServer prepares for this,and y?
Many thanks...
September 6, 2010 at 4:59 am
can you elaborate it ? i saw the exec plan but didnt get anything bad there
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
September 6, 2010 at 5:08 am
What exactly do you find strange?
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
September 6, 2010 at 5:28 am
Chris Morris-439714 (9/6/2010)
What exactly do you find strange?
Chris, even in the first try i dint find anything strange.. Execute them, u will find it strange..
September 6, 2010 at 5:32 am
ColdCoffee (9/6/2010)
Chris Morris-439714 (9/6/2010)
What exactly do you find strange?Chris, even in the first try i dint find anything strange.. Execute them, u will find it strange..
Must be monday morning effect CC, I still see nothing unexpected.
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
September 6, 2010 at 5:35 am
ColdCoffee (9/6/2010)
Execute them, u will find it strange..
Still nothing.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
September 6, 2010 at 5:39 am
Chris Morris-439714 (9/6/2010)
ColdCoffee (9/6/2010)
Chris Morris-439714 (9/6/2010)
What exactly do you find strange?Chris, even in the first try i dint find anything strange.. Execute them, u will find it strange..
Must be monday morning effect CC, I still see nothing unexpected.
😀 , u are legend mate, u shdn have "morning effects"
ok, the first chunk :
DECLARE @s1 varchar(10),@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
Produces :
10_jack
The second:
--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
Produces:
20_samjack
The third:
--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
Produces:
10_samjack 20_samjack
Now if u see, the first and third uses @s1, which is VARCHAR(10) , but the first one truncates the result to only one values 10_jack, while the third produces 10_samjack.. how would this be possible ?
September 6, 2010 at 5:49 am
ColdCoffee (9/6/2010)
Now if u see, the first and third uses @s1, which is VARCHAR(10) , but the first one truncates the result to only one values 10_jack, while the third produces 10_samjack.. how would this be possible ?
Here SELECT @s1=@s1+NAME,@s2=@s2+NAME is dealing with concatenation .
look into it
DECLARE @s1 varchar(10),@s2 varchar(20) --len diff
--SQL 1
SELECT @s1='10_',@s2='20_'
SELECT @s1=@s1+NAME,@s2=@s2+NAME
FROM ( SELECT 'test1' as name union select 'my_test' ) a
SELECT @s1,@s2
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
September 6, 2010 at 5:53 am
Bhuvnesh (9/6/2010)
ColdCoffee (9/6/2010)
Now if u see, the first and third uses @s1, which is VARCHAR(10) , but the first one truncates the result to only one values 10_jack, while the third produces 10_samjack.. how would this be possible ?Here
SELECT @s1=@s1+NAME,@s2=@s2+NAMEis dealing with concatenation .look into it
DECLARE @s1 varchar(10),@s2 varchar(20) --len diff
--SQL 1
SELECT @s1='10_',@s2='20_'
SELECT @s1=@s1+NAME,@s2=@s2+NAME
FROM ( SELECT 'test1' as name union select 'my_test' ) a
SELECT @s1,@s2
Bhuvnesh, the thing is the result set produced by FROM clause will differ as the concatenation of test1+mytest1+10_ will be > VARCHAR(10).. but in the above case, the TOP 2 will produce (sam + jack + 10_ ) = 10 chars.. so why would chunk 1 truncate the result ?
September 6, 2010 at 5:58 am
Try this, CC:
--SQL 3(use 2 variable together,the same results)
SELECT @s1 = '10_45', @s2 = '20_45'
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
The value assigned to @s1 depends upon the number of variables which are assigned values in the SELECT.
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
September 6, 2010 at 6:09 am
Chris Morris-439714 (9/6/2010)
Try this, CC:
--SQL 3(use 2 variable together,the same results)SELECT @s1 = '10_45', @s2 = '20_45'
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
The value assigned to @s1 depends upon the number of variables which are assigned values in the SELECT.
CM, i think i dint explain myself well here.. Chunk 1 and chunk 3 do exactly the same thing... but when i see the output, Chunk 1 has produced 10_jack (10_samjack is the expected output , as per my knowledge).. or shouldnt it?
September 6, 2010 at 6:13 am
ColdCoffee (9/6/2010)
Chris Morris-439714 (9/6/2010)
Try this, CC:
--SQL 3(use 2 variable together,the same results)SELECT @s1 = '10_45', @s2 = '20_45'
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
The value assigned to @s1 depends upon the number of variables which are assigned values in the SELECT.
CM, i think i dint explain myself well here.. Chunk 1 and chunk 3 do exactly the same thing... but when i see the output, Chunk 1 has produced 10_jack (10_samjack is the expected output , as per my knowledge).. or shouldnt it?
CC, I see the same as you.
Chunk 1 generates '10_jack' for @s1.
Chunk 3 generates '10_samjack' for @s1.
Now comment out the assignment to @s2 in chunk 3 and see what happens.
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
September 6, 2010 at 6:20 am
Chris Morris-439714 (9/6/2010)
ColdCoffee (9/6/2010)
Chris Morris-439714 (9/6/2010)
Try this, CC:
--SQL 3(use 2 variable together,the same results)SELECT @s1 = '10_45', @s2 = '20_45'
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
The value assigned to @s1 depends upon the number of variables which are assigned values in the SELECT.
CM, i think i dint explain myself well here.. Chunk 1 and chunk 3 do exactly the same thing... but when i see the output, Chunk 1 has produced 10_jack (10_samjack is the expected output , as per my knowledge).. or shouldnt it?
CC, I see the same as you.
Chunk 1 generates '10_jack' for @s1.
Chunk 3 generates '10_samjack' for @s1.
Now comment out the assignment to @s2 in chunk 3 and see what happens.
Oh Yeah, CM, i got it.. But why would Chunk 1 truncate the result first place ?
September 6, 2010 at 6:29 am
ColdCoffee (9/6/2010)
Oh Yeah, CM, i got it.. But why would Chunk 1 truncate the result first place ?
It doesn't look right to me either. Check these out:
DECLARE @s1 varchar(10), @Name1 varchar(10), @Name2 varchar(10)
SET @Name1 = 'Sam'
SET @Name2 = 'Jack'
SET @s1 = '10__'
SELECT @s1 = @s1 + Name
FROM (SELECT TOP 2 ID = 1, Name = @Name1 UNION ALL SELECT 2, @Name2) tb ORDER BY ID
SELECT @s1
SET @s1 = '10__'
SELECT @s1 = @s1 + Name
FROM (SELECT ID = 1, Name = @Name1 UNION ALL SELECT 2, @Name2) tb ORDER BY ID
SELECT @s1
SET @s1 = '10__'
SELECT @s1 = @s1 + Name
FROM (SELECT ID = 1, Name = @Name1 UNION ALL SELECT 2, @Name2) tb ORDER BY Name
SELECT @s1
SET @s1 = '10__'
SELECT @s1 = @s1 + Name
FROM (SELECT ID = 1, Name = @Name1 UNION ALL SELECT 2, @Name2) tb
SELECT @s1
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
September 6, 2010 at 6:35 am
Chris Morris-439714 (9/6/2010)
ColdCoffee (9/6/2010)
Oh Yeah, CM, i got it.. But why would Chunk 1 truncate the result first place ?It doesn't look right to me either. Check these out:
DECLARE @s1 varchar(10), @Name1 varchar(10), @Name2 varchar(10)SET @Name1 = 'Sam'
SET @Name2 = 'Jack'
SET @s1 = '10__'
SELECT @s1 = @s1 + Name
FROM (SELECT TOP 2 ID = 1, Name = @Name1 UNION ALL SELECT 2, @Name2) tb ORDER BY ID
SELECT @s1
SET @s1 = '10__'
SELECT @s1 = @s1 + Name
FROM (SELECT ID = 1, Name = @Name1 UNION ALL SELECT 2, @Name2) tb ORDER BY ID
SELECT @s1
SET @s1 = '10__'
SELECT @s1 = @s1 + Name
FROM (SELECT ID = 1, Name = @Name1 UNION ALL SELECT 2, @Name2) tb ORDER BY Name
SELECT @s1
SET @s1 = '10__'
SELECT @s1 = @s1 + Name
FROM (SELECT ID = 1, Name = @Name1 UNION ALL SELECT 2, @Name2) tb
SELECT @s1
An exactly, CM.. i am totay bemused when i saw that.. i realy dont know if the OP understood what the error is, but something is not right.. now i guess me and u are in same page..
i increased the VARCHAR to 12 and voila, i get the desired result "always"...Hmm..
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply