T-SQL插入或更新 [英] T-SQL Insert or update

问题描述

我对SQL Server的性能有疑问.

I have a question regarding performance of SQL Server.

假设我有一个表persons，其中包含以下列:id，name，surname.

Suppose I have a table persons with the following columns: id, name, surname.

现在，我想在此表中插入新行.规则如下:

Now, I want to insert a new row in this table. The rule is the following:

1. 如果表中没有id，则插入行.

如果存在id，则进行更新.

If id is present, then update.

我在这里有两种解决方法:

I have two solutions here:

第一:

update persons
  set id=@p_id, name=@p_name, surname=@p_surname
where id=@p_id
if @@ROWCOUNT = 0 
  insert into persons(id, name, surname)
  values (@p_id, @p_name, @p_surname)

第二:

if exists (select id from persons where id = @p_id)
  update persons
    set id=@p_id, name=@p_name, surname=@p_surname
  where id=@p_id
else
  insert into persons(id, name, surname)
  values (@p_id, @p_name, @p_surname)

什么是更好的方法?似乎在第二种选择中，要更新一行，必须对其进行两次搜索，而在第一种选择中，则只需一次.还有其他解决方案吗?我正在使用MS SQL 2000.

What is a better approach? It seems like in the second choice, to update a row, it has to be searched two times, whereas in the first option - just once. Are there any other solutions to the problem? I am using MS SQL 2000.

推荐答案

两者都可以正常工作，但是我通常使用选项2(mssql 2008之前的版本)，因为它的读起来更清晰一些.我也不会在这里强调性能...如果出现问题，可以在exists子句中使用NOLOCK.尽管在开始到处使用NOLOCK之前，请确保已覆盖所有基础知识(索引和大型架构方面的内容).如果您知道每个项目都将更新一次以上，那么考虑选择选项1可能是值得的.

Both work fine, but I usually use option 2 (pre-mssql 2008) since it reads a bit more clearly. I wouldn't stress about the performance here either...If it becomes an issue, you can use NOLOCK in the exists clause. Though before you start using NOLOCK everywhere, make sure you've covered all your bases (indexes and big picture architecture stuff). If you know you will be updating every item more than once, then it might pay to consider option 1.

选项3是不使用破坏性更新.这需要更多的工作，但是基本上每次数据更改时都插入新行(从不更新或从表中删除)，并具有一个视图来选择所有最近的行.如果希望表包含其所有先前状态的历史记录，这很有用，但它也可能会显得过大.

Option 3 is to not use destructive updates. It takes more work, but basically you insert a new row every time the data changes (never update or delete from the table) and have a view that selects all the most recent rows. It's useful if you want the table to contain a history of all its previous states, but it can also be overkill.

这篇关于T-SQL插入或更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！