Is any performance difference - 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 2000 Forums

Transact-SQL (2000)

Is any performance difference

Author

Topic

DMP
Starting Member

41 Posts

Posted - 2006-01-09 : 02:32:12

Hi,
Is any performance difference between follwing two T-SQL ?
Why ?

T-SQL1 :

Update TableA Set Field1=xx
Where Field2=yy

T-SQL2 :
Update TableA Set Field1=xx From TableA
Where Field2=yy

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-09 : 03:11:22

Usually second method will be used if you want to update the column with column of other table

Madhivanan

Failing to plan is Planning to fail

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-01-09 : 09:06:12

No performance difference. Execution plans would be identical.

Kristen
Test

22859 Posts

Posted - 2006-01-09 : 12:50:19

FWIW Even if there is only one table I used this format (slight variation on yours):

Update U
Set Field1=xx
From TableA AS U
Where Field2=yy

(The "U" alias stands for "Update")

as it is easy to extend to

Update U
Set Field1=xx
From TableA AS U
JOIN TableB AS B ON B.Col1 = U.Col1
Where Field2=yy

the intention being that the "U" alias table is consistent from place-to-place and easily spotted - e.g. if I want to update TableB instead:

Update U
Set Field1=xx
From TableA AS A
JOIN TableB AS U ON U.Col1 = A.Col1
Where Field2=yy

Kristen

druer
Constraint Violating Yak Guru

314 Posts

Posted - 2006-01-09 : 15:38:59

DMP in general anytime you wonder what the execution plan would be by slight modifications to a command just put both commands in the Query Analyzer, tell it to show you the execution plan and then see what the plan is. Not only will the plans be shown, but you will see what Percent of the total each took. If you see 50% and 50% then likely there is no difference. If you see 75% and 25% then you will likely see a different execution plan, and you know that you've done something good in your change. (Assuming that you are still returning/seeing the same results.)

DMP
Starting Member

41 Posts

Posted - 2006-01-09 : 23:54:33

Thanks everybody.............

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources