SQL Server中char、varchar、text和nchar、nvarchar、ntext的區別

標籤：style   blog   http   color   使用   ar   strong   sp   資料   

轉：http://blog.csdn.net/jackychu/article/details/4183118

http://www.cnblogs.com/jhxk/articles/1633578.html

很多開發人員進行資料庫設計的時候往往並沒有太多的考慮char， varchar類型，有的是根本就沒注意，因為儲存價格變得越來越便宜了，忘記了最開始的一些基本設計理論和原則，這點讓我想到了現在的年輕人，大手一揮 一把人民幣就從他手裡溜走了，其實我想不管是做人也好，做開發也好，細節的把握直接決定很多東西。當然還有一部分人是根本就沒弄清楚他們的區別，也就隨便 選一個。在這裡我想對他們做個簡單的分析，當然如果有不對的地方希望大家指教。

1、CHAR。CHAR儲存定長資料很方便，CHAR欄位上的索引效率級高，比如定義char(10)，那麼不論你儲存的資料是否達到了10個位元組，都要佔去10個位元組的空間,不足的自動用空格填充，所以在讀取的時候可能要多次用到trim（）。

2、VARCHAR。儲存變長資料，但儲存效率沒有CHAR高。如果一個欄位可能的值是不固定長度的，我們只知道它不可能超過10個字元，把它定義為 VARCHAR(10)是最合算的。VARCHAR類型的實際長度是它的值的實際長度+1。為什麼“+1”呢？這一個位元組用於儲存實際使用了多大的長度。從空間上考慮，用varchar合適；從效率上考慮，用char合適，關鍵是根據實際情況找到權衡點。

3、TEXT。text儲存可變長度的非Unicode資料，最大長度為2^31-1(2,147,483,647)個字元。

4、NCHAR、NVARCHAR、NTEXT。這三種從名字上看比前面三種多了個“N”。它表示儲存的是Unicode資料類型的字元。我們知道字元中，英文字元只需要一個位元組儲存就足夠了，但漢字眾多，需要兩個位元組儲存，英文與漢字同時存在時容易造成混亂，Unicode字元集就是為瞭解決字元集這種不相容的問題而產生的，它所有的字元都用兩個位元組表示，即英文字元也是用兩個位元組表示。nchar、nvarchar的長度是在1到4000之間。和char、varchar比較起來，nchar、nvarchar則最多儲存4000個字元，不論是英文還是漢字；而char、varchar最多能儲存8000個英文，4000個漢字。可以看出使用nchar、nvarchar資料類型時不用擔心輸入的字元是英文還是漢字，較為方便，但在儲存英文時數量上有些損失。

所以一般來說，如果含有中文字元，用nchar/nvarchar，如果純英文和數字，用char/varchar

我把他們的區別概括成：

CHAR，NCHAR 定長，速度快，占空間大，需處理
VARCHAR，NVARCHAR，TEXT 不定長，空間小，速度慢，無需處理
NCHAR、NVARCHAR、NTEXT處理Unicode碼

第二篇：

以前只知道text和image是可能被SQL Server淘汰的資料類型，但具體原因不太清楚，今天讀書的時候發現了text與varchar(max)和nvarchar(max)的區別，主要是對操作符的限制，text只能被下列函數作用：

 

函數	語句
DATALENGTH	READTEXT
PATINDEX	SET TEXTSIZE
SUBSTRING	UPDATETEXT
TEXTPTR	WRITETEXT
TEXTVALID

 

舉個列子，如果“文本”這一列的資料類型為text，那麼它將不能用於“=”“left()”等操作，比如下面的例子：

建立表，填充資料：

if exists (select * from sysobjects where id = OBJECT_ID(‘[asdf]‘) and OBJECTPROPERTY(id, ‘IsUserTable‘) = 1)

DROP TABLE [asdf]

CREATE TABLE [asdf] (

[inttest] [int] IDENTITY (1, 1) NOT NULL ,

[text] [text] NULL ,

[varcharmax] varchar(max) NULL )

ALTER TABLE [asdf] WITH NOCHECK ADD CONSTRAINT [PK_asdf] PRIMARY KEY NONCLUSTERED ( [inttest] )

SET IDENTITY_INSERT [asdf] ON

INSERT [asdf] ( [inttest] , [text] , [varcharmax] ) VALUES ( 1 , ‘1111111‘ , ‘1111111‘ )

SET IDENTITY_INSERT [asdf] OFF

執行查詢：

查詢一：
SELECT [text]

      ,[varcharmax]

FROM [testDB].[dbo].[asdf]

where

[text] =‘11111‘ AND

[varcharmax] = ‘1111111‘

會出現以下錯誤提示：

訊息402，層級16，狀態1，第1 行

資料類型text 和varchar 在equal to 運算子中不相容。

查詢二：
SELECT [text]

      ,[varcharmax]

FROM [testDB].[dbo].[asdf]

where

[varcharmax] = ‘1111111‘

可以成功運行

在MS SQL2005及以上的版本中，加入大值資料類型（varchar(max)、nvarchar(max)、varbinary(max) ）。大值資料類型最多可以儲存2^30-1個位元組的資料。

這幾個資料類型在行為上和較小的資料類型 varchar、nvarchar 和 varbinary 相同。

微軟的說法是用這個資料類型來代替之前的text、ntext 和 image 資料類型，它們之間的對應關係為：

varchar(max)-------text;

nvarchar(max)-----ntext;

varbinary(max)----image.

 

有了大值資料類型之後，在對大值資料操作的時候要比以前靈活的多了。比如：之前text是不能用‘like’的，有了varchar(max)之後就沒有這些問題了，因為varchar(max)在行為上和varchar(n)上相同，所以，可以用在varcahr的都可以用在varchar(max)上。

另外，這個還支援對插入的和刪除的表中的大值資料類型列引用上使用 AFTER 觸發器。text就不行，總之，用了大值資料類型之後，我是“腰也不疼了，腿也不酸了，一口氣也能上六樓了”。還等什麼呢，快用大實值型別吧。

2014年10月16日11:34:19

對SQL Server資料庫的資料類型儲存的學習掌握的確實不太好，我覺得我以前就是作者說的現在的年輕人，看了一點資料，對作者說的進行一下總結：

1.資料類型的對應關係：

varchar(max)-------text;

nvarchar(max)-----ntext;

varbinary(max)----image.

2.現在盡量不用text,ntext,image類型來儲存資料了

3.從空間上考慮，用varchar合適（其實還是盡量使用varchar,畢竟現在是大資料時代）；從效率上考慮，用char合適，關鍵是根據實際情況找到權衡點。

4.使用nchar、nvarchar資料類型時不用擔心輸入的字元是英文還是漢字，較為方便，但在儲存英文時數量上有些損失。所以一般來說，如果含有中文字元，用nchar/nvarchar，如果純英文和數字，用char/varchar

5.

支援多語言的網站應考慮使用 Unicode nchar 或 nvarchar 資料類型以盡量減少字元轉換問題。

如果希望列中的資料值大小接近一致，請使用 char。

如果希望列中的資料值大小顯著不同，請使用 varchar。

如果希望列中所有資料項目的大小接近一致，則使用 nchar。

如果希望列中資料項目的大小差異很大，則使用 nvarchar。

如果執行 CREATE TABLE 或 ALTER TABLE 時 SET ANSI_PADDING 為 OFF，則一個定義為 NULL 的 char 列將被作為 varchar 處理。

6.

1）如果資料量非常大，又能100%確定長度且儲存只是ansi字元，那麼char
2）能確定長度又不一定是ansi字元或者，那麼用nchar；
3）不確定長度，要查詢且希望利用索引的話，用nvarchar類型吧，將它們設到400；
4）不查詢的話沒什麼好說的，用nvarchar(4000)

7.

CHAR，NCHAR 定長，速度快，占空間大，需處理
VARCHAR，NVARCHAR，TEXT 不定長，空間小，速度慢，無需處理
NCHAR、NVARCHAR、NTEXT處理Unicode碼

 

SQL Server中char、varchar、text和nchar、nvarchar、ntext的區別 （轉）