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