Efficiency in SQL

Question

What is more efficiency: to do update with join or to do update with where.

Here is my code:

Join:

CREATE procedure [dbo].[MyProc]
@tvp myType readonly
as
 update tb
 set pp_update=CONVERT(date,GETDATE(),101)
 from myTable tb
 join @tvp t on t.crc32 = tb.pp_crc32

Where:

CREATE procedure [dbo].[MyProc]
@tvp myType readonly
as
 update tb
 set pp_update=CONVERT(date,GETDATE(),101)
 from myTable tb
 where t.crc32 = tb.pp_crc32

What is prefer? and if I have 2 terms can I use join??

The second query wont work as you haven't established what `t` is. The only way you'll ever really know is to profile it, but I would expect the performance of both to be comparable - it's a perennial question - http://stackoverflow.com/questions/121631/inner-join-vs-where — dash, Oct 10 '12 at 08:32

score 1 · Accepted Answer · answered Oct 10 '12 at 08:32

1

I think you have a typo error at the second query. Maybe you mean

CREATE procedure [dbo].[MyProc]
@tvp myType readonly
as
 update tb
 set pp_update=CONVERT(date,GETDATE(),101)
 from myTable tb, @tvp t
 where t.crc32 = tb.pp_crc32

The two queries are the same. The only thing is that the first query is in ANSI SQL-92 syntax while the other one is the SQL-89 (old one) syntax.

answered Oct 10 '12 at 08:32

John Woo

258,903
69
498
492

thanks, and if I have 2 terms, can I use with the first syntax? – Chani Poz Oct 10 '12 at 08:38
the first syntax is much better since it is more clear than the second one. – John Woo Oct 10 '12 at 08:40

Efficiency in SQL

1 Answers1