January 2, 2008 at 11:43 am
Kenneth Fisher (1/2/2008)
Ok probably one of the more interesting discussions on NULL and comparison operators but I have a question. I feel like I missed the 1st half of the discussion. Where is the original question/statement that Jeff posted?
He's posted it in several threads. It's one of the interview questions he asks people, especially if they claim to be experienced or senior or the like.
Here's one: Lighter fare - Doh! Querys
Also just to be sure I understand correctly. A <> is ALWAYS going to do a table scan? And from what you said earlier Jeff
SELECT * FROM sometable WHERE somecolumn > 0 OR somecolumn < 0
will also do a table scan and can't be optimized?
It honestly depends on the data in the table. If all the values of that column are in the range 1..100, then a query that asks for rows <> 0 will have to do a table/index scan. All the data is needed. A scan is fastest in that case.
If 80% of the data has that column 0, then you'll probably get a partial scan (appears on the execution plan as an index seek)
If you look at the execution plan for a query that has WHERE somecolumn != 0, then you'll notice that the condition gets expanded to somecolumn > 0 OR somecolumn < 0. It's how the query compiler works.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 4, 2008 at 12:56 am
And,Databases treat NULL values in a special way, depending upon the type of operation that it is used in.
1) When a NULL value appears as an operand to an AND operation, the operation’s value is FALSE if the other operand is FALSE (there is no way the expression could be TRUE with one FALSE operand). On the other hand, the result is NULL (unknown) if the other operand is either TRUE or NULL (because we can’t tell what the result would be.)
2) The OR operand treats NULL values in a similar fashion. If the other operand is TRUE, the result of the OR is TRUE (because the real value of the NULL operand doesn’t matter.) On the other hand, if the other operand is either FALSE or NULL, the result of the OR operation is NULL.
I also got a chance to read the below one from the following URL.
http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.dapip.doc/dapip73.htm">
http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.dapip.doc/dapip73.htm
The SQL NULL value represents a null or empty value in a database column. The NULL value is distinct from all valid values for a given data type. For example, the INTEGER data type holds a four-byte integer. This four-byte data type can hold 232 (or 4,294,967,296) values:
zero (0)
positive values: 1 to 2,147,483,647
negative values: -1 to -2,147,483,647
NULL value: 2,147,483,648 (the maximum negative number)
Because the representation of the NULL value is unique to each data type.
karthik
January 4, 2008 at 1:07 am
karthikeyan (1/4/2008)
The SQL NULL value represents a null or empty value in a database column. The NULL value is distinct from all valid values for a given data type. For example, the INTEGER data type holds a four-byte integer. This four-byte data type can hold 232 (or 4,294,967,296) values:zero (0)
positive values: 1 to 2,147,483,647
negative values: -1 to -2,147,483,647
NULL value: 2,147,483,648 (the maximum negative number)
Because the representation of the NULL value is unique to each data type.
That may be true on Informix, but it's not true on SQL. Null values are not stored in the data pages. There's no special value that is null. Rather there's a null bitmap within a row that shows which columns are null
On SQL, the max negative value for an int is-2,147,483,648 and the max positive is 2,147,483,647.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 4, 2008 at 1:07 am
karthikeyan (1/4/2008)
And,Databases treat NULL values in a special way, depending upon the type of operation that it is used in.1) When a NULL value appears as an operand to an AND operation, the operation’s value is FALSE if the other operand is FALSE (there is no way the expression could be TRUE with one FALSE operand). On the other hand, the result is NULL (unknown) if the other operand is either TRUE or NULL (because we can’t tell what the result would be.)
2) The OR operand treats NULL values in a similar fashion. If the other operand is TRUE, the result of the OR is TRUE (because the real value of the NULL operand doesn’t matter.) On the other hand, if the other operand is either FALSE or NULL, the result of the OR operation is NULL.
I also got a chance to read the below one from the following URL.
http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.dapip.doc/dapip73.htm">
http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.dapip.doc/dapip73.htm
The SQL NULL value represents a null or empty value in a database column. The NULL value is distinct from all valid values for a given data type. For example, the INTEGER data type holds a four-byte integer. This four-byte data type can hold 232 (or 4,294,967,296) values:
zero (0)
positive values: 1 to 2,147,483,647
negative values: -1 to -2,147,483,647
NULL value: 2,147,483,648 (the maximum negative number)
Because the representation of the NULL value is unique to each data type.
Following runs without error:
DECLARE @I int
SELECT @I = -2147483648
SELECT @I
In SQL Server , all representation of the NULL value are the same.
Nullable datatypes are one byte larger than non-nullable ones.
January 4, 2008 at 1:08 am
I have also gathered some points about NULL in some websites. It is here.
1) Null is untyped in SQL, meaning that it is not designated as an integer, character, or any other specific data type.[4] Because of this, it is sometimes mandatory (or desirable) to explicitly convert Nulls to a specific data type.
2) SQL CASE expressions operate under the same rules as the DML WHERE clause rules for Null. Because it can be evaluated as a series of equality comparison conditions, a simple CASE expression cannot check for the existence of Null directly. A check for Null in a simple CASE expression always results in Unknown, as in the following:
SELECT CASE i WHEN NULL THEN 'Is Null' -- This will never be returned
WHEN 0 THEN 'Is Zero' -- This will be returned when i = 0
WHEN 1 THEN 'Is One' -- This will be returned when i = 1
END
FROM t
Because the expression i = NULL evaluates to Unknown no matter what value column i contains (even if it contains Null), the string 'Is Null' will never be returned.
A searched CASE expression also returns the first value for which the result of the comparison predicate evaluates to True, including comparisons using the IS NULL and IS NOT NULL comparison predicates. The following example shows how to use a searched CASE expression to properly check for Null:
SELECT CASE WHEN i IS NULL THEN 'Null Result' -- This will be returned when i is NULL
WHEN i = 0 THEN 'Zero' -- This will be returned when
i = 0
WHEN i = 1 THEN 'One' -- This will be returned when
i = 1
END
FROM t
3)Because Null is not a data value, but a marker for an unknown value, using mathematical operators on Null results in an unknown value, which is represented by Null.[8] In the following example, multiplying 10 by Null results in Null:
10 * NULL -- Result is NULL
This can lead to unanticipated results. For instance, when an attempt is made to divide Null by zero, platforms may return Null instead of throwing an expected "data exception - division by zero".In the example below, the Oracle, Postgresql and Microsoft SQL Server platforms all return a Null result.
NULL / 0
String concatenation operations, which are common in SQL, also result in Null when one of the operands is Null.[9] The following example demonstrates the Null result returned by using Null with the SQL || string concatenation operator.
'Fish ' || NULL || 'Chips' -- Result is NULL
karthik
January 4, 2008 at 1:18 am
Koji Matsumura (1/4/2008)
In SQL Server , all representation of the NULL value are the same.Nullable datatypes are one byte larger than non-nullable ones.
Null and not null datatypes are the same size. In SQL Server, the null bitmap that's stored within the row shows whether or not a particular column is null for that row.
The null bitmap stores one bit per column in the record, regardless of whether the column is nullable or not
For more details on the null bitmap, see Paul Randel's post on the anatomy of a record
Quoting from that...
Paul Randal
Having a null bitmap removes the need for storing special 'NULL' values for fixed-length datatypes. Without the null bitmap, how can you tell whether a column is NULL? For fixed-length columns you'd need to define a special 'NULL' value, which limits the effective range of the datatype being stored. For varchar columns, the value could be a zero-length empty string, so just checking the length doesn't work - you'd need the special value again. For all other variable-length data types you can just check the length.
I'm not sure about variables. For them there's probably a flag witing the definition of the variable.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 4, 2008 at 1:28 am
GilaMonster (1/4/2008)
Koji Matsumura (1/4/2008)
In SQL Server , all representation of the NULL value are the same.Nullable datatypes are one byte larger than non-nullable ones.
Null and not null datatypes are the same size. In SQL Server, the null bitmap that's stored within the row shows whether or not a particular column is null for that row.
The null bitmap stores one bit per column in the record, regardless of whether the column is nullable or not
For more details on the null bitmap, see Paul Randel's post on the anatomy of a record
Quoting from that...
Paul Randal
Having a null bitmap removes the need for storing special 'NULL' values for fixed-length datatypes. Without the null bitmap, how can you tell whether a column is NULL? For fixed-length columns you'd need to define a special 'NULL' value, which limits the effective range of the datatype being stored. For varchar columns, the value could be a zero-length empty string, so just checking the length doesn't work - you'd need the special value again. For all other variable-length data types you can just check the length.
I'm not sure about variables. For them there's probably a flag witing the definition of the variable.
Thank you Gail for the info.
I guess I was somehow confused with char VS varchar datatype.
By the way, how do we know that even when a column is not nullable, still null bitmap is there?
January 4, 2008 at 1:38 am
Koji Matsumura (1/4/2008)[hr
Thank you Gail for the info.
I guess I was somehow confused with char VS varchar datatype.
Yeah. The varxxx datatypes have 2 bytes extra to store the data size
By the way, how do we know that even when a column is not nullable, still null bitmap is there?
Because a former member of the storage engine team said so? 😉
Have a look at Paul Randal's blog. He's got several post on the physical structure of rows, pages, extents and the like, if you're interested in that. He also shows some undocumented commands to read the physical constructs.
The post I linked to 2 posts up is a good place to start.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 4, 2008 at 1:43 am
GilaMonster (1/4/2008)
Koji Matsumura (1/4/2008)[hr
Thank you Gail for the info.
I guess I was somehow confused with char VS varchar datatype.
Yeah. The varxxx datatypes have 2 bytes extra to store the data size
By the way, how do we know that even when a column is not nullable, still null bitmap is there?
Because a former member of the storage engine team said so? 😉
Have a look at Paul Randal's blog. He's got several post on the physical structure of rows, pages, extents and the like, if you're interested in that. He also shows some undocumented commands to read the physical constructs.
The post I linked to above is a good place to start.
Thanks again Gail.
January 4, 2008 at 1:48 am
Pleasure
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 4, 2008 at 8:27 am
GilaMonster (1/4/2008)
Koji Matsumura (1/4/2008)[hr
Thank you Gail for the info.
I guess I was somehow confused with char VS varchar datatype.
Yeah. The varxxx datatypes have 2 bytes extra to store the data size
Some confusion also comes in there because there are times when ANSI padding is turned off (or rather - left in its default state of OFF), fixed width NULLABLE character fields are treated internally as variable-width character fields. So -
SET ANSI_PADDDING OFF
GO
create table table1( mychar as char(20) null)
is the same as defining mychar as varchar(20).
Of course - it kind of makes sense when you sit down and think about it for a while (a fixed-width field that sometimes takes things with NO width isn't really a fixed width field now is it?).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 7, 2008 at 12:44 am
SET ANSI_PADDDING OFF
is it good practise to use "SET ANSI_PADDDING OFF" in production server ?If yes (or)not,How and why?
karthik
January 7, 2008 at 12:50 am
Generally I would recommend leaving the connection settings at their default values, unless you have a good reason to change them, and you know exactly what changing them will do.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 7, 2008 at 12:59 am
Create table Emp
(
Eno int,
Ename varchar(50),
SurName varchar(50) NULL
)
Insert into Emp Values ( 1,'AAAA',null)
(or)
Insert into Emp(Eno,Ename)values( 1,'AAAA')
1) Is there any difference between these two statements ?Both can
insert the data.
2) Which method should i follow,if i face the above situation ?
3) Which one is the optimized or better way to insert?
Also,
How NULL's are treated in memory?i.e How NULL values are get stored in memory or data page ?
I think NULL's are always cause confuse for the beginners.
karthik
January 7, 2008 at 1:11 am
You should always specify column names in an insert. Otherwise if the structure of the table changes, you code breaks. Whether you explicitly state default values, or leave them out is up to you.
For info on how things are stored, read Paul Randal's blog. I linked to it a couple posts earlier.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 31 through 45 (of 57 total)
You must be logged in to reply to this topic. Login to reply