通过 SQL 存储过程更新具有连续数字的表的标识列 [英] Updating Identity Column of a table with consecutive numbers through SQL Stored Procedure

问题描述

从表中删除重复记录后，我想用从 1 开始的连续编号更新表的标识列.这是我的表详细信息

After deleting the duplicate records from the table, I want to update Identity column of a table with consecutive numbering starting with 1. Here is my table details

id(identity(1,1)),
EmployeeID(int),
Punch_Time(datetime),
Deviceid(int)

我需要通过存储过程执行此操作.当我在存储过程中尝试以下语句时

I need to perform this action through a stored procedure. When i tried following statement in stored procedure

DECLARE @myVar int
        SET @myVar = 0
        set identity_insert TempTrans_Raw# ON
        UPDATE  TempTrans_Raw# SET  @myvar = Id = @myVar + 1
        set identity_insert TempTrans_Raw# off

出现错误，例如...无法更新标识列Id"任何人都请建议如何使用从 1 开始的连续编号更新该表的 Identity 列.

gave error like...Cannot update identity column 'Id' Anyone please suggest how to update Identity column of that table with consecutive numbering starting with 1.

推荐答案

IDENTITY 关键字用于生成可以与 PRIMARY KEY 约束结合使用以获得技术密钥的密钥.这些键是技术，它们用于链接表记录.它们应该没有其他含义(例如排序顺序).SQL Server 不保证生成的 ID 是连续的.但是，他们确实保证您按顺序获得它们.(所以你可能会得到 1, 2, 4, ...，但永远不会得到 1, 4, 2, ...)

The IDENTITY keword is used to generate a key which can be used in combination with the PRIMARY KEY constraint to get a technical key. Such keys are technical, they are used to link table records. They should have no other meaning (such as a sort order). SQL Server does not guarantee the generated IDs to be consecutive. They do guarantee however that you get them in order. (So you might get 1, 2, 4, ..., but never 1, 4, 2, ...)

以下是 IDENTITY 的文档:https://msdn.microsoft.com/de-de/library/ms186775.aspx.

Here is the documentation for IDENTITY: https://msdn.microsoft.com/de-de/library/ms186775.aspx.

我个人不喜欢保证生成的 ID 是有序的.除了提供对记录的引用之外，技术 ID 应该没有任何意义.您可以依赖订单，但如果订单是您感兴趣的信息，您应该存储我认为该信息(例如以时间戳的形式).

Personally I don't like it to be guaranteed that the generated IDs are in order. A technical ID is supposed to have no meaning other then offering a reference to a record. You can rely on the order, but if order is information you are interested in, you should store that information in my opinion (in form of a timestamp for example).

如果您希望有一个数字告诉您某条记录是第 5 条或第 16 条记录，或者其他任何记录，您可以使用 ROW_NUMBER 函数随时获取该数字.所以不需要生成和存储这样的连续值(当涉及到表上的并发事务时，这也会很麻烦).以下是获取该号码的方法:

If you want to have a number telling you that a record is the fifth or sixteenth or whatever record in order, you can get always get that number on the fly using the ROW_NUMBER function. So there is no need to generate and store such consecutive value (which could also be quite troublesome when it comes to concurrent transactions on the table). Here is how to get that number:

select
  row_number() over(order by id),
  employeeid,
  punch_time,
  deviceid
from mytable;

说了这么多；永远不需要更改 ID.如果您觉得需要，这是不适当的桌子设计的标志.

Having said all this; it should never be necessary to change an ID. It is a sign for inappropriate table design, if you feel that need.

这篇关于通过 SQL 存储过程更新具有连续数字的表的标识列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！