1. 先搞明白SQL Server的數(shù)據(jù)類(lèi)型體系究竟分幾類(lèi)很多人一打開(kāi)SQL Server聯(lián)機(jī)文檔看到幾十種數(shù)據(jù)類(lèi)型直接頭大心想這玩意兒怎么能叫大全干脆當(dāng)成字典用遇到啥查啥。說(shuō)實(shí)話(huà)我剛接觸SQL Server那會(huì)兒也是這么干的。直到后來(lái)在真實(shí)項(xiàng)目里吃到虧——因?yàn)椴涣私忸?lèi)型的底層表示方式設(shè)計(jì)表結(jié)構(gòu)時(shí)埋了一堆雷才老老實(shí)實(shí)把整條類(lèi)型體系捋了一遍。其實(shí)SQL Server的數(shù)據(jù)類(lèi)型分類(lèi)特別清晰官方的套路是把它們按存儲(chǔ)特征和行為習(xí)慣切成了幾個(gè)大類(lèi)精確數(shù)值、近似數(shù)值、字符串、Unicode字符串、二進(jìn)制字符串、日期時(shí)間、其他特殊類(lèi)型。這個(gè)分法本身就是最好的學(xué)習(xí)線(xiàn)索因?yàn)樗鼜?qiáng)調(diào)的是這些類(lèi)型在數(shù)據(jù)庫(kù)引擎眼里到底是怎么存的、能精確到什么程度、支持什么樣的操作。你可以把數(shù)據(jù)庫(kù)里的數(shù)據(jù)類(lèi)型想象成容器的材質(zhì)瓷碗和塑料碗都能裝水但瓷碗能進(jìn)微波爐塑料碗不行有的容器標(biāo)明刻度可以精確量取有的只能估個(gè)大概。字段選型本質(zhì)上就是在挑容器存儲(chǔ)的語(yǔ)義、取值范圍、精度要求、查詢(xún)方式、索引兼容性每一項(xiàng)都直接影響后面幾十年的使用體驗(yàn)。還有一件事必須在開(kāi)頭就講清楚SQL Server里很多類(lèi)型看著名字像、其實(shí)脾氣完全不同。比如char和nchar只差一個(gè)前綴但一個(gè)是非Unicode、一個(gè)是Unicode存儲(chǔ)邏輯和字符集處理完全是兩碼事datetime和datetime2差了整整一個(gè)精度時(shí)代bit和tinyint都能存0和1但語(yǔ)義完全不同。后面我會(huì)一個(gè)個(gè)拆開(kāi)講。這篇文章的定位不是把微軟官方文檔搬過(guò)來(lái)念一遍而是按這個(gè)類(lèi)型適合干什么、有什么坑、表單怎么建最穩(wěn)的思路把整個(gè)類(lèi)型體系串成一張可以實(shí)際照著用的地圖。適合正在建表、優(yōu)化庫(kù)表結(jié)構(gòu)、或者準(zhǔn)備面試需要把基礎(chǔ)夯實(shí)的開(kāi)發(fā)者。1.1 官方分類(lèi)口徑與實(shí)際業(yè)務(wù)的映射官方的分類(lèi)是從數(shù)據(jù)庫(kù)引擎的實(shí)現(xiàn)角度出發(fā)的但落到真實(shí)業(yè)務(wù)里我們要關(guān)心的其實(shí)是另一組映射關(guān)系。比如精確數(shù)值類(lèi)型tinyint、smallint、int、bigint、decimal、numeric、money、smallmoney——管的是數(shù)字必須分毫不差的場(chǎng)景主鍵、計(jì)數(shù)器、金額、數(shù)量。近似數(shù)值類(lèi)型float、real——管的是量級(jí)和趨勢(shì)對(duì)就行最后幾位無(wú)所謂的場(chǎng)景溫度傳感器數(shù)據(jù)、科學(xué)計(jì)算中間量。字符串類(lèi)型char、varchar、text——管的是字符集單一且以英文為主的場(chǎng)景。Unicode字符串類(lèi)型nchar、nvarchar、ntext——管的是可能有中文、日文、emoji等多語(yǔ)言的場(chǎng)景。二進(jìn)制類(lèi)型binary、varbinary、image、FILESTREAM——管的是文件、加密串、序列化對(duì)象的場(chǎng)景。日期時(shí)間類(lèi)型date、time、smalldatetime、datetime、datetime2、datetimeoffset——管的是事件發(fā)生時(shí)點(diǎn)、區(qū)間計(jì)算、時(shí)區(qū)換算的場(chǎng)景。其他特殊類(lèi)型uniqueidentifier、rowversion、xml、json、sql_variant、hierarchyid、空間地理類(lèi)型等——管的是分布式唯一ID、版本標(biāo)記、半結(jié)構(gòu)化數(shù)據(jù)、樹(shù)形結(jié)構(gòu)、地理位置的場(chǎng)景。這套映射關(guān)系理清楚之后再看官方文檔就不會(huì)暈了。說(shuō)白了分類(lèi)是形式業(yè)務(wù)才是本質(zhì)。1.2 從存儲(chǔ)和性能角度看為什么不能拍腦袋選選錯(cuò)數(shù)據(jù)類(lèi)型的代價(jià)是隱蔽的但一旦爆發(fā)就很疼。最典型的三個(gè)維度是存儲(chǔ)空間、索引效率和隱式轉(zhuǎn)換。先說(shuō)存儲(chǔ)空間。SQL Server的數(shù)據(jù)頁(yè)固定是8KB行數(shù)據(jù)都往頁(yè)里塞。你要是把只需要0~200的數(shù)字塞進(jìn)bigint白白浪費(fèi)6個(gè)字節(jié)一張表幾百萬(wàn)行浪費(fèi)的存儲(chǔ)空間倒是小事關(guān)鍵是索引頁(yè)變多、緩沖池命中率下降、IO變大。有人覺(jué)得服務(wù)器不差這點(diǎn)磁盤(pán)但磁盤(pán)不差、內(nèi)存差數(shù)據(jù)庫(kù)的性能瓶頸往往出在這些不起眼的地方。再說(shuō)隱式轉(zhuǎn)換。查詢(xún)條件里的數(shù)據(jù)類(lèi)型和字段類(lèi)型不一致時(shí)優(yōu)化器會(huì)嘗試把一邊轉(zhuǎn)成另一邊。常見(jiàn)情況是字段是varchar參數(shù)傳的是nvarchar結(jié)果索引失效走全表掃描。這個(gè)問(wèn)題在字符串類(lèi)型上最嚴(yán)重后面我會(huì)單獨(dú)拎出來(lái)講。第三是精度問(wèn)題。用float存金額看起來(lái)能存進(jìn)去取出來(lái)多一位小數(shù)、少一位小數(shù)賬面不平了財(cái)務(wù)和審計(jì)直接炸。類(lèi)型選錯(cuò)不是以后再說(shuō)的問(wèn)題而是上線(xiàn)就會(huì)爆的問(wèn)題。2. 數(shù)值系全家桶從bit到decimal的精度博弈數(shù)值類(lèi)型是數(shù)據(jù)庫(kù)里最較真的一類(lèi)因?yàn)樗苯雨P(guān)系到賬目、統(tǒng)計(jì)、排序、索引。SQL Server的數(shù)值類(lèi)型看起來(lái)不多但每一檔都有嚴(yán)格的邊界踩線(xiàn)就是報(bào)錯(cuò)。2.1 整數(shù)四兄弟與bit的邊界先看最常用的整數(shù)類(lèi)型類(lèi)型存儲(chǔ)大小取值范圍bit1字節(jié)8個(gè)bit列共用1字節(jié)0、1、NULLtinyint1字節(jié)8 bit0 ~ 255smallint2字節(jié)16 bit-32,768 ~ 32,767int4字節(jié)32 bit-2,147,483,648 ~ 2,147,483,647bigint8字節(jié)64 bit-9,223,372,036,854,775,808 ~ 9,223,372,036,854,775,807很多人把bit理解成布爾值這個(gè)理解沒(méi)錯(cuò)但要特別注意它在存儲(chǔ)上的一個(gè)特性一張表里如果只有1個(gè)bit列它占用1字節(jié)如果有8個(gè)bit列它們加起來(lái)還是占1字節(jié)。這是因?yàn)镾QL Server會(huì)把同一行里的多個(gè)bit列按位打包存儲(chǔ)。這個(gè)特性在表設(shè)計(jì)時(shí)可以幫你省一點(diǎn)空間但別指望靠它優(yōu)化出質(zhì)的飛躍。tinyint因?yàn)橹荒艽?~255常被用來(lái)存狀態(tài)碼、枚舉值、端口號(hào)、年齡等小范圍數(shù)據(jù)。smallint和int是業(yè)務(wù)主力絕大多數(shù)計(jì)數(shù)器、自增主鍵用int都?jí)蛄?。bigint只有在確鑿需要超大范圍時(shí)才用——比如分布式系統(tǒng)里的雪花算法ID、流水號(hào)、埋點(diǎn)事件ID。有人建表時(shí)習(xí)慣性往大了選主鍵一律bigint這不是錯(cuò)誤但確實(shí)不夠精細(xì)。int主鍵的上限是21億多很多業(yè)務(wù)表其實(shí)十萬(wàn)年也到不了這個(gè)量級(jí)。但反過(guò)來(lái)如果已經(jīng)有int主鍵的表數(shù)據(jù)量逼近瓶頸想在線(xiàn)改成bigint那絕對(duì)是一場(chǎng)災(zāi)難——所有關(guān)聯(lián)表的外鍵都要跟著改索引全部重建鎖表時(shí)間長(zhǎng)得能讓人崩潰。所以主鍵用bigint還是int這個(gè)選擇與其說(shuō)是空間優(yōu)化不如說(shuō)是容量規(guī)劃。2.2 decimal/numeric的精度刻度參數(shù)到底怎么設(shè)decimal和numeric在SQL Server里功能完全等價(jià)官方文檔原話(huà)是numeric 等同于 decimal。它倆都允許你顯式聲明精度precision和小數(shù)位數(shù)scale語(yǔ)法是DECIMAL(p, s)。pprecision總位數(shù)包括小數(shù)點(diǎn)兩邊的所有數(shù)字取值范圍1~38。sscale小數(shù)點(diǎn)右邊保留的位數(shù)取值范圍0~p。存儲(chǔ)大小不是固定的SQL Server會(huì)根據(jù)p的值決定用多少字節(jié)p 9用5字節(jié)p 19用9字節(jié)p 28用13字節(jié)p 38用16字節(jié)。這里有個(gè)特別容易犯的錯(cuò)DECIMAL(18, 4)表示總位數(shù)18位其中小數(shù)部分4位整數(shù)部分只有14位。有人建表時(shí)以為DECIMAL(18, 4)能存18位整數(shù)再加4位小數(shù)結(jié)果存入超過(guò)14位整數(shù)的數(shù)字時(shí)直接報(bào)算術(shù)溢出錯(cuò)誤將 numeric 轉(zhuǎn)換為數(shù)據(jù)類(lèi)型 numeric 時(shí)出錯(cuò)。設(shè)計(jì)金額字段時(shí)我的習(xí)慣是整數(shù)部分按業(yè)務(wù)上限的3倍預(yù)留小數(shù)部分按實(shí)際需要留2~4位。比如一個(gè)系統(tǒng)里的最大單筆金額不會(huì)超過(guò)1億整數(shù)部分9位我就建DECIMAL(16, 4)整數(shù)部分12位留足余量小數(shù)4位應(yīng)對(duì)匯率、折扣、計(jì)價(jià)單位等場(chǎng)景。另一個(gè)關(guān)鍵點(diǎn)SQL Server對(duì)decimal的除法、乘法運(yùn)算結(jié)果也是有精度規(guī)則的比如兩個(gè)DECIMAL(38, 10)相除結(jié)果精度可能退化或報(bào)錯(cuò)。如果你做復(fù)雜計(jì)算時(shí)發(fā)現(xiàn)了算術(shù)溢出錯(cuò)誤不要只懷疑數(shù)據(jù)很可能是中間結(jié)果的精度被引擎卡住了解決方法是CAST成合適精度的中間值。2.3 float和real為什么差一點(diǎn)點(diǎn)會(huì)要命float和real屬于近似數(shù)值類(lèi)型這意味著它們?cè)诖鎯?chǔ)時(shí)用二進(jìn)制近似表示十進(jìn)制小數(shù)無(wú)法保證精確。float的可選參數(shù)n決定精度和存儲(chǔ)大小float(n)精度存儲(chǔ)大小1~247位有效數(shù)字4字節(jié)25~5315位有效數(shù)字8字節(jié)real等同于float(24)占4字節(jié)7位有效數(shù)字。7位有效數(shù)字是什么概念1234567.89這種數(shù)字存進(jìn)去最終可能是1234567.9因?yàn)橐嬷槐ＷC前7位數(shù)字可靠。float的設(shè)計(jì)初衷是給科學(xué)計(jì)算用的那類(lèi)場(chǎng)景對(duì)稍微偏移幾納米溫度波動(dòng)0.000001度不敏感但對(duì)金融、計(jì)費(fèi)、庫(kù)存這種必須精確對(duì)賬的業(yè)務(wù)來(lái)說(shuō)float就是一顆定時(shí)炸彈。我見(jiàn)過(guò)不少項(xiàng)目把金額、單價(jià)、稅率存成float平時(shí)看著好好的合計(jì)數(shù)對(duì)不上、報(bào)表偶現(xiàn)2.999999999排查到凌晨才懷疑到類(lèi)型頭上來(lái)。所以我的結(jié)論很直接業(yè)務(wù)字段凡是涉及錢(qián)、量、稅、率的一律不準(zhǔn)用float/real只有在統(tǒng)計(jì)傳感器讀數(shù)、算法輸出這類(lèi)本身就帶誤差的數(shù)據(jù)時(shí)float才是合理選擇。2.4 money/smallmoney別被貨幣專(zhuān)用誤導(dǎo)money占8字節(jié)smallmoney占4字節(jié)。這倆類(lèi)型從名字看好像是存錢(qián)專(zhuān)用但它們內(nèi)部其實(shí)是定點(diǎn)小數(shù)精度固定到小數(shù)點(diǎn)后4位而且取值范圍受限于money的精度模型。說(shuō)實(shí)話(huà)我在實(shí)際項(xiàng)目里推薦大家謹(jǐn)慎使用money。理由有兩條第一它不支持任意精度的小數(shù)位數(shù)設(shè)定如果業(yè)務(wù)需要保留6位甚至更多小數(shù)位它直接歇菜第二它的貨幣語(yǔ)義只在SQL Server內(nèi)部有意義一旦和外部系統(tǒng)交換數(shù)據(jù)、做報(bào)表導(dǎo)出、寫(xiě)應(yīng)用程序映射反而容易引入特殊處理邏輯。絕大多數(shù)現(xiàn)代業(yè)務(wù)系統(tǒng)用DECIMAL就能完美解決問(wèn)題沒(méi)必要為了一個(gè)看起來(lái)像專(zhuān)業(yè)貨幣類(lèi)型的名頭引入額外約束。3. 字符串與Unicodechar/varchar/nvarchar的取舍與浪費(fèi)字符串是日常開(kāi)發(fā)里打交道最多的類(lèi)型也是翻車(chē)率最高的類(lèi)型。這章把char、varchar、nchar、nvarchar、text/ntext這些老少爺們都拉出來(lái)遛遛。3.1 定長(zhǎng)與變長(zhǎng)的根本差異char(n)是定長(zhǎng)字符串無(wú)論你實(shí)際存入多少字符它都會(huì)占用固定n個(gè)字節(jié)非Unicode下1字符1字節(jié)不足部分用空格填充。varchar(n)是變長(zhǎng)字符串存儲(chǔ)開(kāi)銷(xiāo)等于實(shí)際字符數(shù)加上少量開(kāi)銷(xiāo)字節(jié)2字節(jié)記錄長(zhǎng)度varchar(max)的存儲(chǔ)方式另有不同。nchar(n)、nvarchar(n)是它們對(duì)應(yīng)的Unicode版本每個(gè)字符固定占2字節(jié)非增補(bǔ)平面字符同樣的n值下占用的空間直接翻倍。這兩組怎么選說(shuō)實(shí)話(huà)現(xiàn)代系統(tǒng)里我?guī)缀醪粫?huì)主動(dòng)推薦char/nchar做業(yè)務(wù)字段因?yàn)樽冮L(zhǎng)存儲(chǔ)能大幅節(jié)省空間而且varchar在查詢(xún)上和char沒(méi)有本質(zhì)性能差距。但有一種場(chǎng)景char有優(yōu)勢(shì)固定長(zhǎng)度的編碼字段。比如身份證號(hào)18位、統(tǒng)一社會(huì)信用代碼18位、MD5哈希32位這些字段語(yǔ)義上長(zhǎng)度固定用char(18)可以避免varchar的2字節(jié)開(kāi)銷(xiāo)單行不算啥幾億行就有意義還能防止少填一位、多填一位這類(lèi)不嚴(yán)謹(jǐn)數(shù)據(jù)悄悄溜進(jìn)表里。而且定長(zhǎng)字段在排序、比較時(shí)的行為更穩(wěn)定。不過(guò)要注意char在讀取時(shí)尾隨空格的行為有坑。你用char(10)存了a實(shí)際存儲(chǔ)是a SQL Server在做等值比較時(shí)會(huì)自動(dòng)忽略尾隨空格但LEN()函數(shù)也會(huì)忽略尾隨空格而DATALENGTH()不會(huì)。如果業(yè)務(wù)邏輯對(duì)尾隨空格敏感定長(zhǎng)類(lèi)型會(huì)讓你抓狂。3.2 nvarchar(MAX)不是銀彈先給結(jié)論nvarchar(max)是個(gè)偉大的類(lèi)型但它不該被濫用。nvarchar(max)可以存最大約2GB的Unicode文本而且它在8KB以?xún)?nèi)的數(shù)據(jù)可以是行內(nèi)存儲(chǔ)超過(guò)閾值則自動(dòng)把大值移到LOB存儲(chǔ)。理論上它一個(gè)類(lèi)型打天下小到標(biāo)題、大到長(zhǎng)文都能存。問(wèn)題出在性能和索引上max類(lèi)型的字段不能做索引鍵除非你給計(jì)算列建索引或用全文索引對(duì)max字段的排序、分組、去重代價(jià)很高它參與查詢(xún)時(shí)優(yōu)化器對(duì)它的統(tǒng)計(jì)信息估算往往不準(zhǔn)容易導(dǎo)致執(zhí)行計(jì)劃不理想。比如一個(gè)用戶(hù)備注字段明明業(yè)務(wù)上最多100字你用nvarchar(max)建表開(kāi)發(fā)時(shí)爽快后面要在這張表上做報(bào)表、做分析查詢(xún)時(shí)每次都要小心翼翼別碰那個(gè)大字段。性能調(diào)優(yōu)時(shí)看到大對(duì)象字段拖慢頁(yè)密度悔不當(dāng)初。我的選型原則是這樣的長(zhǎng)度區(qū)間推薦類(lèi)型理由1~100字符varchar(100)/nvarchar(100)行內(nèi)存儲(chǔ)、支持索引開(kāi)銷(xiāo)可控100~4000字符varchar(4000)/nvarchar(4000)大多數(shù)業(yè)務(wù)夠用仍在行內(nèi)常規(guī)存儲(chǔ)區(qū)間超過(guò)4000字符或不確定上限varchar(max)/nvarchar(max)不得已而為之注意性能取舍這里要特別提醒不要直接用nvarchar存所有字符串除非確實(shí)需要多語(yǔ)言。系統(tǒng)只在中文域跑或者雖然用戶(hù)可能輸入中文但數(shù)據(jù)庫(kù)排序規(guī)則支持中文那么普通varchar配合合適的排序規(guī)則在存儲(chǔ)和性能上會(huì)好很多。nvarchar用2字節(jié)存英文數(shù)字相當(dāng)于多花了1倍空間。3.3 老伙計(jì)text/ntext/image為何被退役text、ntext、image是SQL Server 2005之前的大對(duì)象類(lèi)型現(xiàn)在微軟官方已經(jīng)明確把它們標(biāo)記為不推薦使用新代碼不該再碰未來(lái)版本大概率會(huì)徹底移除。它們?cè)诠δ苌戏謩e被varchar(max)、nvarchar(max)、varbinary(max)取代。老系統(tǒng)遷移時(shí)這仨類(lèi)型的字段往往還活著建議逐步轉(zhuǎn)換因?yàn)榕f類(lèi)型的操作限制太多比如不能直接參與某些字符串函數(shù)、排序規(guī)則行為怪異。4. 日期時(shí)間類(lèi)型8個(gè)親戚怎么認(rèn)日期時(shí)間是表設(shè)計(jì)中被忽視得最嚴(yán)重的一類(lèi)。很多老項(xiàng)目一水兒的datetime哪怕只需要存?zhèn)€生日、只需要精確到天的日期也從不去想date類(lèi)型。等表大了才發(fā)現(xiàn)所有日期查詢(xún)都在做無(wú)謂的時(shí)區(qū)換算、范圍計(jì)算多出好幾個(gè)小時(shí)。4.1 一張表看全各類(lèi)型的精度、范圍與存儲(chǔ)開(kāi)銷(xiāo)類(lèi)型范圍精度存儲(chǔ)大小典型場(chǎng)景date0001-01-01 ~ 9999-12-311天3字節(jié)生日、發(fā)貨日、業(yè)務(wù)周期time00:00:00.0000000 ~ 23:59:59.9999999100納秒3~5字節(jié)打卡時(shí)間、時(shí)段統(tǒng)計(jì)smalldatetime1900-01-01 ~ 2079-06-061分鐘4字節(jié)老系統(tǒng)的粗略時(shí)間datetime1753-01-01 ~ 9999-12-313.33毫秒8字節(jié)存量系統(tǒng)、兼容舊數(shù)據(jù)datetime20001-01-01 ~ 9999-12-31100納秒6~8字節(jié)現(xiàn)代精確時(shí)間字段datetimeoffset0001-01-01 ~ 9999-12-31含時(shí)區(qū)偏移100納秒8~10字節(jié)跨時(shí)區(qū)業(yè)務(wù)、審計(jì)時(shí)間datetime2(0)等同datetime2可選秒精度6字節(jié)需要秒級(jí)但無(wú)關(guān)微秒這里最容易被忽略的一對(duì)是datetime和datetime2。datetime的歷史包袱太重——它只支持3.33毫秒精度且范圍從1753年才開(kāi)始因?yàn)楫?dāng)年的格里高利歷切換導(dǎo)致年份跳變。而datetime2支持100納秒精度范圍到公元1年語(yǔ)法上還可以自己指定小數(shù)秒精度datetime2(0)精確到秒、datetime2(3)精確到毫秒、datetime2(7)精確到100納秒。按需壓縮精度還能省存儲(chǔ)空間。4.2 datetime與datetime2的遷移注意如果把datetime字段改成datetime2表面上看只是精度提升內(nèi)核里會(huì)發(fā)生很多變化。最大的坑是語(yǔ)義變化datetime在存儲(chǔ)時(shí)舍入到0、0.003、0.007秒這樣的步長(zhǎng)datetime2則是精確存儲(chǔ)。原本存進(jìn)去的23:59:59.997在datetime2里就是精確值不會(huì)像以前那樣在某些運(yùn)算后出現(xiàn)意外進(jìn)位。這本來(lái)是好事但如果老系統(tǒng)有大量針對(duì)毫秒等于0的分組統(tǒng)計(jì)邏輯遷移后結(jié)果會(huì)變必須回歸測(cè)試。另一個(gè)坑是字符串格式的差異。datetime2不支持某些datetime特有的隱式格式轉(zhuǎn)換行為從客戶(hù)端傳入亂七八糟的日期字符串時(shí)datetime2往往更嚴(yán)格直接把錯(cuò)誤拋出來(lái)。老系統(tǒng)跑得好好的導(dǎo)入腳本換字段類(lèi)型之后可能在格式上崩一遍。4.3 時(shí)區(qū)敏感場(chǎng)景datetimeoffset的正確打開(kāi)方式如果你的業(yè)務(wù)涉及多地域協(xié)同——比如一個(gè)訂單在紐約下單、在上海審核、在東京入庫(kù)所有時(shí)間戳都落到一個(gè)數(shù)據(jù)庫(kù)里那datetimeoffset就派上用場(chǎng)了。它能保存本地時(shí)間加上UTC偏移量比如2025-06-01 10:00:00 08:00這樣無(wú)論數(shù)據(jù)來(lái)自哪個(gè)時(shí)區(qū)都能精確還原出UTC時(shí)刻并且支持SWITCHOFFSET、TODATETIMEOFFSET等函數(shù)在時(shí)區(qū)之間自由換算。很多團(tuán)隊(duì)嫌datetimeoffset麻煩統(tǒng)一用UTC時(shí)間存datetime2這也是一種很成熟的做法——應(yīng)用層負(fù)責(zé)時(shí)區(qū)轉(zhuǎn)換。但如果數(shù)據(jù)源非常多、采集端不可控或者審計(jì)要求保留原始本地時(shí)間datetimeoffset能減少大量口徑扯皮。4.4 只存日期、只存時(shí)間、用date和time建表時(shí)能省則省的原則在這里特別適用。需求是出生日期就用date8字節(jié)變3字節(jié)需求是營(yíng)業(yè)時(shí)段開(kāi)始結(jié)束用time即可需求是某年某月的某一天的報(bào)表統(tǒng)計(jì)字段用date做分組鍵比datetime清爽得多索引也更小。這種細(xì)節(jié)累計(jì)起來(lái)在大表上的差距非常明顯。5. 結(jié)構(gòu)化與半結(jié)構(gòu)化的新貴從uniqueidentifier到spatial能撐起現(xiàn)代業(yè)務(wù)復(fù)雜度的少不了這些特殊類(lèi)型。它們不像數(shù)值和字符串那么日常但用對(duì)了能解決一堆架構(gòu)難題。5.1 rowversion/uniqueidentifier并發(fā)與唯一性的幕后rowversion舊稱(chēng)timestamp是數(shù)據(jù)庫(kù)自動(dòng)維護(hù)的二進(jìn)制版本號(hào)每行數(shù)據(jù)被更新時(shí)該行的rowversion值都會(huì)自動(dòng)遞增。它最適合做樂(lè)觀(guān)并發(fā)控制讀取時(shí)拿到版本號(hào)更新時(shí)在where條件里帶上rowversion字段如果發(fā)現(xiàn)版本號(hào)變了說(shuō)明這行被改過(guò)更新失敗應(yīng)用層決定是刷新重試還是提示用戶(hù)。由于是數(shù)據(jù)庫(kù)引擎自動(dòng)生成完全不用擔(dān)心并發(fā)下的唯一性。注意一張表只能有一個(gè)rowversion列而且它不能做邏輯主鍵。uniqueidentifier也就是GUID16字節(jié)常用于分布式系統(tǒng)的主鍵和外鍵。它的優(yōu)勢(shì)是可以在多個(gè)節(jié)點(diǎn)獨(dú)立生成而不沖突劣勢(shì)是隨機(jī)性導(dǎo)致索引碎片嚴(yán)重。如果必須用GUID做主鍵有兩個(gè)緩解手段一是用NEWSEQUENTIALID()生成順序GUID二是讓GUID作為聚簇索引鍵之外的邏輯主鍵給表單獨(dú)建一個(gè)自增的聚簇鍵。我在分布式日志系統(tǒng)、消息表里見(jiàn)過(guò)大量GUID主鍵配合順序生成器后性能好了不少但總體上還是比自增整型主鍵貴。5.2 xml與json現(xiàn)代接口的存儲(chǔ)姿勢(shì)SQL Server從2005年就開(kāi)始支持xml類(lèi)型可以在字段里存XML文檔還能用XQuery查詢(xún)節(jié)點(diǎn)。到了2016年SQL Server引入了對(duì)JSON的支持——注意這里的JSON類(lèi)型實(shí)際上并不存在獨(dú)立的JSON存儲(chǔ)類(lèi)型它不是像PostgreSQL的jsonb那樣而是以nvarchar(max)為載體通過(guò)ISJSON、JSON_VALUE、OPENJSON等函數(shù)加以約束和解析。這也是我經(jīng)常被問(wèn)到的一個(gè)點(diǎn)SQL Server沒(méi)有JSON字段類(lèi)型用的是nvarchar(max) JSON函數(shù)。那xml和JSON怎么選如果你確實(shí)有大量XML格式的歷史數(shù)據(jù)、并且供應(yīng)鏈領(lǐng)域強(qiáng)依賴(lài)XML標(biāo)準(zhǔn)保留xml類(lèi)型沒(méi)問(wèn)題。但對(duì)現(xiàn)代API交互型業(yè)務(wù)JSON已經(jīng)是事實(shí)標(biāo)準(zhǔn)直接在nvarchar(max)上做校驗(yàn)和解析是更順滑的路線(xiàn)。有一點(diǎn)務(wù)必提醒不要把整張業(yè)務(wù)表的字段都塞進(jìn)一個(gè)JSON大字段里否則數(shù)據(jù)庫(kù)的約束能力、查詢(xún)優(yōu)化、外鍵關(guān)系全部失效表結(jié)構(gòu)變成寬鍵值倉(cāng)庫(kù)后續(xù)維護(hù)成本暴漲。JSON適合存結(jié)構(gòu)不固定、需要靈活擴(kuò)展的部分字段比如用戶(hù)偏好設(shè)置、第三方回調(diào)的原始報(bào)文。5.3 空間地理類(lèi)型與hierarchyid的適用邊界geography和geometry主要用于地理空間數(shù)據(jù)。geography存經(jīng)緯度坐標(biāo)單位是度支持球面距離計(jì)算geometry存平面坐標(biāo)系數(shù)據(jù)單位取決于坐標(biāo)系定義。如果你在做地圖POI、配送范圍、軌跡分析這倆類(lèi)型配合空間索引非常強(qiáng)大寫(xiě)查詢(xún)像STDistance、STIntersects就能完成復(fù)雜的空間判斷。hierarchyid是專(zhuān)門(mén)表達(dá)樹(shù)結(jié)構(gòu)的類(lèi)型適合組織架構(gòu)、商品分類(lèi)、評(píng)論回復(fù)這類(lèi)父子層級(jí)數(shù)據(jù)。它把整棵樹(shù)的路徑編碼在一個(gè)字段里查詢(xún)某個(gè)節(jié)點(diǎn)的所有后代非?？?。但它的API有學(xué)習(xí)曲線(xiàn)常見(jiàn)寫(xiě)法也不像parent_id遞歸那么直觀(guān)。如果你的樹(shù)層級(jí)很深、查詢(xún)頻繁值得投入如果只是很淺的兩三層父子關(guān)系用傳統(tǒng)自關(guān)聯(lián)外鍵就夠了。5.4 少見(jiàn)的sql_variant和table類(lèi)型sql_variant可以裝任何SQL Server數(shù)據(jù)類(lèi)型除了text、ntext、image、rowversion以及自身是萬(wàn)能容器。聽(tīng)起來(lái)很酷實(shí)戰(zhàn)中幾乎不用。因?yàn)樗褦?shù)據(jù)類(lèi)型的強(qiáng)約束完全消解了查詢(xún)、索引、比較、排序的規(guī)則全得動(dòng)態(tài)判斷性能也差。我只在某些配置表里見(jiàn)過(guò)它——一行存一個(gè)配置項(xiàng)值的類(lèi)型各不相同勉強(qiáng)算一個(gè)合理場(chǎng)景。table主要用于表值參數(shù)和表值函數(shù)返回值不用于字段定義。它在存儲(chǔ)過(guò)程編程里很好用把一個(gè)DataTable當(dāng)作參數(shù)傳給存儲(chǔ)過(guò)程做批量操作比逐條拼SQL字符串高效得多同時(shí)還能保留約束和事務(wù)。6. 選型判斷一張決策清單和四個(gè)高頻翻車(chē)現(xiàn)場(chǎng)聊了這么多類(lèi)型細(xì)節(jié)最后把這些經(jīng)驗(yàn)收攏成一張可以照著做的決策思路再用四個(gè)真實(shí)場(chǎng)景告訴你別跟我一樣踩坑。6.1 從需求反推類(lèi)型的決策清單我建表時(shí)習(xí)慣按下面的問(wèn)題清單快速收斂字段類(lèi)型建議你也直接用起來(lái)這個(gè)字段語(yǔ)義是什么數(shù)值、文本、時(shí)間、二進(jìn)制、特殊結(jié)構(gòu)如果是數(shù)值需要精確嗎涉及金額/庫(kù)存/計(jì)算對(duì)賬——需要?jiǎng)tdecimal純計(jì)數(shù)用整數(shù)范圍多大選int/bigint。如果是文本需要多語(yǔ)言需要超過(guò)4000字符是否需要索引——默認(rèn)varchar/nvarchar按業(yè)務(wù)上限設(shè)長(zhǎng)度絕不無(wú)腦max。如果是時(shí)間需要精確到秒還是毫秒要跨時(shí)區(qū)嗎——優(yōu)先datetime2時(shí)區(qū)敏感上datetimeoffset只存日期用date。有排序、分組、去重需求嗎——避免大對(duì)象類(lèi)型避免sql_variant避免把業(yè)務(wù)內(nèi)容塞進(jìn)JSON大字段。字段寬度有硬性上限嗎身份證、手機(jī)號(hào)、編碼——可考慮char。這套清單能解決絕大多數(shù)建表爭(zhēng)議。遇到團(tuán)隊(duì)里我就要一個(gè)萬(wàn)能類(lèi)型的聲音把清單懟過(guò)去基本好使。6.2 翻車(chē)現(xiàn)場(chǎng)1金額字段用float某個(gè)老系統(tǒng)的訂單表金額字段是float平時(shí)原價(jià)、折后價(jià)看著都正常直到做了財(cái)務(wù)月結(jié)SQL對(duì)賬腳本一跑好幾筆訂單差出0.000001元把明細(xì)JOIN起來(lái)看A表和B表相同的訂單號(hào)金額居然對(duì)不上。最后排查發(fā)現(xiàn)兩套系統(tǒng)一個(gè)用float存、一個(gè)用decimal存二進(jìn)制浮點(diǎn)數(shù)的尾數(shù)差異在跨庫(kù)關(guān)聯(lián)時(shí)原形畢露。修復(fù)方案是把所有涉及錢(qián)的字段統(tǒng)一DECIMAL(18, 2)或更精細(xì)的小數(shù)位應(yīng)用層也全部改用定點(diǎn)數(shù)運(yùn)算。6.3 翻車(chē)現(xiàn)場(chǎng)2手機(jī)號(hào)用int/bigint有人覺(jué)得手機(jī)號(hào)不就是一串?dāng)?shù)字嘛然后建了bigint。頭一回沒(méi)事直到某天發(fā)現(xiàn)手機(jī)號(hào)前面的0被吃掉、國(guó)際區(qū)號(hào)完全沒(méi)法存、后續(xù)對(duì)接營(yíng)銷(xiāo)系統(tǒng)時(shí)號(hào)碼被科學(xué)計(jì)數(shù)法格式化導(dǎo)出。手機(jī)號(hào)本質(zhì)是由數(shù)字構(gòu)成的字符串不是數(shù)值。它不做加減乘除只有等值匹配和前綴匹配所以字段類(lèi)型應(yīng)選varchar(20)預(yù)留國(guó)際號(hào)碼空間。6.4 翻車(chē)現(xiàn)場(chǎng)3全表nvarchar(MAX)治百病我曾經(jīng)接手過(guò)一張用戶(hù)反饋表除了主鍵、時(shí)間字段外所有字段一律nvarchar(max)。設(shè)計(jì)人當(dāng)時(shí)圖省事誰(shuí)知道用戶(hù)填多長(zhǎng)max萬(wàn)無(wú)一失。后來(lái)要在這張表上做關(guān)鍵詞模糊搜索全表掃得痛苦不堪要做按反饋類(lèi)型分組統(tǒng)計(jì)發(fā)現(xiàn)同義詞混亂無(wú)約束根本沒(méi)法聚合。最后不得不重構(gòu)表把標(biāo)題、反饋類(lèi)型、聯(lián)系電話(huà)等字段改成合理長(zhǎng)度并加約束只有反饋正文保留nvarchar(max)。這個(gè)改動(dòng)的代價(jià)是整整兩個(gè)通宵。6.5 翻車(chē)現(xiàn)場(chǎng)4時(shí)間字段取datetime算了新項(xiàng)目建表時(shí)想都不想就datetime結(jié)果業(yè)務(wù)要求精確到毫秒的并發(fā)時(shí)間戳發(fā)現(xiàn)datetime的3.33毫秒精度讓兩個(gè)并發(fā)操作的時(shí)間完全一樣后來(lái)想改成datetime2(7)又要處理大量歷史數(shù)據(jù)和時(shí)間格式的變化?，F(xiàn)在SQL Server 2016以上的環(huán)境里新表創(chuàng)建日期時(shí)間字段我真的找不到用datetime的理由一律datetime2精度按業(yè)務(wù)選0~7跨時(shí)區(qū)就上datetimeoffset。我在實(shí)際項(xiàng)目里還有一個(gè)習(xí)慣每次建表之后花兩分鐘對(duì)每個(gè)字段做一次如果數(shù)據(jù)量翻100倍這個(gè)類(lèi)型還撐得住嗎的推演。這個(gè)方法幫我避開(kāi)了很多設(shè)計(jì)期覺(jué)得沒(méi)問(wèn)題、上線(xiàn)后天天想改表的坑。SQL Server的數(shù)據(jù)類(lèi)型看起來(lái)龐雜但背后的取舍規(guī)律并不復(fù)雜——空間、精度、約束能力、索引友好度永遠(yuǎn)把業(yè)務(wù)語(yǔ)義放在第一位。希望這份大全能讓你以后建表時(shí)少走彎路。