SQL Server：最常用的数据类型？

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

身份证电话号码电子邮件说明（一段文字）姓名SSN价格出货日期性别（月/日）已停产（是/否）数量邮政编码

5 个解决方案

#1

A brief recommendation:

简要推荐：

TEXT, NTEXT, IMAGE: all those types are deprecated and scheduled to be removed in a future version of SQL Server - don't use those!

TEXT，NTEXT，IMAGE：所有这些类型都已弃用并计划在SQL Server的未来版本中删除 - 请勿使用这些类型！
CHAR vs. VARCHAR: CHAR is 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. VARCHAR on the other hand works best for longer strings and is only storing as much characters as are inserted/updated. If you define a VARCHAR(200) and only insert Christmas into the field, your field occupies 9 characters (and a litte bit of overhead)

CHAR与VARCHAR：CHAR是固定长度的，它将填充带有空格的填充输入到定义的长度。适用于短字符串（<5个字符），例如代码，如货币（几乎总是3个字符），美国状态（2个字符）等。另一方面，VARCHAR最适合较长的字符串，并且只存储插入/更新的字符数。如果你定义一个VARCHAR（200）并且只在该字段中插入圣诞节，那么你的字段占用9个字符（并且有一点点开销）
NCHAR/NVARCHAR: Unicode versions of the above; always stores 2 bytes per characters, so your field with Christmas in 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.

NCHAR / NVARCHAR：以上的Unicode版本;每个字符总是存储2个字节，因此包含圣诞节的字段将存储9个字符并使用18个字节来执行此操作。如果您有非西欧语言字符，例如西里尔语，阿拉伯语，希伯来语，亚洲语或其他字母，则需要这些字符。
VARCHAR(MAX) / NVARCHAR(MAX) are the replacements for TEXT and NTEXT - storing up to 2 GByte (2 billion bytes) of data - that's over 300 times the content of Tolstoi's War and Peace - should suffice for the vast majority of cases :-)

VARCHAR（MAX）/ NVARCHAR（MAX）是TEXT和NTEXT的替代品 - 存储多达2 GByte（20亿字节）的数据 - 这是Tolstoi战争与和平的300倍以上 - 应该足以满足绝大多数情况:-)

So your decision tree could be like this:

所以你的决策树可能是这样的：

Do I need non-Western-European characters? If yes --> use NCHAR/NVARCHAR types, otherwise CHAR/VARCHAR

我需要非西欧角色吗？如果是 - >使用NCHAR / NVARCHAR类型，否则使用CHAR / VARCHAR
Is my string very short (< 5 characters) and typically always the same length? If yes: use CHAR, otherwise VARCHAR

我的字符串是否很短（<5个字符）并且通常总是相同的长度？如果是：使用CHAR，否则使用VARCHAR
Do I need really really huge volumes of text? If so, use VARCHAR(MAX), otherwise size it to match your needs

我真的需要非常大量的文字吗？如果是这样，请使用VARCHAR（MAX），否则请根据您的需要调整大小

#2

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

#3

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

#4

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)

#5

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

#1