多少大小“Null"SQL Server 中的值 [英] How much size "Null" value takes in SQL Server

问题描述

我有一张大表，有 10 列.其中 4 个在大多数情况下保持为空.我有一个查询，它执行空值需要任何大小或没有大小(以字节为单位).我读了几篇文章，其中一些人说:

http://www.sql-server-citation.com/2009/12/common-mistakes-in-sql-server-part-4.html

<块引用>

有一种误解，认为如果表中有 NULL 值，它就不会占用存储空间.事实是，NULL 值占用空间 – 2 个字节

SQL:使用 NULL 值与默认值><块引用>

数据库中的 NULL 值是一个系统值，它占用一个字节的存储空间，表示一个值不存在，而不是空格或零或任何其他默认值.

您能否指导我了解空值占用的大小.

解决方案

如果字段为固定宽度，则存储 NULL 与任何其他值占用相同的空间 - 字段的宽度.

如果字段为可变宽度，则 NULL 值不占用空间.

除了存储空值所需的空间之外，还有一个可以为空的列的开销.对于每一行，每个可空列使用一位来标记该列的值是否为空.无论列是固定长度还是可变长度，都是如此.

<小时>

您在其他来源的信息中观察到的差异的原因:

• 第一篇文章的开头有点误导.这篇文章不是在谈论存储 NULL 值的成本，而是具有能力存储 NULL 的成本(即使列可以为空的成本).确实，使列可以为空需要一些存储空间，但是一旦完成，存储 NULL 所需的空间少于存储值所需的空间(对于可变宽度列).

• 第二个链接似乎是关于 Microsoft Access 的问题.我不知道 Access 如何存储 NULL 的细节，但如果它与 SQL Server 不同，我不会感到惊讶.

I have a large table with say 10 columns. 4 of them remains null most of the times. I have a query that does null value takes any size or no size in bytes. I read few articles some of them are saying :

http://www.sql-server-citation.com/2009/12/common-mistakes-in-sql-server-part-4.html

There is a misconception that if we have the NULL values in a table it doesn't occupy storage space. The fact is, a NULL value occupies space – 2 bytes

SQL: Using NULL values vs. default values

A NULL value in databases is a system value that takes up one byte of storage and indicates that a value is not present as opposed to a space or zero or any other default value.

Can you please guide me regarding the size taken by null value.

解决方案

If the field is fixed width storing NULL takes the same space as any other value - the width of the field.

If the field is variable width the NULL value takes up no space.

In addition to the space required to store a null value there is also an overhead for having a nullable column. For each row one bit is used per nullable column to mark whether the value for that column is null or not. This is true whether the column is fixed or variable length.

---

The reason for the discrepancies that you have observed in information from other sources:

• The start of the first article is a bit misleading. The article is not talking about the cost of storing a NULL value, but the cost of having the ability to store a NULL (i.e the cost of making a column nullable). It's true that it costs something in storage space to make a column nullable, but once you have done that it takes less space to store a NULL than it takes to store a value (for variable width columns).

• The second link seems to be a question about Microsoft Access. I don't know the details of how Access stores NULLs but I wouldn't be surprised if it is different to SQL Server.

这篇关于多少大小“Null"SQL Server 中的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！