[個人筆記] Character-Based型態的資料在SQL裡面儲存的Columns要選用什麼DataType？
之前我曾經寫過一篇[個人筆記]string在MS SQL裡面要選用什麼型態做儲存? 對於字串於MS-SQL的處理做了一個粗略的紀錄，近來同事問了一個關於為什麼有些SQL處理需要在字串前面加大寫N作為前置字元(prefix)，為何有些地方有加N與否好像沒有影響，但是有些又非加N不可？其實簡單的來說，它是告知SQL系統你所輸入的字串內容須轉為Unicode，使用2Bytes來處理。
一般來說對於西文字元(例如英文及阿拉伯數字)，用一個位元組來儲存就足夠了，但是對於東方文字字元(例如中文及日文)，就需要兩個位元組來儲存，而Unicode 為了統一、規範、方便、相容，就規定西文字元也用兩個位元組來儲存。
但是為何有時候有加跟沒加都一樣呢？其實原因在於當初欄位如果設定為Unicode (NCHAR、NVARCHAR、NTEXT)類的資料型態，則處理的時候會自動轉換。
declare @status nvarchar(20)
select @status = N'stopped'
select @status = 'stopped'所以在上面的程式碼中，第二行與第三行的處理結果是相同的，因為status變數類型設定為NVARCHAR(Unicode Support)。
但是在某些情況下就非加不可，例如sp_executesql 的參數不能自動轉換，所以需要加 N 了。
在這個時候又要探討如果這麼麻煩，乾脆全部字串欄位都選用Unicode類型好了？底下有個比較表：
|
CHAR |
CHAR支援固定長度的字串，最長為8,000個characters，適用於固定長度的資料，例如身分證字號、書本ISBN等資料類型，當輸入資料不足長度時會自動補空格，故處理的時候要注意。 |
|
NCHAR |
類似於CHAR，但是支援Unicode，不過建議僅使用於必要支援Unicode的資料欄位，因為它儲存一個character需要使用2 bytes, 所以它最長為4,000 characters。 |
|
VARCHAR |
VARCHAR 支援不固定的長度的字串資料，最長8,000 characters，適用於字串長度不固定的資料，例如名子類的資料。 |
|
NVARCHAR |
類似於VARCHAR，但是支援Unicode，不過建議僅使用於必要支援Unicode的資料欄位，因為它儲存一個character需要使用2 bytes, 所以它最長為4,000 characters。 |
|
TEXT |
TEXT 支援動態長度的字串，最大可以到2 GB, 透過一個它自己的16-byte指標進行跨行儲存。這種資料欄位應該僅用於你的資料會超過CHAR/VARCHAR 的8,000 character限制時，但是如果你只有12,000 或甚至是24,000 characters，你應該考慮過載儲存於多個VARCHAR欄位，而不要使用較無彈性且較低效能的TEXT資料型態。(我這段翻譯可能不甚準確，請參考原文：TEXT supports variable length strings, up to 2 GB, stored off row with a 16-byte pointer in the record itself. Should be used whenever your data will exceed the 8,000 character limit of CHAR/VARCHAR columns (though if you need only 12,000 or even 24,000 characters, you might consider overflowing into multiple VARCHAR columns, rather than use the inflexible and less efficient TEXT datatype). |
|
NTEXT |
類似於TEXT，但是支援Unicode，不過建議僅使用於必要支援Unicode的資料欄位，因為它儲存一個character需要使用2 bytes, 所以它最長為1GB。 |
|
VARCHAR(MAX) |
類似於TEXT，但是相較之下它的2GB容量使用的較適當，可以使用一般字串功能(CHARINDEX, REPLACE, LEFT)，不再需要使用WRITETEXT/UPDATETEXT. (我這段翻譯可能不甚準確，請參考原文：Similar to TEXT, however this 2GB capacity is employed much more appropriately... you can now use regular string functions (CHARINDEX, REPLACE, LEFT), and you no longer need WRITETEXT/UPDATETEXT.) |
|
NVARCHAR(MAX) |
除了容量只有一半(因為支援Unicode)，其他同於VARCHAR(MAX)。 |
Original From：What datatype should I use for my character-based database columns?
關於這邊的TEXT與VARCHAR(MAX)部分，我做了進一步的資料蒐集：
在舊版的MS SQL SERVER中，我們如果直接存取TEXT欄位會出現一個TEXTPTR，所以我們需要用READTEXT與WRITETEXT來操作它，不過MS SQL SERVER2005之後就可以直接操作，那要怎麼於兩者之間作選擇呢？
選用TEXT為較佳的狀態：
- 如果需要儲存大量TEXT於資料庫的時候
- 如果不會對這資料作搜尋
- 如果很少查詢這個欄位而且不對它進行Join
選用VARCHAR(MAX)為較佳的狀態：
- 如果會儲存少量的字串資料
- 如果會對欄位內容的字串資料作搜尋
- 如果常常查詢這個欄位而且會對它進行Join
更完整的資料請參考來源：SQL Server Text type vs. varchar data type
另外這邊還有一些資料關於char/varchar/nchar/nvarchar的選擇：
(1)varchar/char（可變動/不可變動）
varchar是可變動長度，假設它Schema長度是10，而char也是10（不可變動），但您只存"a"一個字元時，varchar是佔掉1個字元，但char一定必須用掉10字元，因為varchar是用多少吃多少，char很死一次一定要吃掉Schema長度；又以身份證字號為例，如兩者Schema長度都是50好了,char會佔50個字元，但varchar會佔10個字元，這是兩者的落差
(2)Nvarchar/varchar（雙字元與單字元）
如果儲存的資料確定不會用到雙字元則應選用varchar（如身份證字號），若可能包含中文字則應選擇Nvarchar，但是Nvarchar儲存空間會是varchar兩倍，如Schema長度都是50好了,varchar會佔10個字元，但Nvarchar會佔20個字元，這是UniCode的影響
因此你的考量不只是varchar/char變動長度與固定長度的問題,還包括了雙字元與單字元的問題，甚至是兩者的混合，故您必須依需求選
除此之外我可以跟你講是效能與儲存空間計算，SQL Server底層資料最小的儲存單位是Page(8k)，但是我們無法直接取用，我們能使用到的是資料表,Index，而它們是儲存在一個更大的單位extend(64K，等於8個Pages)，講這個幹嘛？
(1) 因為Server磁碟在格式化時，NTFS有個選項配置單位大小4096Bytes,因此磁碟IO的二個Allocation剛好可以儲存一個Page，當你資料表Schema設計愈精簡，SQL Server的儲存讀取單位與NTFS磁碟IO愈能Match，這時就會效能最佳化；若Schema長度都是50，舉例來說身份證字號H120067600存varchar是10個字元，但由nvarchar會用掉20個字元，故可以理解若你每次讀取1000筆，nvarchar必須有更多的磁碟IO才能將資料讀完，同時也較浪費磁碟空間與電腦記憶體，故精明的DBA是會真實利用較為深入的知識來計算最佳化的Table Schema設計．
(2)另外一個是SQL Server必須維護相對的索引，包括索引空間及B-tree的維護，Nvarchar成本會比varchar來得高，最終也是效能問題，那Nvarchar/Nchar在索引樹的分割也會有不同影響。
原文摘自：varchar 和nvarchar的抉擇
故對於資料庫的資料型態設計需要針對未來儲存內容來作考量，特別是資料量大的時候，它的效能更受到設計的影響。
其他參考資料：
