T-SQL 插入或更新

发布于 2024-08-22 12:21:52 字数 848 浏览 8 评论 0原文

我有一个关于 SQL Server 性能的问题。

假设我有一个表 persons ,其中包含以下列:idnamesurname

现在,我想在此表中插入一个新行。规则如下:

  1. 如果表中不存在id,则插入该行。

  2. 如果id存在,则更新。

我这里有两个解决方案:

第一:

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。

I have a question regarding performance of SQL Server.

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. If id is not present in the table, then insert the row.

  2. If id is present, then update.

I have two solutions here:

First:

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)

Second:

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)

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.

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(5

提笔落墨 2024-08-29 12:21:52

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

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

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.

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.

谈场末日恋爱 2024-08-29 12:21:52

选项 1 似乎不错。但是,如果您使用的是 SQL Server 2008,则还可以使用 MERGE,这对于此类 UPSERT 任务可能表现良好。

请注意,您可能希望使用显式事务和 XACT_ABORT 选项此类任务,以便在出现问题或并发更改的情况下保持事务一致性。

Option 1 seems good. However, if you're on SQL Server 2008, you could also use MERGE, which may perform good for such UPSERT tasks.

Note that you may want to use an explicit transaction and the XACT_ABORT option for such tasks, so that the transaction consistency remains in the case of a problem or concurrent change.

幻梦 2024-08-29 12:21:52

您可以使用 @@RowCount 来查看更新是否执行了任何操作。像这样的东西:

    UPDATE MyTable
       SET SomeData = 'Some Data' WHERE ID = 1
    IF @@ROWCOUNT = 0
      BEGIN
        INSERT MyTable
        SELECT 1, 'Some Data'       
      END

You could just use @@RowCount to see if the update did anything. Something like:

    UPDATE MyTable
       SET SomeData = 'Some Data' WHERE ID = 1
    IF @@ROWCOUNT = 0
      BEGIN
        INSERT MyTable
        SELECT 1, 'Some Data'       
      END
哀由 2024-08-29 12:21:52

我倾向于使用选项1。如果表中有记录,则保存一次搜索。如果没有,你也不会失去任何东西。此外,在第二个选项中,您可能会遇到与锁不兼容相关的有趣的锁定和死锁问题。
我的博客上有更多信息:

http ://sqlblogcasts.com/blogs/piotr_rodak/archive/2010/01/04/updlock-holdlock-and-deadlocks.aspx

I tend to use option 1. If there is record in a table, you save one search. If there isn't, you don't loose anything. Moreover, in the second option you may run into funny locking and deadlocking issues related to locks incompatibility.
There's some more info on my blog:

http://sqlblogcasts.com/blogs/piotr_rodak/archive/2010/01/04/updlock-holdlock-and-deadlocks.aspx

窗影残 2024-08-29 12:21:52

为了更加干燥,我避免将值列表写两次。

begin tran
insert into persons (id)
select @p_id from persons
 where not exists (select * from persons where id = @p_id)

update persons
set name=@p_name, surname=@p_surname
where id = @p_id

commit

namesurname 列必须可为空。

该交易意味着其他用户将永远不会看到“空白”记录。

编辑:清理

Aiming to be a little more DRY, I avoid writing out the values list twice.

begin tran
insert into persons (id)
select @p_id from persons
 where not exists (select * from persons where id = @p_id)

update persons
set name=@p_name, surname=@p_surname
where id = @p_id

commit

Columns name and surname have to be nullable.

The transaction means no other user will ever see the "blank" record.

Edit: cleanup

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文