What are good sizes for data types in SQL Server? When defining columns, i see data types with sizes of 50 as one of the default sizes(eg: nvarchar(50), binary(50)). What is the significance of 50? I'm tempted to use sizes of powers of 2, is that better or just useless?
SQL Server中数据类型的好大小是什么?在定义列时，我将大小为50的数据类型视为默认大小之一(例如:nvarchar(50)，二进制(50))。50的意义是什么?我想用2的幂的大小，这样更好还是没用?
Update 1 Alright thanks for your input guys. I just wanted to know the best way of defining the size of a datatype for a column.
更新1，谢谢你们的输入。我只是想知道定义列的数据类型大小的最佳方法。
7 个解决方案
#1
4
The reason so many fields have a length of 50 is that SQL Server defaults to 50 as the length for most data types where length is an issue.
很多字段的长度都是50的原因是SQL Server默认为50，这是大多数数据类型的长度，其中长度是一个问题。
As has been said, the length of a field should be appropriate to the data that is being stored there, not least because there is a limit to the length of single record in SQL Server (it's ~8000 bytes). It is possible to blow past that limit.
如前所述，字段的长度应该与存储在那里的数据相适应，尤其是因为SQL Server中单个记录的长度是有限的(它是~8000字节)。突破这个极限是可能的。
Also, the length of your fields can be considered part of your documentation. I don't know how many times I've met lazy programmers who claim that they don't need to document because the code is self documenting and then they don't bother doing the things that would make the code self documenting.
此外，字段的长度也可以视为文档的一部分。我不知道有多少次我遇到过懒惰的程序员，他们声称他们不需要文档化，因为代码是自文档化的，然后他们不需要做那些让代码自文档化的事情。
#2
6
There is no reason to use powers of 2 for performance etc. Data length should be determined by the size stored data.
没有理由使用2的幂等性能等。数据长度应由存储数据的大小来决定。
#3
5
Why not the traditional powers of 2, minus 1 such as 255...
为什么不是传统的2，- 1，比如255……
Seriously, the length should match what you need and is suitable for your data.
认真地说，长度应该符合您的需要，并且适合您的数据。
Nothing else: how the client uses it, aligns to 32 bit word boundary, powers of 2, birthdays, Scorpio rising in Uranus, roll of dice...
别无他法:客户如何使用它，对齐到32位字边界，2，生日，天蝎在天王星升起，掷骰子……
#4
4
You won't gain anything from using powers of 2. Make the fields as long as your business needs really require them to be - let SQL Server handle the rest.
你不会从2的幂中得到任何东西。只要您的业务需求确实需要字段，就让SQL Server来处理其余的字段。
Also, since the SQL Server page size is limited to 8K (of which 8060 bytes are available to user data), making your variable length strings as small as possible (but as long as needed, from a requirements perspective) is a plus.
此外，由于SQL Server页面大小被限制为8K(其中8060字节可供用户数据使用)，所以使可变长度字符串尽可能小(但从需求的角度来看，只要需要)是一个优点。
That 8K limit is a fixed SQL Server system setting which cannot be changed.
这个8K限制是一个固定的SQL Server系统设置，不能更改。
Of course, SQL Server these days can handle more than 8K of data in a row, using so called "overflow" pages - but it's less efficient, so trying to stay within 8K is generally a good idea.
当然，SQL Server现在可以连续处理超过8K的数据，使用所谓的“溢出”页面——但效率较低，所以尝试保持在8K以内通常是个好主意。
Marc
马克
#5
3
The size of a field should be appropriate for the data you are planning to store there, global defaults are not a good idea.
字段的大小应该适合您计划在那里存储的数据，全局默认值不是一个好主意。
#6
2
This totally depends on what you are storing. If you need x chars use x not some arbitrarily predefined amount.
这完全取决于存储的内容。如果您需要x字符，请使用x而不是任意预定义的数量。
#7
2
It's a good idea that the whole row fits into page several times without leaving too much free space.
这是一个好主意，整个行适合几次页而不留下太多的*空间。
A row cannot span two pages, an a page has 8096 bytes of free space, so two rows that take 4049 bytes each will occupy two pages.
一行不能跨越两个页面，一个A页有8096字节的空闲空间，因此每一行占用4049字节的两行将占用两个页面。
See docs on how to calculate the space occupied by one row.
请参阅文档，了解如何计算一行占用的空间。
Also note that VAR in VARCHAR and VARBINARY stands for "varying", so if you put a 1-byte value into a 50-byte column, it will take but 1 byte.
还要注意，VARCHAR和VARBINARY中的VAR表示“变化”，所以如果将一个1字节的值放入一个50字节的列中，那么只需要1个字节。
#1
4
The reason so many fields have a length of 50 is that SQL Server defaults to 50 as the length for most data types where length is an issue.
很多字段的长度都是50的原因是SQL Server默认为50，这是大多数数据类型的长度，其中长度是一个问题。
As has been said, the length of a field should be appropriate to the data that is being stored there, not least because there is a limit to the length of single record in SQL Server (it's ~8000 bytes). It is possible to blow past that limit.
如前所述，字段的长度应该与存储在那里的数据相适应，尤其是因为SQL Server中单个记录的长度是有限的(它是~8000字节)。突破这个极限是可能的。
Also, the length of your fields can be considered part of your documentation. I don't know how many times I've met lazy programmers who claim that they don't need to document because the code is self documenting and then they don't bother doing the things that would make the code self documenting.
此外，字段的长度也可以视为文档的一部分。我不知道有多少次我遇到过懒惰的程序员，他们声称他们不需要文档化，因为代码是自文档化的，然后他们不需要做那些让代码自文档化的事情。
#2
6
There is no reason to use powers of 2 for performance etc. Data length should be determined by the size stored data.
没有理由使用2的幂等性能等。数据长度应由存储数据的大小来决定。
#3
5
Why not the traditional powers of 2, minus 1 such as 255...
为什么不是传统的2，- 1，比如255……
Seriously, the length should match what you need and is suitable for your data.
认真地说，长度应该符合您的需要，并且适合您的数据。
Nothing else: how the client uses it, aligns to 32 bit word boundary, powers of 2, birthdays, Scorpio rising in Uranus, roll of dice...
别无他法:客户如何使用它，对齐到32位字边界，2，生日，天蝎在天王星升起，掷骰子……
#4
4
You won't gain anything from using powers of 2. Make the fields as long as your business needs really require them to be - let SQL Server handle the rest.
你不会从2的幂中得到任何东西。只要您的业务需求确实需要字段，就让SQL Server来处理其余的字段。
Also, since the SQL Server page size is limited to 8K (of which 8060 bytes are available to user data), making your variable length strings as small as possible (but as long as needed, from a requirements perspective) is a plus.
此外，由于SQL Server页面大小被限制为8K(其中8060字节可供用户数据使用)，所以使可变长度字符串尽可能小(但从需求的角度来看，只要需要)是一个优点。
That 8K limit is a fixed SQL Server system setting which cannot be changed.
这个8K限制是一个固定的SQL Server系统设置，不能更改。
Of course, SQL Server these days can handle more than 8K of data in a row, using so called "overflow" pages - but it's less efficient, so trying to stay within 8K is generally a good idea.
当然，SQL Server现在可以连续处理超过8K的数据，使用所谓的“溢出”页面——但效率较低，所以尝试保持在8K以内通常是个好主意。
Marc
马克
#5
3
The size of a field should be appropriate for the data you are planning to store there, global defaults are not a good idea.
字段的大小应该适合您计划在那里存储的数据，全局默认值不是一个好主意。
#6
2
This totally depends on what you are storing. If you need x chars use x not some arbitrarily predefined amount.
这完全取决于存储的内容。如果您需要x字符，请使用x而不是任意预定义的数量。
#7
2
It's a good idea that the whole row fits into page several times without leaving too much free space.
这是一个好主意，整个行适合几次页而不留下太多的*空间。
A row cannot span two pages, an a page has 8096 bytes of free space, so two rows that take 4049 bytes each will occupy two pages.
一行不能跨越两个页面，一个A页有8096字节的空闲空间，因此每一行占用4049字节的两行将占用两个页面。
See docs on how to calculate the space occupied by one row.
请参阅文档，了解如何计算一行占用的空间。
Also note that VAR in VARCHAR and VARBINARY stands for "varying", so if you put a 1-byte value into a 50-byte column, it will take but 1 byte.
还要注意，VARCHAR和VARBINARY中的VAR表示“变化”，所以如果将一个1字节的值放入一个50字节的列中，那么只需要1个字节。