我在accdetails表中的accno上应用标识列 [英] i am applying a identity column on accno in accdetails table

问题描述

当我从accdetails中删除记录时.当我在accdetails中插入新记录时，我希望重用已删除记录的accno.是否可以在Win窗体中进行编程


thanq

when i am deleting the record from the accdetails.i want the accno of the deleted record to be reused when i insert a new record in accdetails.is it possible in the programming from the win forms


thanq

推荐答案

可以使用以下命令重置Identity列的种子值，只需从表中获取maxValue并将Identity列值重置为max + 1. ，请点击链接以了解更多信息.

You can reset the seed value of the Identity column, just fetch the maxValue from the table and reset the Identity column value to max + 1 using the following command, click the link to learn more.

DBCC CHECKIDENT (Person.Contact, reseed, 100);

http://sqlwithmanoj.wordpress.com/2011/07/08/reseed -tables-identity-column-value/ [

http://sqlwithmanoj.wordpress.com/2011/07/08/reseed-tables-identity-column-value/[^]

首先，请阅读以下内容:

http://stackoverflow.com/questions/4288089/reuse-identity-value-after-删除行 [ ^ ].

接下来，恐怕使用身份列对于您的情况不是一个好的指导.如果您需要自动编号并重复使用已删除的编号，则可以执行以下操作:

1)自己维护下一行ID"计数器-数据库中可以有一个表，其中包含ID计数器的最新值.

2)维护一个单独的已删除ID表.您可以从数据表上的AFTER DELETE触发器填充它.

3)添加新行时，首先尝试从已删除的ID表中出列"最低ID，然后如果不成功，则增加ID计数器(您可以在INSTEAD OF INSERT触发器中完成此操作).

但是，上述解决方案有两件事要牢记.

1)如果对数据表同时存在多个INSERT，则必须正确处理增加计数器和使丢弃的ID出队的并发性.如果您使用SQL Server 2005及更高版本，则可以为此使用CTE(WITH ... UPDATE和WITH ... DELETE语句－在这里:http://rusanu.com/2010/03/26/using-tables-as-queues/ [

First, read this:

http://stackoverflow.com/questions/4288089/reuse-identity-value-after-deleting-rows[^].

Next, I''m afraid that using identity column is not a good direction for your scenario. If you need automatic numbering with reuse of deleted numbers, you can do, for example, the following:

1) Maintain "next row ID" counter by yourself -- you can have a table in the database that will hold the latest value of the ID counter.

2) Maintain a separate table of dropped IDs. You can populate it from an AFTER DELETE trigger on your data table.

3) When adding a new row, first try to "dequeue" lowest ID from the table of dropped IDs, then if unsuccessful, increment the ID counter (you can do it in an INSTEAD OF INSERT trigger).

There are two things to bear in mind with the above solution, though.

1) If there are multiple simultaneous INSERTs to data table, you have to correctly handle concurrency of incrementing the counter and dequeueing dropped IDs. If you use SQL Server 2005 and higher, you can use CTEs for this purpose (WITH...UPDATE and WITH...DELETE statements -- look here: http://rusanu.com/2010/03/26/using-tables-as-queues/[^]).

2) If your application produces a tough load by issuing miltiple simultaneous INSERTs and DELETEs on the data table, the dropped IDs table can become a performance bottleneck. This solution would work best if you have much less DELETEs than INSERTs.

如果accno 是自动递增字段，则除了定期执行维护工作外别无选择.这意味着您应按所有记录ID之间没有间隙的方式顺序重置所有记录ID，然后使用 Pandya Anil 为您提供的命令将种子编号设置为最大的标识号.
每次您感觉到差距越来越大时都要重复这项工作.

如果accno 不是自动递增字段，则可以在另一个表中累积已删除的ID，并且在插入新记录时，首先从已删除的ID中选择ID，如果没有记录，则创建下一个最大的ID并将其用于插入. >
最后，我邀请您考虑可以用作自动增量字段的BigInt 数据类型.它有8个字节(64位)，因此它可以存储的最大数字为:18,446,744,073,709,551,616
这意味着您可以在1 billion day期间每天插入18 billion records.
您是否真的认为拥有如此巨大的数字空间可能会遇到问题?

If accno is an auto increment field you have no choice other than doing a maintenance job periodically. It means that you should reset all of the record IDs sequentially in a way that there is no gap between them and then set the seed number with command that Pandya Anil have provided for you to the largest identity number.
And repeat this work every time that you feel the number of gaps are getting very large.

If accno is not an auto increment field then you can accumulate deleted IDs in another table and when inserting new records first pick IDs from deleted ones and if no record was there create the next largest one and use it for insertion.

And finally I invite you to consider the data type of BigInt which can be used as an autoinrement field. It has 8 bytes (64 bits) so the largest number that it can store is : 18,446,744,073,709,551,616
It means that you can insert 18 billion records every day for a 1 billion day period.
Do you really think that you may encounter a problem with having this enormous number space ?

这篇关于我在accdetails表中的accno上应用标识列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！