SQL Server中的身份 [英] Identity in sql server

问题描述

我有这样的唱片

Hi i have record like this

sno    name        mail
1       vijay     [e-mail]
2       ajith     [e-mail]
3       vivek     [e-mail]

在sno中，我已应用了身份，因此它将自动加1

我的问题是现在我删除2条记录意味着输出是

in sno i have applied identity so it will increment by 1 automatically

my question is now i delete 2 record means the output is

sno    name        mail
1       vijay     [e-mail]
3       vivek     [e-mail]

但我需要这样的输出

but i need the output like this

sno    name        mail
1       vijay     [e-mail]
2       vivek     [e-mail]

第三个记录标识字段需要更改为2

the 3 rd record identity field need to change as 2

推荐答案

这是设计使然，系统会维护IDENTITY列，并且当您删除记录，这是为了在其他地方维护引用完整性.

如果您需要递增值而没有漏洞，请尝试添加自己的列并保持自己的值.

This is by design, IDENTITY columns are maintained by the system and the value does not change when you delete records, this is to maintain referential integrity elsewhere.

If you need incrementing values without holes try adding your own column and maintaining the values your self.

引用:

http://msdn.microsoft.com/en-us/library/ms186775.aspx [ ^ ]

Refer:

http://msdn.microsoft.com/en-us/library/ms186775.aspx[^]

首先，我有一个问题.为什么需要更新标识列.标识列由SQL Server本身维护.如果您需要一个报告，例如在srno中显示记录而不破坏序列，则可以尝试以下代码.

First I have a question. Why do you need to update the identity columns. Identity columns are maintained by SQL Server itself. If you need a report like showing the records in srno without breaking the sequence, you can try below code.

Select Row_Number() over ( order by SrNo) as SrNo,Name,Mail from yourTableName

这篇关于SQL Server中的身份的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！