Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345»»»

Multi-statement execution Expand / Collapse
Author
Message
Posted Wednesday, February 03, 2010 8:06 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, April 22, 2014 7:03 AM
Points: 3,924, Visits: 1,589
Very good question. I should had executed it on SSMS and then answered it. But, no I just read and answered and got it wrong.



SQL DBA.
Post #858710
Posted Wednesday, February 03, 2010 8:32 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: 2 days ago @ 11:24 AM
Points: 1,480, Visits: 922
SanjayAttray (2/3/2010)
Very good question. I should had executed it on SSMS and then answered it. But, no I just read and answered and got it wrong.



What would have running it in SSMS and putting the answer in have achieved apart from getting you the points?



Post #858743
Posted Wednesday, February 03, 2010 10:21 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, April 21, 2014 10:01 AM
Points: 1,676, Visits: 1,745
This is an excellent question, I thoroughly enjoyed it. I guess the reason why initially it is tempting to select 0 as a correct answer is incorrect assumption about when does the DSql gets injected. In other words, if the insert into portion was a part of the DSql itself then the answer would be a no-brainer (0). To figure that the execution works as it is explained is not easy, but of course makes perfect sense. There is an insert into followed by some statement to parse, and the engine expects the latter to possibly have something selected from somewhere. The select portion of the DSql does it, the delete does not change the originally selected data as it is already on the heap, and so the insert of 4 rows takes place.

I wish we had more questions like this! Thank you!

Oleg
Post #858853
Posted Wednesday, February 03, 2010 10:42 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 1:19 PM
Points: 311, Visits: 282
Thank you for all your comments.
Unfortunately no explanation links to MSDN or BOL ... probably because of that's a simplified real life example
Actually to believe in this kind of "functionality" I had to develop my own "clean" code that was published later as QoD.

Thanks,
Gennadiy
Post #858870
Posted Wednesday, February 03, 2010 12:15 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 7:59 AM
Points: 3,773, Visits: 3,589
I hadn't considered the SQL Injection aspect of this question. Thanks for pointing that out.
Post #858923
Posted Wednesday, February 03, 2010 12:24 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, April 21, 2014 10:01 AM
Points: 1,676, Visits: 1,745
It is interesting how the engine behaves when the second part of DSql changes to something different. For example, if instead of deleting the records from the table the second part of the DSql tries to say, drop it then the whole insert actually fails with error stating that insert failed because the statement tried to change the table schema. If the delete from #funny_test is replaced with select 1 then insert fails because it actually tries to accumulate all select(s) results on the hip and the select 1 portion does not have the value for the second column. But if delete from #funny_test is replaced with for example, select 99, 99 then insert succeeds and the total number of the records in the tables happens to be 15 (!), i.e. 10 original records, 4 inserted because of the first select of the DSql, and 1 inserted from the second select of DSql. In this case the ' select id, row_num from #funny_Test where row_num < ' + str(@Del_Row_Num) +'; select 99, 99;' is functionally equivalent to ' select id, row_num from #funny_Test where row_num < ' + str(@Del_Row_Num) + ' union all select 99, 99;'

Oleg
Post #858932
Posted Wednesday, February 03, 2010 6:32 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Sunday, January 19, 2014 5:00 PM
Points: 605, Visits: 1,690
I thoroughly enjoyed this question. After the raft of recent questions with debatable answers, this had no 'trick' and tested several concepts, ie:

1) Temp tables can be referenced by "lower level" code, whether that be an EXEC() or a stored proc
2) The lower level code will execute in it's entirety before it returns to the calling code
3) Results are stored in places outside of the referenced tables (ie: memory or tempdb, obviously)
4) Illustrates an example of SQL Injection


S.
Post #859220
Posted Wednesday, February 03, 2010 11:28 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, February 13, 2013 9:15 PM
Points: 112, Visits: 319
Good question. I guessed 0 thinking it was SQL injection issue which would delete all the rows in the table.Learnt sth new.
Post #859343
Posted Thursday, February 04, 2010 2:46 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 7:53 AM
Points: 1,659, Visits: 6,008
Interesting question, thanks :)

I got it right, but largely down to guesswork - my initial thought was 0, but the name of the temp table "#funny_test" made me expected something, well, funny - so I thought again!
Post #859448
Posted Thursday, February 04, 2010 5:34 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, January 11, 2013 12:41 PM
Points: 621, Visits: 297
Good One ...



Bhavesh Patel

http://bhaveshgpatel.wordpress.com/
Post #859542
« Prev Topic | Next Topic »

Add to briefcase «««12345»»»

Permissions Expand / Collapse