none
請問字串資料行可以都偷懶開nvarchar(max)嗎?

    問題

  • 不好意思,請教各位DB高手,我是認真的問

    由於我對於字串類型的資料行一定都是開nvarchar的習慣

    因為可支援特殊字+儲存的資料長度輸入多少存多少(※透過datalength()函數來看的話)

    最近手邊案子開發初期,利用SSMS新增資料表時

    開發初期也不知道該字串上線後實際會儲存多少長度,加上趕專案時間,我對於字串型別的資料行

    都一律開nvarchar(MAX),想說既然是可變動長度的資料行,nvarchar(MAX)應該沒差吧(?

    後來專案到後期被客戶那邊的工程師發現我欄位都開很大,希望我改掉

    所以想上來向各位高手確認一下

    nvarchar(MAX)和nvarchar(4000),如果都儲存同樣的字串情況下,這兩個資料所佔儲存空間應該一樣吧?

    以下是我自行用datalength測試的結果:

    以上對於nvarchar(max)和nvarchar(4000)所佔用儲存空間,不知道有沒有遺漏什麼考慮因素?請前輩們指教

    另外,我查MSDN:http://msdn.microsoft.com/zh-tw/library/ms186939(v=sql.110).aspx

    上面寫nvarhcar,儲存體大小 (以位元組為單位) 是輸入資料之實際長度的兩倍 + 2 位元組

    請問那為什麼我用datalength()來算,感覺都是字數*2,跟MSDN的說明有點不太一樣呢?

    以上兩點問題,請多指教,先謝謝




    • 已編輯 Shadow .Net 2014年5月29日 下午 08:22 更改超連結
    2014年5月29日 下午 07:52

解答

  • 勸你改掉這種爛習慣 (不誇張,這習慣和不論任何欄位都用 char/varchar 型態一樣爛...)。

    資料庫儲存引擎對不同的資料型態有不同的對待方式,固定長度欄位直接搜尋分頁指標位置取出資料,可變長度欄位會先算資料實際長度再進入分頁取資料,速度本來就會比固定長度慢一些,用 max 方式宣告的話,儲存引擎會將它視為 LOB 資料型態,會將它儲存到額外的分頁內,光是在磁碟中搜尋這些分頁的成本會比計算長度更高 (別忘了這是 I/O 動作),會使得查詢和寫入速度都變慢。資料少量時看不出來,等量很大時就準備哭哭吧。

    Reference: http://www.sqlskills.com/blogs/paul/inside-the-storage-engine-anatomy-of-a-record/


    強力監督SQL Injection問題!!

      • 小朱的技術隨手寫:http://www.dotblogs.com.tw/regionbbs/
      • 雲端學堂Facebook: http://www.facebook.com/studyazure

    • 已提議為解答 KKBruceMVP 2014年5月30日 上午 12:00
    • 已標示為解答 Shadow .Net 2014年5月30日 下午 12:00
    2014年5月29日 下午 11:52
    版主
  • 您好,

    http://stackoverflow.com/questions/2091284/varcharmax-everywhere

    另一個考量是index的問題,之後會針對那些欄位Search嗎?

    max的話,是沒辦法針對它去建立index的,只能用included去包含它!


    亂馬客blog: http://www.dotblogs.com.tw/rainmaker/

    • 已標示為解答 Shadow .Net 2014年5月30日 下午 12:00
    2014年5月30日 上午 01:34

所有回覆

  • 勸你改掉這種爛習慣 (不誇張,這習慣和不論任何欄位都用 char/varchar 型態一樣爛...)。

    資料庫儲存引擎對不同的資料型態有不同的對待方式,固定長度欄位直接搜尋分頁指標位置取出資料,可變長度欄位會先算資料實際長度再進入分頁取資料,速度本來就會比固定長度慢一些,用 max 方式宣告的話,儲存引擎會將它視為 LOB 資料型態,會將它儲存到額外的分頁內,光是在磁碟中搜尋這些分頁的成本會比計算長度更高 (別忘了這是 I/O 動作),會使得查詢和寫入速度都變慢。資料少量時看不出來,等量很大時就準備哭哭吧。

    Reference: http://www.sqlskills.com/blogs/paul/inside-the-storage-engine-anatomy-of-a-record/


    強力監督SQL Injection問題!!

      • 小朱的技術隨手寫:http://www.dotblogs.com.tw/regionbbs/
      • 雲端學堂Facebook: http://www.facebook.com/studyazure

    • 已提議為解答 KKBruceMVP 2014年5月30日 上午 12:00
    • 已標示為解答 Shadow .Net 2014年5月30日 下午 12:00
    2014年5月29日 下午 11:52
    版主
  • 您好,

    http://stackoverflow.com/questions/2091284/varcharmax-everywhere

    另一個考量是index的問題,之後會針對那些欄位Search嗎?

    max的話,是沒辦法針對它去建立index的,只能用included去包含它!


    亂馬客blog: http://www.dotblogs.com.tw/rainmaker/

    • 已標示為解答 Shadow .Net 2014年5月30日 下午 12:00
    2014年5月30日 上午 01:34
  • Can create full text index on nvarchar(max) column.
    2014年5月30日 上午 01:58
  • 感謝小朱大和亂馬客大哥的回覆:

    看了兩位給的超連結,小弟歸納以下結論,不知道有沒有錯...

    1.nvarchar(max)和nvarchar(4000)如果從儲存角度來看,儲存相同資料的話佔用空間都差不多?

    http://stackoverflow.com/questions/2091284/varcharmax-everywhere
    from a storage perspective there is no disadvantage of using VARCHAR(MAX) over VARCHAR(N) for some N.

    2.但資料行開nvarchar(max)會造成查詢效能低落,不能建立索引的索引鍵資料行,對校能調校會有影響

    然後,不好意思,想再確認整數型別資料行的情況是不是類似?※原本也想偷懶都開bigint取代int(逃~

    但有找到此篇討論↓

    http://stackoverflow.com/questions/2124631/sql-server-int-or-bigint-database-table-ids

    bigint和int在儲存數字都一樣的情況下,兩者都是佔用固定空間8 Bytes4 Bytes
    ※出自http://msdn.microsoft.com/zh-tw/library/ms187745.aspx

    下圖是我用bigint和smallint做的比較資料長度結果


    但要注意資料表儲存的資料列數量愈大時候,bigint會很佔儲存空間(因為固定8 Bytes*資料列數量),也會造成查詢效能低落

    以上,不知道我的理解正不正確? 再煩請各位前輩解惑,謝謝。



    2014年5月30日 上午 06:46
  • 您好,

    雖然說有效能的考量,但主要的還是源自於「需求」的考量,

    以Table中的Identity來看,資料有可能超出 int 那就要使用bigint,

    以避免將來爆掉。


    亂馬客blog: http://www.dotblogs.com.tw/rainmaker/

    2014年5月30日 上午 09:42
  • 您好,

    雖然說有效能的考量,但主要的還是源自於「需求」的考量,

    以Table中的Identity來看,資料有可能超出 int 那就要使用bigint,

    以避免將來爆掉。


    亂馬客blog: http://www.dotblogs.com.tw/rainmaker/

    好的,了解

    上面文章提到的超連結:http://stackoverflow.com/questions/2124631/sql-server-int-or-bigint-database-table-ids

    也說在int快爆掉前,到時候你是有時間去Update該資料行為bigint的

    既然沒人再反駁小弟的結論,那就先當小弟的結論是正確的

    然後把兩位標正解,結案此討論,謝謝兩位前輩。


    2014年5月30日 下午 12:00
  • 整數型別 int/bigint 的儲存方式是相同的,它不會被當做 LOB 而有不同的處理方式。


    強力監督SQL Injection問題!!

      • 小朱的技術隨手寫:http://www.dotblogs.com.tw/regionbbs/
      • 雲端學堂Facebook: http://www.facebook.com/studyazure

    2014年5月30日 下午 01:05
    版主