两种方法:
一种是用Truncate
TRUNCATE
TABLE
name
可以删除表内所有值并重置标识值
二是用DBCC CHECKIDENT
DBCC
CHECKIDENT (
'
table_name
'
, RESEED, new_reseed_value)
如dbcc checkident ("bc_pos",reseed,1)即可,但如果表内有数据,则重设的值如果小于最大值可能会有问题,这时可以用 dbcc checkident("bc_pos",reseed)即可自动重设值。
070805更新:VB的函数
'
'重置表的自动编号(标识列)的初始值
' ' 正确 返回 0
' ' 错误 返回 -1
Public Function reGenID(ByVal Tbl As String , Optional ByVal IDFld As String ) As Integer
Dim SQL As String , ret
On Error Resume Next
Conn.BeginTrans
SQL = " DBCC CHECKIDENT ( " & Tbl & " , RESEED, 0) "
Conn.Execute SQL
SQL = " DBCC CHECKIDENT ( " & Tbl & " , RESEED) " ' '进行修复,如果表中还有数据,则修复为正确的值!
Conn.Execute SQL
If Err.Number = 0 Then
Conn.CommitTrans
ret = 0
Else
Conn.RollbackTrans
ret = - 1
End If
Err.Clear
On Error GoTo 0
reGenID = ret
End Function
' ' 正确 返回 0
' ' 错误 返回 -1
Public Function reGenID(ByVal Tbl As String , Optional ByVal IDFld As String ) As Integer
Dim SQL As String , ret
On Error Resume Next
Conn.BeginTrans
SQL = " DBCC CHECKIDENT ( " & Tbl & " , RESEED, 0) "
Conn.Execute SQL
SQL = " DBCC CHECKIDENT ( " & Tbl & " , RESEED) " ' '进行修复,如果表中还有数据,则修复为正确的值!
Conn.Execute SQL
If Err.Number = 0 Then
Conn.CommitTrans
ret = 0
Else
Conn.RollbackTrans
ret = - 1
End If
Err.Clear
On Error GoTo 0
reGenID = ret
End Function