標籤: identity

分類
Database

找出SQL Server中的identity欄位值

一般在 SQL Server 中, 若要找某 table 中的 identity 最後值(最大值或目前值), 可以使用以下指令:

dbcc checkident('table_name', NORESEED)

可以參考之前的這篇文章: SQL Server的Identity欄位使用/複製/重設 – https://diary.tw/archives/457

不過, 若是 table 很多, 又想一次性的將各 table 中的 identity 欄位最後值找出, 可以利用系統資料表: sys.identity_columns (2005, 2008, 2008R2都有) 來查找, 配合 sys.objects 表, 可以一次將 table, column, 最後值(last_value) 查找出來, 如下:

select b.name, a.name, a.last_value from sys.identity_columns a inner join sys.objects b on a.object_id=b.object_id

這樣可以利用一個指令就將該資料庫中的所有資料表含有 identity 欄位的最後值, 若是只需要使用者自行定義的 table (不要含系統表), 可以多加上 b.type=’U’ 來進行過濾.

參考資料:
http://technet.microsoft.com/zh-tw/library/ms176057.aspx
http://msdn.microsoft.com/en-us/library/ms187334.aspx

分類
Database

SQL Server取得identity的目前值及下一個

一般來說, 俱有 identity 屬性欄位的 table 的目前值可以很容易地利用 max 函數來取得. 不過, 也很有可能 max 該筆資料已被刪除了, 所以利用 max 查找出來的結果不正確.

但又若是要找出下一個 identity 的值呢? 就一定得知道漸增量 (increment) 為何了, 否則是沒有辦法計算出來的.

請先參考這篇文章: SQL Server的Identity欄位使用/複製/重設 https://diary.tw/archives/457 , 這篇文章中也有提到有關取得目前的 identity 值的方式, 是利用 dbcc checkident(‘table_name’, NORESEED) 來取回, 不過取回來的是文字訊息 (text message), 而不是很方便程式化, 若要滿足前面的需求, 包含取回 identity 目前值及下一個, 則有現成的函數可用:

  1. IDENT_CURRENT: 取出 identity 欄位的目前值
  2. IDENT_INCR: 取出 identity 欄位的漸增值
  3. IDENT_SEED: 取出 identity 欄位的起始種子

使用方式如下:

select IDENT_CURRENT('table_name')

 

這樣可以取出該 table_name table 中的 identity 欄位的目前值, 而下一個呢? 可以利用:

select IDENT_CURRENT('table_name') + IDENT_INCR('table_name')

 

這樣就可以順利取出來, 也就是拿目前值加上漸增值. 而且回傳的結果是一個 resultset 的方式將值傳回, 很方便應用於程式化的需求.

不過無論如何, 也請特別注意, 這種應用需求, 並非是要 developer 將值取出後再塞回去的, 因為 identity 欄位是由系統維護的, 而不是 developer (或說程式) 維護的, 所以這樣取出是可以拿來觀察, 而不是要塞回去用的, 請特別注意一下.

參考資料: http://social.msdn.microsoft.com/forums/zh-TW/240/thread/ba6ff915-99be-42ee-831c-21d7532f3c47/

分類
Database

SQL Server 在資料庫中找出有 identity 的欄位及table

SQL Server 2005 中, 若想要找出所有有 identity 的欄位及 table , 可以利用系統表, sys.columns 及 sys.objects 來個來查詢.

其中的 sys.columns 的 is_identity 就可以判定該 column 是否是 identity 欄位. 所以在 SQL Server 2005 要判定這些 table 及欄位時, 可以使用以下查詢:

select o.name, c.name
from sys.columns c inner join sys.objects o 
on c.object_id=o.object_id 
where c.is_identity = 1 and o.type='U'

當然, 應該會有人問, 那 SQL Server 2000 怎麼辦呢? 因為 SQL Server 2000 並沒有像 SQL Server 2005 有 sys.columns , 而 sys.objects 還可以用 sysobjects 取代, 但麻煩的是那個在 sys.columns 裡的 is_identity 欄位, 要怎麼找出有 identity 的欄位呢? 可以利用 syscolumns 中的 status 欄位, 其中的 0x80 就是 identity 屬性, 詳情可以參考這篇: http://msdn.microsoft.com/en-us/library/aa260398(SQL.80).aspx 其中的 status 欄位.

根據這樣的資料, 我們就不難組合出 SQL Server 2000 查找出有 identity 欄位的 table 了, 如下:

select o.name, c.name
from syscolumns c inner join sysobjects o on c.id=o.id 
where c.status & 0x80 = 0x80 and o.type='U'

整理出來的結果就會是 table / column , 很方便大家能快速地將資料表及欄位含有 identity 屬性的部分找出來.

ps. sysobjects 中的 type=’U’ 是指 user table .

繼續閱讀:
syscolumns: http://msdn.microsoft.com/en-us/library/aa260398(SQL.80).aspx
sysobjects: http://msdn.microsoft.com/en-us/library/aa260447(SQL.80).aspx
SQL Server的Identity欄位使用/複製/重設: https://diary.tw/tim/65

分類
Database

當identity到達該欄位上限時…

今天在整理資料庫時, 發現有個 table 的 identity 欄位, 即將到達上限囉. 於是便來著手研究一下會發生什麼問題.

LAB 狀況 Microsoft SQL Server 2005, 某 table 的 identity 欄位為 tinyint, 並進行 insert 測試.

狀況 1, tinyint 為 (0~255), 若於資料已屆 255 時, 再 insert 資料會發生下面錯誤 (exception):

“轉換 IDENTITY 到資料類型 tinyint 時發生算術溢位錯誤。
發生算術溢位。”

狀況 2, 先將前面 < 100 的資料刪除, 再利用 dbcc checkident(‘table_name’, RESEED, 20) 指令執行後, 重置 identity 值 (下一筆會由 21 開始), 再進行 insert, 則可以正常 insert (dbcc checkindent 資料可以看這篇文章: SQL Server的Identity欄位使用/複製/重設 )

狀況 3, 繼續再進行 insert, 直到 99 時, 再繼續 insert 會發生 primary key violation 的錯誤.

由以上狀況可知, 一旦發生 identity 到該欄位上限時, 將會發生無法寫入資料的狀況, 而且是以算術溢位錯誤的方式發生, 一旦造成, 會有極嚴重的問題, 一定得在發生之前規劃好, 以免造成問題!

分類
Database 懶得分類

SQL Server的Identity欄位使用/複製/重設

在資料庫中, 常用的一個流水編號通常會使用 identity 欄位來進行設置, 這種編號的好處是一定不會重覆, 而且一定是唯一的, 這對table中的唯一值特性很重要, 通常用來做客戶編號, 訂單編號等功能, 以下介紹關於此種欄位常用方式及相關技術.

後面的範例表皆以此表為建立:

CREATE TABLE products (id int IDENTITY PRIMARY KEY, product varchar(40))
閱讀全文 “SQL Server的Identity欄位使用/複製/重設”