SQL Server:Null VS 空字符串 [英] SQL Server: Null VS Empty String

问题描述

NULL 和 Empty Varchar 值如何存储在 SQL Server 中.如果我的 UI 上没有 string 字段的用户条目，我应该存储 NULL 还是 '' ?

How are the NULL and Empty Varchar values stored in SQL Server. And in case I have no user entry for a string field on my UI, should I store a NULL or a '' ?

推荐答案

有一篇不错的文章 这里 讨论了这一点.要带走的关键是表大小没有区别，但是有些用户更喜欢使用空字符串，因为它可以使查询更容易，因为没有 NULL 检查要做.您只需检查字符串是否为空.另一件需要注意的事情是 NULL 在关系数据库的上下文中意味着什么.这意味着指向字符字段的指针在行头中设置为 0x00，因此没有数据可访问.

There's a nice article here which discusses this point. Key things to take away are that there is no difference in table size, however some users prefer to use an empty string as it can make queries easier as there is not a NULL check to do. You just check if the string is empty. Another thing to note is what NULL means in the context of a relational database. It means that the pointer to the character field is set to 0x00 in the row's header, therefore no data to access.

更新有一篇详细的文章here 讨论了什么实际上是连续发生的

Update There's a detailed article here which talks about what is actually happening on a row basis

对于允许空值的列，每一行都有一个空位图.如果行中该列为空然后位图中的位为 1，否则为 0.

Each row has a null bitmap for columns that allow nulls. If the row in that column is null then a bit in the bitmap is 1 else it's 0.

对于可变大小的数据类型，实际大小为 0 字节.

For variable size datatypes the acctual size is 0 bytes.

对于固定大小的数据类型，实际大小是默认的数据类型大小以字节为单位设置为默认值(数字为 0，字符为 '').

For fixed size datatype the acctual size is the default datatype size in bytes set to default value (0 for numbers, '' for chars).

DBCC PAGE 的结果显示，NULL 和空字符串都占用零字节.

the result of DBCC PAGE shows that both NULL and empty strings both take up zero bytes.

这篇关于SQL Server:Null VS 空字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！