SQL SERVER ->> IDENTITY相關函數

標籤：

IDENTITY函數 -- 只能用在SELECT INTO語句中，用於在插入資料的時候類比IDENTITY屬性的作用產生自增長值。

SELECT IDENTITY(int, 1,1) AS ID_Num  INTO NewTable  FROM OldTable;  

 

IDENT_INCR 函數 -- 返回表的自動成長值，比如我們希望每次增長1，那這個函數就會返回1.

USE AdventureWorks2012;  GO  SELECT TABLE_SCHEMA, TABLE_NAME,      IDENT_INCR(TABLE_SCHEMA + ‘.‘ + TABLE_NAME) AS IDENT_INCR  FROM INFORMATION_SCHEMA.TABLES  WHERE IDENT_INCR(TABLE_SCHEMA + ‘.‘ + TABLE_NAME) IS NOT NULL;  

 

IDENT_SEED 函數 -- 自動成長列的初始種子值

USE AdventureWorks2012;  GO  SELECT TABLE_SCHEMA, TABLE_NAME,      IDENT_SEED(TABLE_SCHEMA + ‘.‘ + TABLE_NAME) AS IDENT_SEED  FROM INFORMATION_SCHEMA.TABLES  WHERE IDENT_SEED(TABLE_SCHEMA + ‘.‘ + TABLE_NAME) IS NOT NULL;  GO  

 

IDENT_CURRENT -- 表的IDENTITY列的當前自動成長值

USE AdventureWorks2012;  GO  SELECT TABLE_SCHEMA, TABLE_NAME,      IDENT_CURRENT (TABLE_SCHEMA + ‘.‘ + TABLE_NAME) AS IDENT_SEED  FROM INFORMATION_SCHEMA.TABLES  WHERE IDENT_CURRENT (TABLE_SCHEMA + ‘.‘ + TABLE_NAME) IS NOT NULL;  GO  

 

 

SCOPE_IDENTITY() -- 當前模組內（預存程序）產生的最大自動成長值

 

@@IDENTITY -- 當前會話內產生的最大自動成長值

 

SQL SERVER ->> IDENTITY相關函數