Advanced Interdisciplinary Projects Lab(AIPLab) 討論區

Please login or register.

請輸入帳號, 密碼以及預計登入時間

新聞:

[開學]105學年第2學期的課程看版開張了 歡迎同學問問題-20170201

作者 主題: SQL: If Exists Update Else Insert  (閱讀 7596 次)

fracchu

  • Newbie
  • *
  • 文章: 42
    • 檢視個人資料
SQL: If Exists Update Else Insert
« 於: 三月 28, 2009, 02:39:37 am »
http://blogs.msdn.com/miah/archive/2008/02/17/sql-if-exists-update-else-insert.aspx
Sunday, February 17, 2008 10:30 PM Jeremiah.Clark
SQL: If Exists Update Else Insert
This is a pretty common situation that comes up when performing database operations.  A stored procedure is called and the data needs to be updated if it already exists and inserted if it does not.  If we refer to the Books Online documentation, it gives examples that are similar to:

程式碼: [Select]
IF EXISTS (SELECT * FROM Table1 WHERE Column1='SomeValue')
    UPDATE Table1 SET (...) WHERE Column1='SomeValue'
ELSE
    INSERT INTO Table1 VALUES (...)

This approach does work, however it might not always be the best approach.  This will do a table/index scan for both the SELECT statement and the UPDATE statement.  In most standard approaches, the following statement will likely provide better performance.  It will only perform one table/index scan instead of the two that are performed in the previous approach.


程式碼: [Select]
UPDATE Table1 SET (...) WHERE Column1='SomeValue'
IF @@ROWCOUNT=0
    INSERT INTO Table1 VALUES (...)

The saved table/index scan can increase performance quite a bit as the number of rows in the targeted table grows.

Just remember, the examples in the MSDN documentation are usually the easiest way to implement something, not necessarily the best way.  Also (as I re-learned recently), with any database operation, it is good to performance test the different approaches that you take.  Sometimes the method that you think would be the worst might actually outperform the way that you think would be the better way.
 

已記錄
 

SimplePortal Classic 2.0.5