TomThomson (11/26/2014)
If you make two tables, once called @table and the other called @tible, say, both with the same content, and make one of the methods operate on @table and the other on @tible, you can have a batch that contains both methods. That makes it possible to bring up the estimated execution plans, and discover that the optimizer believes option 2 is better. In case you don't trust the optimizer, you can then run the batch and look at the actual execution plans. The second option turns out better than the first again - indeed the whole of the second option (two plans - a plan whose last part is a cond-with-query op, and a second plan for the query component of that op) costs less than the first of the two queries in the first option (two plans, one for each of the two queries). Now this is of course for SQL Server 2014 developer edition on my laptop, with other SQL Server releases and different hardware the result may be different; but the measurement that your comment provoked me to undertake seems to indicate that your comment is excessively critical of the question, unwarrantedly harsh.
Do not trust the cost figures in execution plans for tuning. They are estimates. (Yes, even in an actual execution plan). So if you have performance issues caused by a misestimate, the figure you are looking at tells you exactly the same incorrect information that the optimizer based its bad plan choice on.
In this specific case, the estimated execution plan will always show both queries in the IF EXISTS version, because the entire batch is compiled. The actual execution plan will show only the queries that actually ran. If you run the batch a second time, I expect the UPDATE + condition INSERT to become cheaper because now the actual plan will no longer include the INSERT.
However, you are still looking at lies.
Bottom line is, option 1 executes either 1 or 2 queries; option 2 always executes 2 queries. One of those queries may or may not be marginally cheaper than the other (I doubt it, though), but the cost of skipping a full query outweighs that by a huge margin.