数据库中空值使用的空间 [英] Space used by nulls in database
问题描述
如果某列为空，是否会影响该列使用的空间?列定义使用的空间是否固定?这是否因数据库而异.(我主要对 SQL Server 2000 感兴趣.)
If a column is null, does that affect the space used by the column? Is the space used fixed by the column definition? Does this vary from database to database. (I am mainly interestred in SQL Server 2000.)
说明:问题与当该列可为空"时发生的情况无关(正如 Kritsen & gbn 指出的那样，这又花费了一点).问题是，当列实际上为空(在某些特定行中)时，是否有任何节省.
Clarification: The question relates not to what happens when the column is 'nullable' (This costs another bit as Kritsen & gbn pointed out). The question is, is there any saving when the column is actually null (in some particular row).
...
Cadaeic 为 SQL Server 提供了答案，在 2008 版之前似乎不会在 SQL Server 中节省成本，而根据 Quassnoi 的说法，如果空列在最后，您可以在 Oracle 中节省成本.感谢您的回答，他们都很有帮助.
Cadaeic provided the answer for SQL Server which seems to be no savings in SQL Server until version 2008, whereas according to Quassnoi you can get savings in Oracle if the null columns are at the end. Thanks for the answers, they were all helpful.
推荐答案
在列中存储 NULL 不会特别花费或节省空间.对于定长数据，仍然保留整个空间.
Storing a NULL in a column does not specifically cost or save space. For fixed-length data, the entire space is still reserved.
另一方面，变长数据只需要数据的长度加上开销来存储实际长度.比如一个VARCHAR(n)会用2个字节的数据来表示实际长度，所以需要的存储空间总是n+2.
On the other hand, variable-length data requires only the length of the data plus overhead to store the actual length. For example, a VARCHAR(n) will use 2 bytes of data to indicate that actual length, so the storage space required is always n+2.
另外，应该提到的是，如果 SET ANSI_PADDING ON，一个带有 NULL 值的 char(n) 将表现为一个 VARCHAR(n).
Additionally, it should be mentioned that if SET ANSI_PADDING ON, a char(n) with a NULL value will behave as a VARCHAR(n).
无论如何，在使用 SQL Server 2000 或 SQL Server 2005 时，您将无法通过存储 NULL 来节省空间".SQL Server 2008 引入了稀疏列的概念，它可以为主要为 NULL 的列节省空间.
No matter what, you will not recognize space "savings" from storing NULLs when using SQL Server 2000 or SQL Server 2005. SQL Server 2008 introduces the concept of a sparse column, which can yield savings for columns that are primarily NULL.
这篇关于数据库中空值使用的空间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
