哪一个是最佳范围标识或在SQL Server中获取最大ID？ [英] Which one is best scope identity or get the maximum id in SQL server?

问题描述

我在表中插入值，插入后我也获得范围标识值，但同时该特定记录也从表中删除。会是什么原因？



哪一个是获得表范围标识的最大id或在sql server中获得最大id的最佳选择



我尝试了什么：



在一种情况下，当插入一列值为空时，数据插入，但它被自动删除

解决方案

永远不要使用最大值来假设唯一性的ID - 在多用户环境中，它会给出间歇性的问题锻炼，更不用说修复了。始终使用作用域标识，但将其作为INSERT的一部分 - 即编写存储过程以执行插入操作，并返回ID值或错误代码。永远不要尝试将它作为C＃代码中的两个命令。

替代方案（我更喜欢）根本不使用IDENTITY字段，而是使用GUID ID代替，通过UNIQUEIDENTIFIER列，在执行插入时从C＃代码中分配它。这样，您可以完全控制值，而无需查找插入的值。我只使用IDENTITY字段来提供我不关心的唯一值，并且不会交叉引用，例如日志表ID。



至于你的删除行问题 - 没有相关的代码片段，我们不能有任何想法！ ：laugh：

检查表属性中是否存在约束。假设您不允许在特定列中使用空格，并且在查询中将空白作为值传递，则不会插入记录。



参考 - < a href =http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/> SQL SERVER - @@ IDENTITY vs SCOPE_IDENTITY（）vs IDENT_CURRENT - 检索最后插入的记录标识 - 使用Pinal Dave访问SQL权限 [ ^ ]。

I am inserting values into table, after inserting i am getting scope identity value also, but at the same time that particular record also deleted from table. what would be the reason?

Which one is best option to get maximum id of table scope identity or get the maximum id in sql server

What I have tried:

In one scenario, when inserting one column value is empty, data is inserted, but its deleted automatically

解决方案

Never use maximum value to assume an ID for uniqueness - in a multiuser environment, it will give intermittent problems which are a pain to work out, much less fix. Always use scope identity, but make it part of the INSERT - i.e. write a stored procedure to do the insert, and return either the ID value or an error code. Never try to do it as two commands from your C# code.
The alternative (which I prefer) is not to use IDENTITY fields at all, but to use GUID ID's instead, via a UNIQUEIDENTIFIER column, and assign it from your C# code when you do the insert. That way, you are in total control of the values and don't need to "look up" what value was inserted. I only use IDENTITY fields to provide unique values that I don't care about, and won't cross reference such as log table IDs.

As for your "deleted rows" problem - without the relevant code fragments we can't have any idea! :laugh:

Check out if there are constraints in the table properties. Suppose you are not allowing blanks in a particular column and in the query you are passing blank as a value, then that won't insert the record.

Refer - SQL SERVER - @@IDENTITY vs SCOPE_IDENTITY() vs IDENT_CURRENT - Retrieve Last Inserted Identity of Record - Journey to SQL Authority with Pinal Dave[^].

这篇关于哪一个是最佳范围标识或在SQL Server中获取最大ID？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！