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

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

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

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

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

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开始。

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

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 keword用于生成一个可与PRIMARY KEY约束组合使用以获取技术密钥的密钥。 这些键是技术性的 ，它们用于链接表格记录。 它们应该没有其他含义（例如排序顺序）。 SQL Server不保证生成的ID是连续的。 然而，他们确保您按顺序获得它们。 （所以你可能会得到1，2，4，...，但从来没有1，4，2，...）

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

就我个人而言，我不喜欢它保证生成的ID是有序的。 一个技术ID应该没有其他意义，然后提供一个记录的参考。 您可以依靠订单，但如果订单是您感兴趣的信息，则应按我的意见存储该信息（例如，以时间戳的形式）。

如果你想让一个数字告诉你记录是第五或第十六或者任何顺序的记录，你可以使用ROW_NUMBER函数随时获得该数字。 所以不需要生成和存储这样的连续值（当涉及到表上的并发事务时，这也可能是相当麻烦的）。 以下是获取该数字的方法：

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

说完了这一切; 应该永远不需要更改ID。 如果您觉得有需要，这是表格设计不合适的标志。

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, ...)

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

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).

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;

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.