SQL Server：最常用的数据类型？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4505385/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server: most commonly used data types?
提问by foreyez
I'm a bit confused as there are many variable types in sql server (ntext, varchar, nvarchar, etc) so maybe if you give me what data types you use for the following fields I'll understand this a little better. If I'm missing a common field type please let me know.
我有点困惑，因为 sql server 中有很多变量类型（ntext、varchar、nvarchar 等），所以也许如果你给我你用于以下字段的数据类型，我会更好地理解这一点。如果我缺少常见的字段类型，请告诉我。
ID
Telephone Number
Email
Description (a paragraph of text)
Name
SSN
Price
Ship Date
Sex (m/f)
Discontinued (yes/no)
Quantity
Zip Code
ID
电话号码
电子邮件
描述（一段文字）
姓名
SSN
价格
发货日期
性别（男/女）
停产（是/否）
数量
邮政编码
回答by marc_s
A brief recommendation:
一个简短的建议：
TEXT, NTEXT, IMAGE: all those types are deprecatedand scheduled to be removed in a future version of SQL Server - don't use those!CHARvs.VARCHAR:CHARis fixed-length, and it will be padding inputs with spaces to the defined length. Works best for short strings (< 5 characters), e.g. codes, like currency (almost always 3 characters), US status (2 chars) etc.VARCHARon the other hand works best for longer strings and is only storing as much characters as are inserted/updated. If you define aVARCHAR(200)and only insertChristmasinto the field, your field occupies 9 characters (and a litte bit of overhead)NCHAR/NVARCHAR: Unicode versions of the above; always stores 2 bytes per characters, so your field withChristmasin it will store 9 characters and use 18 bytes to do so. Those are needed if you have non-Western-European characters - such as Cyrillic, Arabic, Hebrew, Asian or other alphabets.VARCHAR(MAX) / NVARCHAR(MAX)are the replacements forTEXTandNTEXT- storing up to 2 GByte (2 billion bytes) of data - that's over 300 times the content of Tolstoi'sWar and Peace- should suffice for the vastmajority of cases :-)
TEXT, NTEXT, IMAGE：所有这些类型都已弃用，并计划在 SQL Server 的未来版本中删除 -不要使用这些类型！CHARvs.VARCHAR:CHAR是固定长度的，它将用空格填充输入到定义的长度。最适合短字符串（< 5 个字符），例如代码，如货币（几乎总是 3 个字符）、美国状态（2 个字符）等VARCHAR。另一方面，最适合较长的字符串，并且只存储与插入的字符一样多的字符/更新。如果您定义 aVARCHAR(200)并且只插入Christmas到字段中，您的字段将占用 9 个字符（以及一点点开销）NCHAR/NVARCHAR：上述的Unicode版本；每个字符始终存储 2 个字节，因此其中的字段Christmas将存储 9 个字符并使用 18 个字节来执行此操作。如果您有非西欧字符（例如西里尔字母、阿拉伯语、希伯来语、亚洲字母或其他字母），则需要这些字符。VARCHAR(MAX) / NVARCHAR(MAX)是的替代TEXT和NTEXT-存储多达数据的2GB的（2个十亿字节） -这是托尔斯泰的含量超过300倍War and Peace-应足以为广大大多数情况下:-)
So your decision tree could be like this:
所以你的决策树可能是这样的：
Do I need non-Western-European characters? If yes --> use
NCHAR/NVARCHARtypes, otherwiseCHAR/VARCHARIs my string very short (< 5 characters) and typically always the same length? If yes: use CHAR, otherwise VARCHAR
Do I need really really huge volumes of text? If so, use VARCHAR(MAX), otherwise size it to match your needs
我需要非西欧字符吗？如果是 --> 使用
NCHAR/NVARCHAR类型，否则CHAR/VARCHAR我的字符串是否很短（< 5 个字符）并且通常总是相同的长度？如果是：使用 CHAR，否则使用 VARCHAR
我真的需要大量的文本吗？如果是这样，请使用 VARCHAR(MAX)，否则根据您的需要调整大小
回答by Justin Niessner
Field -> Data Type
----- ---------
Id int
Phone # varchar
Email varchar
Desc varchar
Name varchar
Ssn varchar
Price decimal, money, smallmoney
ShipDate datetime
Sex bit
Discont bit
Quantity int
ZipCode varchar
回答by E.J. Brennan
ID - int
Telephone - varchar(12)
email - varchar(size)
descripion varchar(max)
name -varchar(size)
ssn - varchar(11)
price - smallmoney (or money if needed)
shipdate - date (for sql server 2008, or smalldatetime for pre-2008)
discontinued - bit
quanity - int
zipcode - varchar(10)
A lot of folks are going to recommend nvarchar in all cases instead of varchar, but knowing my sites/audience, I don't need to allow for international character sets and don't want to waste the space/speed/resources (minimal I know). If you need to, then substitute nvarchar where appropriate
很多人会在所有情况下推荐 nvarchar 而不是 varchar，但了解我的网站/受众，我不需要允许使用国际字符集，也不想浪费空间/速度/资源（最小的我知道）。如果需要，请在适当的地方替换 nvarchar
回答by bobs
ID int or bigint
Telephone Number varchar
Email varchar
Description varchar
Name varchar
SSN varchar
Price money
Ship Date datetime or date
Sex (m/f) char(1)
Discontinued (yes/no) bit
Quantity int
Zip Code varchar
回答by John Hartsock
Here is what I have used in the past
这是我过去使用的
ID = bigint
Telephone = varchar(12)
Email = varchar(100)
Description = nvarchar(max) (sql Server 2005 and 2008 only)
Name = nvarchar(100)
SSN = varchar(11)
Price = money
ShipDate = datetime (date if using SQL Server 2008)
Sex = char(1) (i have also used bit before 0 = female 1 =male)
Discontinued (true false field) = bit
Quantity = int if not fractional decimal if it is fractional
ZipCode = varchar(10)
