I know NULL is not zero... nor it is empty string. But then what is the value of NULL... which the system keeps, to identify it?
我知道NULL不是零...也不是空字符串。但那么系统保留的NULL值是什么,以识别它?
7 个解决方案
#1
10
I believe that for each column that allow nulls, the rows have a null bitmap. If the row in the specified column is null the bit in the bitmap is 1 otherwise is 0.
我相信对于每个允许空值的列,行都有一个空位图。如果指定列中的行为空,则位图中的位为1,否则为0。
#2
20
NULL is a special element of the SQL language that is neither equal to or unequal to any value in any data type.
NULL是SQL语言的一个特殊元素,它既不等于或不等于任何数据类型中的任何值。
As you said, NULL is not zero, an empty string, or false. I.e. false = NULL returns UNKNOWN.
如上所述,NULL不是零,空字符串或false。即false = NULL返回UNKNOWN。
Some people say NULL is not a value, it's a state. The state of having no value. Sort of like a Zen Koan. :-)
有人说NULL不是一个值,它是一个状态。没有价值的状态。有点像Zen Koan。 :-)
I don't know specifically how MS SQL Server stores it internally, but it doesn't matter, as long as they implement it according to the SQL standard.
我不知道MS SQL Server内部如何存储它,但它没关系,只要它们根据SQL标准实现它。
#3
10
The SQL Server row format is described in MSDN, and also analyzed on various blogs, like Paul Randal's Anatomy of a Record. The important information is the record structure:
SQL Server中描述了SQL Server行格式,并在各种博客上进行了分析,例如Paul Randal的“记录剖析”。重要的信息是记录结构:
- record header
- 4 bytes long
4个字节长
- two bytes of record metadata (record type)
两个字节的记录元数据(记录类型)
- two bytes pointing forward in the record to the NULL bitmap
在记录中向前指向NULL位图的两个字节
记录头4个字节长两个字节的记录元数据(记录类型)两个字节在记录中指向前向NULL位图
- fixed length portion of the record, containing the columns storing data types that have fixed lengths (e.g. bigint, char(10), datetime)
记录的固定长度部分,包含存储具有固定长度的数据类型的列(例如bigint,char(10),datetime)
- NULL bitmap
- two bytes for count of columns in the record
两个字节用于记录中的列数
- variable number of bytes to store one bit per column in the record, regardless of whether the column is nullable or not
记录中每列存储一位的可变字节数,无论该列是否可为空
- this allows an optimization when reading columns that are NULL
这允许在读取NULL列时进行优化
NULL位图两个字节用于记录变量中的列数,用于在记录中每列存储一位,无论该列是否可为空这都允许在读取NULL列时进行优化
- variable-length column offset array
- two bytes for the count of variable-length columns
两个字节用于可变长度列的计数
- two bytes per variable length column, giving the offset to the end of the column value
每个可变长度列两个字节,给出列值末尾的偏移量
可变长度列偏移数组两个字节用于可变长度列的计数每个可变长度列两个字节,给出列值末尾的偏移量
- versioning tag
- a 14-byte structure that contains a timestamp plus a pointer into the version store in tempdb
一个14字节的结构,包含一个时间戳加上一个指向tempdb中版本存储的指针
versioning标记一个14字节的结构,它包含一个时间戳加上一个指向tempdb中版本存储的指针
So NULL fields have a bit set in the NULL bitmap.
所以NULL字段在NULL位图中设置了一个位。
#4
7
I doubt the interviewers wanted you to know exactly how SQL server stores nulls, the point of such a question is to get you to think about how you'd store special values. You can't use a sentinel value (or magic number), as that would make any rows with that value in them suddenly become null.
我怀疑采访者是否希望你确切地知道SQL服务器如何存储空值,这个问题的关键在于让你思考如何存储特殊值。您不能使用sentinel值(或幻数),因为这会使任何具有该值的行突然变为null。
There are quite a few ways to achieve this. The most straightforward 2 that come to mind are to have a flag stored with each nullable value that is basically an isNull flag (this is also basically how Nullable<T> works in .NET). A 2nd method is to store with each row a bitmap of null flags, one for each column.
有很多方法可以实现这一目标。想到的最直接的2是有一个标志存储每个可空值,这个值基本上是一个isNull标志(这也基本上是Nullable
When faced with such an interview questions the absolute worst response is to sit and stare blankly. Think out loud some, admit that you don't know how SQL Server does it, and then present some reasonable sounding ways of doing it. You should also be ready to talk a bit about why you'd pick one method over another, and what the pluses and minuses of each are.
面对这样的面试问题时,绝对最糟糕的反应是坐下来茫然地凝视。大声思考,承认你不知道SQL Server是如何做到的,然后提出一些合理的方法来做到这一点。您还应该准备好谈谈为什么选择一种方法而不是另一种方法,以及每种方法的优点和缺点。
#5
3
As Bill said, it's a state not a value.
正如比尔所说,这是一个没有价值的国家。
In a row in a table SQL Server it's stored in the null bitmap: no value is actually stored.
在表SQL Server中的一行中,它存储在空位图中:实际上没有存储任何值。
One quirk of SQL Server:
SQL Server的一个怪癖:
SELECT TOP 1 NULL AS foo INTO dbo.bar FROM sys.columns
What datatype is foo? It has no meaning of course, but it caught me out once.
什么数据类型是foo?它当然没有任何意义,但它让我抓了一次。
#6
0
Conceptually, NULL means “a missing unknown value” and it is treated somewhat differently from other values.
从概念上讲,NULL表示“缺少未知值”,并且与其他值的处理方式略有不同。
In MySQL, 0 or NULL means false and anything else means true. The default truth value from a boolean operation is 1.
在MySQL中,0或NULL表示false,其他任何表示都是true。布尔运算的默认真值为1。
Two NULL values are regarded as equal in a GROUP BY.
在GROUP BY中,两个NULL值被视为相等。
When doing an ORDER BY, NULL values are presented first if you do ORDER BY ... ASC and last if you do ORDER BY ... DESC.
在执行ORDER BY时,如果您执行ORDER BY ... ASC,则首先显示NULL值,如果执行ORDER BY ... DESC,则返回最后一个值。
#7
0
The value of NULL means in essence a missing value, some people will also use the term unknow
NULL的值本质上意味着缺失值,有些人也会使用术语unknow
A Null value is also not equal to anything not even another NULL
Null值也不等于任何其他NULL
Take a look at these examples
看看这些例子
will print is equal
将打印是平等的
IF 1 = 1
PRINT 'is equaL'
ELSE
PRINT 'NOT equal'
will print is equal (an implicit conversion happens)
将打印相等(发生隐式转换)
IF 1 = '1'
PRINT 'is equaL'
ELSE
PRINT 'NOT equal'
will print is not equal
将打印不相等
IF NULL = NULL
PRINT 'is equaL'
ELSE
PRINT 'NOT equal'
#1
10
I believe that for each column that allow nulls, the rows have a null bitmap. If the row in the specified column is null the bit in the bitmap is 1 otherwise is 0.
我相信对于每个允许空值的列,行都有一个空位图。如果指定列中的行为空,则位图中的位为1,否则为0。
#2
20
NULL is a special element of the SQL language that is neither equal to or unequal to any value in any data type.
NULL是SQL语言的一个特殊元素,它既不等于或不等于任何数据类型中的任何值。
As you said, NULL is not zero, an empty string, or false. I.e. false = NULL returns UNKNOWN.
如上所述,NULL不是零,空字符串或false。即false = NULL返回UNKNOWN。
Some people say NULL is not a value, it's a state. The state of having no value. Sort of like a Zen Koan. :-)
有人说NULL不是一个值,它是一个状态。没有价值的状态。有点像Zen Koan。 :-)
I don't know specifically how MS SQL Server stores it internally, but it doesn't matter, as long as they implement it according to the SQL standard.
我不知道MS SQL Server内部如何存储它,但它没关系,只要它们根据SQL标准实现它。
#3
10
The SQL Server row format is described in MSDN, and also analyzed on various blogs, like Paul Randal's Anatomy of a Record. The important information is the record structure:
SQL Server中描述了SQL Server行格式,并在各种博客上进行了分析,例如Paul Randal的“记录剖析”。重要的信息是记录结构:
- record header
- 4 bytes long
4个字节长
- two bytes of record metadata (record type)
两个字节的记录元数据(记录类型)
- two bytes pointing forward in the record to the NULL bitmap
在记录中向前指向NULL位图的两个字节
记录头4个字节长两个字节的记录元数据(记录类型)两个字节在记录中指向前向NULL位图
- fixed length portion of the record, containing the columns storing data types that have fixed lengths (e.g. bigint, char(10), datetime)
记录的固定长度部分,包含存储具有固定长度的数据类型的列(例如bigint,char(10),datetime)
- NULL bitmap
- two bytes for count of columns in the record
两个字节用于记录中的列数
- variable number of bytes to store one bit per column in the record, regardless of whether the column is nullable or not
记录中每列存储一位的可变字节数,无论该列是否可为空
- this allows an optimization when reading columns that are NULL
这允许在读取NULL列时进行优化
NULL位图两个字节用于记录变量中的列数,用于在记录中每列存储一位,无论该列是否可为空这都允许在读取NULL列时进行优化
- variable-length column offset array
- two bytes for the count of variable-length columns
两个字节用于可变长度列的计数
- two bytes per variable length column, giving the offset to the end of the column value
每个可变长度列两个字节,给出列值末尾的偏移量
可变长度列偏移数组两个字节用于可变长度列的计数每个可变长度列两个字节,给出列值末尾的偏移量
- versioning tag
- a 14-byte structure that contains a timestamp plus a pointer into the version store in tempdb
一个14字节的结构,包含一个时间戳加上一个指向tempdb中版本存储的指针
versioning标记一个14字节的结构,它包含一个时间戳加上一个指向tempdb中版本存储的指针
So NULL fields have a bit set in the NULL bitmap.
所以NULL字段在NULL位图中设置了一个位。
#4
7
I doubt the interviewers wanted you to know exactly how SQL server stores nulls, the point of such a question is to get you to think about how you'd store special values. You can't use a sentinel value (or magic number), as that would make any rows with that value in them suddenly become null.
我怀疑采访者是否希望你确切地知道SQL服务器如何存储空值,这个问题的关键在于让你思考如何存储特殊值。您不能使用sentinel值(或幻数),因为这会使任何具有该值的行突然变为null。
There are quite a few ways to achieve this. The most straightforward 2 that come to mind are to have a flag stored with each nullable value that is basically an isNull flag (this is also basically how Nullable<T> works in .NET). A 2nd method is to store with each row a bitmap of null flags, one for each column.
有很多方法可以实现这一目标。想到的最直接的2是有一个标志存储每个可空值,这个值基本上是一个isNull标志(这也基本上是Nullable
When faced with such an interview questions the absolute worst response is to sit and stare blankly. Think out loud some, admit that you don't know how SQL Server does it, and then present some reasonable sounding ways of doing it. You should also be ready to talk a bit about why you'd pick one method over another, and what the pluses and minuses of each are.
面对这样的面试问题时,绝对最糟糕的反应是坐下来茫然地凝视。大声思考,承认你不知道SQL Server是如何做到的,然后提出一些合理的方法来做到这一点。您还应该准备好谈谈为什么选择一种方法而不是另一种方法,以及每种方法的优点和缺点。
#5
3
As Bill said, it's a state not a value.
正如比尔所说,这是一个没有价值的国家。
In a row in a table SQL Server it's stored in the null bitmap: no value is actually stored.
在表SQL Server中的一行中,它存储在空位图中:实际上没有存储任何值。
One quirk of SQL Server:
SQL Server的一个怪癖:
SELECT TOP 1 NULL AS foo INTO dbo.bar FROM sys.columns
What datatype is foo? It has no meaning of course, but it caught me out once.
什么数据类型是foo?它当然没有任何意义,但它让我抓了一次。
#6
0
Conceptually, NULL means “a missing unknown value” and it is treated somewhat differently from other values.
从概念上讲,NULL表示“缺少未知值”,并且与其他值的处理方式略有不同。
In MySQL, 0 or NULL means false and anything else means true. The default truth value from a boolean operation is 1.
在MySQL中,0或NULL表示false,其他任何表示都是true。布尔运算的默认真值为1。
Two NULL values are regarded as equal in a GROUP BY.
在GROUP BY中,两个NULL值被视为相等。
When doing an ORDER BY, NULL values are presented first if you do ORDER BY ... ASC and last if you do ORDER BY ... DESC.
在执行ORDER BY时,如果您执行ORDER BY ... ASC,则首先显示NULL值,如果执行ORDER BY ... DESC,则返回最后一个值。
#7
0
The value of NULL means in essence a missing value, some people will also use the term unknow
NULL的值本质上意味着缺失值,有些人也会使用术语unknow
A Null value is also not equal to anything not even another NULL
Null值也不等于任何其他NULL
Take a look at these examples
看看这些例子
will print is equal
将打印是平等的
IF 1 = 1
PRINT 'is equaL'
ELSE
PRINT 'NOT equal'
will print is equal (an implicit conversion happens)
将打印相等(发生隐式转换)
IF 1 = '1'
PRINT 'is equaL'
ELSE
PRINT 'NOT equal'
will print is not equal
将打印不相等
IF NULL = NULL
PRINT 'is equaL'
ELSE
PRINT 'NOT equal'