@@IDENTITY
|
SCOPE_IDENTITY
|
IDENT_CURRENT
|
IDENT_SEED
|
IDENT_INCR
|
Any table
Current session
Any scopes.
|
Any table
Current session
Current scope.
|
Specific table
Any session
Any scope.
|
Returns the
original seed value
If we re-seed using
DBCC CHECKIDENT
won't change the
value.
|
Returns the
increment value
specified during
the creation of an
identity column in a
table or view
that has an identity
column.
|
Table or View |
Table or View |
Table or View |
|
|
If user truncates or
drop the table
also, it
will return the last
identity value
generated in the
current session |
If user truncates or
drop the table also,
it will return the last
identity value
generated in the
current session |
If user truncated
the table it will
return the Ident_Seed
value.
It return null
when user drop
the table. |
|
A
scope is a module: a stored procedure, trigger, function, or batch.
Each query window in Query Analyzer is a
session.
How to get all the session running in the server?
EXEC sp_who 'test';
How to check the current session
SELECT @@SPID SessionID,
USER_NAME() AS [User_Name] ,
SUSER_NAME() AS [Login identification name of the user.],
CURRENT_USER AS [Name of the current user] ,
SYSTEM_USER AS [System_user],
SESSION_USER AS [Session_user],
USER AS [User]
IDENT_SEED
- DBCC CHECKIDENT ('dbo.IdentityTest', RESEED, 10)
- Returns the original seed value, if we re-seed using DBCC CHECKIDENT.
- IDENT_SEED will return the seed value created for the Idetntiy column( when table creation or Alteration).
- It won't changed by DBCC CHECKIDENT ('dbo.IdentityTest', RESEED, 10)
References
RESED Identity Column
SQLAuthority