一直想寫這篇文章，因為實在太多人曾經問過，如何新增資料時自行產生一個「自動編號」的欄位，重點是這哥們想要自己寫，不想要依賴 SQL Server原生的 identity屬性完成。
所以本期就來探討這樣的問題，分析一下利弊給各位做個參考。

自行產生連續的數字欄位

首先，先解釋一下，在 SQL Server中， identity為何被稱為「識別屬性」而不是稱為「自動編號」（雖然它的本體是在做自動編號），主要是當我們在設計資料庫的時候，必須指定一個主索引鍵，也就是 Primary key，而這個 primary key必須有幾個條件，最基本的條件是不能重複，其次是容易取得。

而大多數的人，都以為 primary key只要不重複就好，事實上容易取得也相當重要，所以很多時候在設計資料庫時不是只要關心不重複即可，適時的使用替代鍵也是相當重要的，而 identity就是用在這個時候，它不會重複而且它不需要輸入值，它由系統自動產生，所以如果你是要以 identity識別屬性來取代原本的 primary key，是非常建議的。

既然是識別屬性，所以它並不會幫你維護數字的「連續性」，換句話說，如果你有五筆資料，分別有1、2、3、4、5這幾個識別屬性，當你把3這筆資料刪除後，再新增一筆資料，最後的結果會是出現6這個識別屬性值，在實務上也不會要求資料的「連續性」，因為識別屬性原本就不是在處理這方面的事情。

如果不用識別屬性，我們還有哪些選擇？我來介紹一下，順便實際測試就知道有多慘烈。

不過在測試之前，我們先將該設定的資料表、環境先產生出來：

建立一個資料表名為 table1，設定 a的資料型別為整數，先不設定 identity，之後隨時視情境更改。

新增資料時取出最大值，再將值加1填入

撰寫一個 stored procedure，程式內容是輸入 N筆（參數），然後每新增一筆，就從現行資料表中取出最大值加一寫回，並且記錄起始時間與結束時間好計算所需時間，在一開始甚至設定開始新增資料的時間，程式如下：

先用單一連線測試這隻程式，輸入下面語法：

exec usp_ins_table1_maxvalue 3000,'session1'

一次輸入3000筆資料，看要花費多久時間，結果如下：

總共花了301秒。

接下來，開三個連線，分別執行下列程式：

exec usp_ins_table1_maxvalue 1000,'session1'

exec usp_ins_table1_maxvalue 1000,'session2'

exec usp_ins_table1_maxvalue 1000,'session3'

最後得到的時間為下：

三個連線的執行時間都是相同，不過我們來看，若用此種做法，會不會有資料重複的問題，所以我們使用下面的語法：

select a from table1

group by a

結果得到如下的結果：

好的！也就是說，會有重複的值會被產生出來（其實這個原本就是可預料到的），再進一步的用這個指令去分析，哪個連線的值造成重複：

select * from (

select count(*) as counts ,a from table1

group by a

having count(*) >1) as b

inner join table1 as c

on b.a = c.a

會得到如下的結果：

是的，有兩個連線同時取得了最大值，再將這個最大值加一填入，就會變成多個連線會取出相同值的結果，其實一點也不意外。

為此，我們把資料表改成如下：

是的，也就是把a欄位改成是 identity，再測試一次（當然 stored procedure也要修改，因為a這個欄位無需自行輸入）：

結果再用 group by的句法檢查資料是否有重複，得到的情況如下：

很好，如此一來，使用 identity資料就不會重複了，那麼只要把原來的 stored procedure改成如下的寫法，你就可以自行控制客製化的自動編號了：

insert table1 (b,c) values (format(@maxvalue,N'\C00000'),@from)

format是 SQL Server 2012的新函數，可以利用它產生你想要的自訂自動編號，最後得到的結果如下：

所以結論是，如果要客製化文字版本的自動編號，記得此種 MAX的手法還是請你乖乖的建立一個 identity欄位吧！

用一個資料表記錄目前連續編號的最大值

第二種做法，是在一個資料表記錄目前此資料表的自動編號最大值，取得之後加1將資料新增，再利用 update的方式修改回該資料表。

為了達到設定目的，同樣的我們也是準備一個資料表，記錄目前連續編號的最大值，如下：

目前資料表 sequence_table，記錄目前的最大值；當然，打從心裡我就不是很支持這樣的做法，因為它一定也會面臨和剛才 maxvalue一樣的問題，也就是同樣會取得重複值，不過還真的有資料庫系統是這麼做的，例如sqlite這個資料庫，當你設定「自動編號」時，它就會幫你產生一個資料表，用來記錄資料連續的狀況，如下圖：