如何在SQL SERVER中更新标识列值？ [英] How to update identity column value in SQL SERVER?

问题描述

您好，



如何在SQL SERVER中更新标识列值？

我正在使用此查询获取更新标识列值sql server。

Hello,

How to update identity column value in SQL SERVER?
I am using this query for update identity column value in sql server.

set IDENTITY_INSERT tableSalesQuotation ON
update tableSalesQuotation
set
SNO=SubQuery.SNO
from
(SELECT Row_Number() OVER (ORDER BY SNO) as SNO
FROM tableSalesQuotation
) SubQuery
set IDENTITY_INSERT tableSalesQuotation OFF

但它收到了错误。

无法更新标识列'SNO'



我必须更新现有的标识列值，这是一个申请需要。



请帮帮我。



先谢谢。



Ankit Agarwal

软件工程师

but it's getting error.
Cannot update identity column 'SNO'

I have to update existing Identity Column Value, it's a application need.

please help me.

Thanks in Advance.

Ankit Agarwal
Software Engineer

推荐答案

这是一个非常非常糟糕的主意。

IDENTITY字段的整个概念是值唯一，而不是它是顺序 - 这意味着当添加和删除行时值不会被重用，但洞会出现在以前的值中。如果你更新它们以给出连续值，那么在30秒内可能会再次出现漏洞！



它也是一件危险的事情;你有其他表引用这个值，你可以通过这样做来破坏你的数据完整性。而且......如果你有多个用户怎么办？当这样的数据更新时，它可能会导致一些可怕的问题...



请不要这样做。如果您需要序列号，那么请使用ROW_NUMBER提供它，但不要更新IDENTITY值以提供顺序结果 - 严重的不是它的用途。



如果你仍然认为你真的，真的，应该这样做，那么一个简单的谷歌会找你怎么做......但是说真的，如果必须的话，你的设计就会出现问题！

It's a very, very poor idea.
The whole idea of an IDENTITY field is that the value is unique, not that it is sequential - which means that as rows are added and removed the values are not reused, but "holes" appear where values used to be. If you update them to give sequential values, then in 30 seconds there could be holes again!

It is also a dangerous thing to do; of you have other tables that refer to this value, you could well destroy your data integrity by doing this. And... What if you have multiple users? When data like this is updated, it can cause some horrible problems...

Please, don't do it. If you need sequential numbers, then use ROW_NUMBER to provide it, but don't update IDENTITY value to give sequential results - it is seriously not what it is there for.

If you still think you really, really, should do this, then a simple Google will find you how to do it... But seriously, if you have to, then there is a problem with your design!

这篇关于如何在SQL SERVER中更新标识列值？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！