June 10, 2008 at 9:39 am
Are there any differences between these two transactions? Is one way better than another?
Transaction #1:
DECLARE @id as int
BEGIN TRANSACTION
select TOP 1 @id = node_id from hierarchy where year = '2007'
update hierarchy
set parent_node = '12'
where node_id = @id
COMMIT TRANSACTION
Transaction #2:
BEGIN TRANSACTION
update hierarchy
set parent_node = '12'
where node_id = (select TOP 1 node_id from hierarchy where year = '2007')
COMMIT TRANSACTION
June 10, 2008 at 10:15 am
There is no noticable difference between these two queries.
If you look at the execution plan you will notice that both techniques have the same cost.
It doesn't matter if you select one or the other.
June 10, 2008 at 10:44 am
Would there be any noticeable differences with regards to locking behavior? If identical concurrent transactions (either #1 or #2) were running, would one transaction be better or worse then the other?
June 10, 2008 at 12:48 pm
To be on the safer side the first approach will be a better option.
June 10, 2008 at 1:01 pm
I don't think either will have a difference if multiple queries were running simultaneously. The best thing to do would be to test both options in a test environment and see if there are any significant differences in processing.
😎
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply