如何从DB表中获取下一个ID而没有IDENTITY或GUID列 [英] How to get the next ID from DB Table without an IDENTITY or GUID Column

问题描述

我有一张表 INT PrimaryKey 列。我不想将 GUID 或 IDENTITY 用作主键。



我想知道在不使用 IDENTITY 的情况下获得下一个ID的最佳方法是什么？或 GUID 作为表中的主键列。我不介意在表中使用 GUID 或 IDENTITY ，除非它的主键列。



我必须找到下一个可用的ID（即获取MAX ID并将其递增1），然后使用：

I have a table with INT PrimaryKey Column. I do not want to use GUID or IDENTITY as Primary Key.

I wanted to know what is the best possible way to get the next ID without using an IDENTITY or GUID as Primary Key column in the table. I do not mind using GUID or IDENTITY in the table unless its the Primary Key column.

I have to find the next available ID (i.e get the MAX ID and increment it by 1),and use that:

Select @id=ISNULL(max(AlbumId)+1,1) from Albums

但是，我想阻止其他应用程序在我这样做时插入到表中，这样我们就不会有任何问题。



注意：我已通过此 [ ^ ]回答并且无法正确理解。有人会善意地向我解释他们在做什么吗？我可以使用相同的技术吗？



谢谢。

However, I want to prevent other applications from inserting into the table when I''m doing this so that we don''t have any problems.

NOTE:I have gone through this[^] answer and could not understand it properly. Would anyone be kind enough to explain me what is it they are doing?? And can i use the same technique??

Thanks.

推荐答案

好像你''如果最好的方法是使用Identity字段并让Insert语句在插入后从SQL Server返回SCOPE_IDENTITY（）变量，那么在不使用IDENTITY的情况下找到最好的方法会遇到很多麻烦。这将确保所有客户获得与其特定插入相关的正确ID。



如果由于某种原因需要努力，那么一种方法是制作另一个持有ID的表。该表可以有2个字段：New_Id和Random



New_Id字段可以是唯一的自动递增整数

随机字段可以是a varchar（32）



您的应用程序可以生成一个32个字符的随机字符串并将其插入表中。然后为该字符串选择New_Id。通过这种方式，应用程序将始终为他们正在做的事情获得正确的ID，而不会相互干扰。



我绝不会建议使用IDENTITY而不是我认为它确实回答了你的问题。



祝你好运！

It seems like you''re going through a lot of trouble to find "the best possible way" without using IDENTITY when the best possible way is to use an Identity field and have the Insert statement return the SCOPE_IDENTITY() variable from SQL Server after your insert. This will insure that all clients get the correct ID related to their specific insert.

If you need to do it the hard way for some reason, one approach is to make another table to hold ID''s. The table can have 2 fields: New_Id and Random

The New_Id field can be a unique auto-incremented integer
The Random field can be a varchar(32)

Your application can generate a random string of 32 characters and insert it into the table. Then select the New_Id for that string of characters. This way the applications will always get the correct Id for what they''re doing without interfering with each other.

I would never suggest doing this over using IDENTITY but I think it does answer your question.

Good Luck!

这篇关于如何从DB表中获取下一个ID而没有IDENTITY或GUID列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！