DEFAULT keyword performance

I have a function which performs a query and returns a table. The one
parameter that can get passed in is a date which defaults to NULL.
There is an IF statement in the function that will set the paramter to
an actual date if null. If I call the function while passing in a date
the function comes back a second or 2 later. But if I pass in DEFAULT
to the function, the same query takes 8 minutes. See code below and
sample call below.

CREATE FUNCTION fCalculateProfitLossFromClearing (
@.TradeDate DATETIME = NULL
)
RETURNS @.t TABLE (
[TradeDate] DATETIME,
[Symbol] VARCHAR(15),
[Identity] VARCHAR(15),
[Exchange] VARCHAR(5),
[Account] VARCHAR(10),
[Value] DECIMAL(18, 6)
)
AS
BEGIN
-- Use previous trading date if none specified
IF @.TradeDate IS NULL
SET @.TradeDate = Supporting.dbo.GetPreviousTradeDate()

-- Make the query
INSERT @.t
SELECT
@.TradeDate,
tblTrade.[Symbol],
tblTrade.[Identity],
tblTrade.[Exchange],
tblTrade.[Account],
SUM((CASE tblTrade.[Side] WHEN 'B' THEN -ABS(tblTrade.[Quantity])
ELSE ABS(tblTrade.[Quantity]) END) * (tblPos.[ClosingPrice] -
tblTrade.[Price])) AS [Value]
FROM
Historical.dbo.ClearingTrade tblTrade
LEFT JOIN Historical.dbo.ClearingPosition tblPos ON (@.TradeDate =
tblPos.[TradeDate] AND tblTrade.[Symbol] = tblPos.[Symbol] AND
tblTrade.[Identity] = tblPos.[Identity])
WHERE
([TradeTimestamp] >= @.TradeDate AND [TradeTimestamp] < DATEADD(DAY,
1, @.TradeDate))
GROUP BY tblTrade.[Symbol],tblTrade.[Identity],tblTrade.[Exchange],tblTrade.[Account]

RETURN
END

If I call the function as

SELECT * FROM fCalculateProfitLossFromClearing('09/25/2003')

it returns in 2 seconds.

If I call the function as

SELECT * FROM fCalculateProfitLossFromClearing(DEFAULT)

in which GetPreviousTradeDate() will set @.TradeDate to 09/25/2003 it
returns in 8 minutes.[posted and mailed, please reply in news]

Jason (JayCallas@.hotmail.com) writes:
> I have a function which performs a query and returns a table. The one
> parameter that can get passed in is a date which defaults to NULL.
> There is an IF statement in the function that will set the paramter to
> an actual date if null. If I call the function while passing in a date
> the function comes back a second or 2 later. But if I pass in DEFAULT
> to the function, the same query takes 8 minutes. See code below and
> sample call below.

The query seems familiar. :-)

The reason for this is that when SQL Server builds the query plan,
it considers the value of the input parameter. When you provide an
explicit date, SQL Server can consult the statistics for the table
and see that the value you provided is very selective, and use the
index.

But if you provide NULL, SQL Server will build the query plan on that
assumption. Obviously a NULL value would return no rows, but SQL Server
never makes any assumptions that could yield incorrect results. Since
you WHERE condition is for a range, SQL Server appears to prefer to
scan the table, than using a non-clustered index.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>
> The query seems familiar. :-)
> The reason for this is that when SQL Server builds the query plan,
> it considers the value of the input parameter. When you provide an
> explicit date, SQL Server can consult the statistics for the table
> and see that the value you provided is very selective, and use the
> index.
> But if you provide NULL, SQL Server will build the query plan on that
> assumption. Obviously a NULL value would return no rows, but SQL Server
> never makes any assumptions that could yield incorrect results. Since
> you WHERE condition is for a range, SQL Server appears to prefer to
> scan the table, than using a non-clustered index.

I hate the restart this thread but I have hit a brick wall...

I am at a lose of whether to creat functions or stored procedures (or
even views).

The below questions/issues are based on a need to return information
on a particular date for one to many symbols.

For my example lets say 15 symbols. You could do a query like Symbol =
'a' OR Symbol = 'b' OR Symbol... but it would make more sense to do
Symbol IN ('a','b',...))

I would also like to give my functions and stored procedures to use a
default date if none is specified. I created a function named
GetPreviousTradeDate() which does this based on a calendar.

SO here is how I see it.

Stored procedures seem to be the fastest in terms of returning data
back. But they seem to be limited in the sense that they can return
ONE row or ALL the rows since you cannot pass in a variable list of
symbols. You also cannot use the SP as part of a query. You could just
return all the rows back to the client and do filter or searching on
that end but that does not seem efficient or professional.

A function also does not let you pass in a variable list of symbols
but at least you can use it in a query. You could do something like
SELECT * FROM TheFunction() WHERE Symbol IN ('a','b',...). All this
happens at the server side and only the needed rows gets sent back.

But functions seem to have MAJOR performance problems when default
values are passed in. When I pass in a specific date the query takes a
few seconds but when I pass in DEFAULT and set the date to the results
of the GetPreviousTradeDate() function the query takes anywhere from 8
minutes to 15 minutes. (This even happens if I do not use the
GetPreviousTradeDate() function and set my parameter to a hard-coded
value)

Any thoughts or comments would be appreciated.|||Jason (JayCallas@.hotmail.com) writes:
> A function also does not let you pass in a variable list of symbols
> but at least you can use it in a query. You could do something like
> SELECT * FROM TheFunction() WHERE Symbol IN ('a','b',...). All this
> happens at the server side and only the needed rows gets sent back.

I happen to have an article on my web site that discusses this case
in detail. You don't have to read all of it, but you can just get
the function you need at
http://www.algonet.se/~sommar/array...html#iterative.

> But functions seem to have MAJOR performance problems when default
> values are passed in. When I pass in a specific date the query takes a
> few seconds but when I pass in DEFAULT and set the date to the results
> of the GetPreviousTradeDate() function the query takes anywhere from 8
> minutes to 15 minutes. (This even happens if I do not use the
> GetPreviousTradeDate() function and set my parameter to a hard-coded
> value)

The difference is not always that big, but apparently your query is
sensitive for this. I suggest that you split up the procedure in two:

EXEC outer_sp @.date = NULL datetime
IF @.date IS NULL
SELECT @.date = dbo.yourfunctionfordefault()
EXEC inner_sp @.date

And then inner_sp includes the actual query.

For a long treatise on this subject, search on Google news for articles
by Bart Duncan (a escalation engineer at Microsoft) and the phrase
"parameter sniffing".

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||JayCallas@.hotmail.com (Jason) wrote in message news:<f01a7c89.0310141510.28e9c846@.posting.google.com>...
> I hate the restart this thread but I have hit a brick wall...
> I am at a lose of whether to creat functions or stored procedures (or
> even views).
> The below questions/issues are based on a need to return information
> on a particular date for one to many symbols.
> For my example lets say 15 symbols. You could do a query like Symbol =
> 'a' OR Symbol = 'b' OR Symbol... but it would make more sense to do
> Symbol IN ('a','b',...))
> I would also like to give my functions and stored procedures to use a
> default date if none is specified. I created a function named
> GetPreviousTradeDate() which does this based on a calendar.
> SO here is how I see it.
> Stored procedures seem to be the fastest in terms of returning data
> back. But they seem to be limited in the sense that they can return
> ONE row or ALL the rows since you cannot pass in a variable list of
> symbols. You also cannot use the SP as part of a query. You could just
> return all the rows back to the client and do filter or searching on
> that end but that does not seem efficient or professional.
> A function also does not let you pass in a variable list of symbols
> but at least you can use it in a query. You could do something like
> SELECT * FROM TheFunction() WHERE Symbol IN ('a','b',...). All this
> happens at the server side and only the needed rows gets sent back.
> But functions seem to have MAJOR performance problems when default
> values are passed in. When I pass in a specific date the query takes a
> few seconds but when I pass in DEFAULT and set the date to the results
> of the GetPreviousTradeDate() function the query takes anywhere from 8
> minutes to 15 minutes. (This even happens if I do not use the
> GetPreviousTradeDate() function and set my parameter to a hard-coded
> value)
> Any thoughts or comments would be appreciated.

Since the stored procedure has both the speed and the ability to use
default values without performance hit, would it be normal practice or
efficient to send separate queries for each symbol to the stored
procedure? This could result in anywhere from a few to several hundred
calls at a time.|||Jason (JayCallas@.hotmail.com) writes:
> Since the stored procedure has both the speed and the ability to use
> default values without performance hit, would it be normal practice or
> efficient to send separate queries for each symbol to the stored
> procedure? This could result in anywhere from a few to several hundred
> calls at a time.

That does not seem like a good idea. Certainly more efficient to get
data for all symbols at once. See my previous post for suggestions.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>> A function also does not let you pass in a variable list of symbols
but at least you can use it in a query ... Any thoughts or comments
would be appreciated. <<

Ever try putting the list of symbols into a one column table and using
an "IN (SELECT parm FROM Parmlist)" instead?

Default Field Value

Is there a way to set default values for a numeric field? I have several
fields that sometimes are null. I want the nulls to show as zero. I tried
iif(value is null, 0.00 , value) but it gives an error on the â'nullâ' word. I
tried â'IsNullâ', â'IsNothingâ' and â'IsNumericâ' all with the same error.
Any ideas?Try
iif(value=Nothing, 0.00 , value)
"tachtenberg" <tachtenberg@.discussions.microsoft.com> escribió en el mensaje
news:9FF1A567-6AB0-402F-A542-8205EB6AA195@.microsoft.com...
> Is there a way to set default values for a numeric field? I have several
> fields that sometimes are null. I want the nulls to show as zero. I
> tried
> iif(value is null, 0.00 , value) but it gives an error on the "null" word.
> I
> tried "IsNull", "IsNothing" and "IsNumeric" all with the same error.
> Any ideas?
>

Default date in a derived column expression

one of my SSIS packages use this expression to put todays date in if it is NULL:

(ISNULL(datejs)) ? GETDATE() : datejs

however, what I really want to do though is put a default date in like '2007-01-01' but I get syntax error because SSIS thinks it's a string, which it is I suppose.

Is it possible to do what I want it to?

Thanks

Try casting it

(ISNULL(datejs)) ? (DT_DBTIMESTAMP)"2007-01-01" : datejs

|||Fresh from the Integration Services Expression Reference section of Books Online, we have the GETDATE (http://msdn2.microsoft.com/en-US/library/ms139875.aspx) topic. So using GETDATE is possible, but sounds like a variable or literal casted as you suggest is actually what is needed here.|||

Larry Charlton wrote:

Try casting it

(ISNULL(datejs)) ? (DT_DBTIMESTAMP)"2007-01-01" : datejs

That's done it. Thank You.

DEFAULT constraint not working

I have a 32 column table. Every column is NOT NULL, and all but the first have DEFAULT constraints. In particular, the 21st column has such a constraint. I also have a stored proc which truncates the table and then loads it with a SELECT from a view, like this

TRUNCATE TABLE NDRS_Call_Data_Table

INSERT INTO NDRS_Call_Data_Table(<column-list>)
SELECT <column-list> FROM NDRS_Call_Data_View

The view is a complex join of several other tables. When I run the proc, I get the error:

"Cannot insert the value NULL into column 'CC_Time', table 'Tomcat_prod.dbo.NDRS_Call_Data_Table'; column does not allow nulls. INSERT fails."

BUT, if I simply do this from Query Analyzer.....

INSERT INTO NDRS_Calls_Data_Table(data_indicator)
VALUES('Z')

it works. The default constraint on the CC_Time column works correctly and supplies the default value of '0 ' as it should. (the column is a CHAR(2) column, despite the name suggesting it is datetime)

Recompiling the view and the stored procedure does not help. Anybody else seen this? Is this a known bug?You constraint works fine. I believe the null value comes from the view. As you said, the view was created from a complex join. Somewhere in the resultset it returns a null value in the view. I would run the query and check the resultset closely.|||Got it. Misunderstanding on the operation of DEFAULT constraints. They don't override explicit nulls if you specify the column in the insert. They only provide values if you leave out the column in the column list. Thanks|||Hi,

Just for my understanding.

Is column "CC_Time" included in <column-list> in your insert statement or not ?

If not, do you have any "after insert" triggers on table "Call_Data_Table" ?

CVM.|||Originally posted by cvandemaele
Hi,

Just for my understanding.

Is column "CC_Time" included in <column-list> in your insert statement or not ?

If not, do you have any "after insert" triggers on table "Call_Data_Table" ?

CVM.

Well, forget about this last post. Just getting a cup of coffee and WHAM problem solved.|||Further research shows that this is ANSI standard behavior. A NOT NULL column with a DEFAULT will not use the default if the column is specified in the insert's column list and NULL is provided. Besides being counter-intuitive, frankly this strikes me as dumb. It really gets in the way of using the INSERT ... SELECT syntax|||To solve the problems of nulls in your view, you can use isnull(a, b) where a is the selected column and b is the default value for that column.

example:

select isnull (title, 'No title') as title, au_lname
from authors a left join titleauthor ta on a.au_id = ta.au_id left join titles t on t.title_id = ta.title_id|||True.

Undortunately, when using INSERT ... SELECT you can't say
SELECT ..., ISNULL(column, DEFAULT), ...

the way you can use the DEFAULT keyword like this...

INSERT
VALUES (x,y,z,DEFAULT,p,q,r)

You have to know what the default value is, and explicitly put it in as the 2nd parameter of the ISNULL function. So if you ever change the default constraint, you'll have to go back and change all the canned queries, too.|||you can declare a variable for each column that you want default to be inserted in place of null, and then initialize them according to each variable data type:

declare @.value int
select @.value = cast(replace(replace(m.text, ')', ''), '(', '') as int)
from syscomments m,syscolumns c,sysobjects o
where c.id=object_id('dbo.your_table')
and c.name=('your_column_name')
and o.type='d'
and m.id=c.cdefault
and m.id=o.id

Decrypting returns a NULL value

I find it weird when decrypting a column from a baked up database and restoring it to another database. Here's the scenario:

Server1 has Database1.
Database1 has Table1 with two columns encyrpted -- Card Number and SS Number
Encryption and decryption in this Database1 is perfectly fine. Records are encrypted and can be decrypted too.
Now, I tried to backup this Database1 and restore it to another server with SQL 2005 instance called Server2. Of course the columns Card and SS Numbers were encrypted. I tried decrypting the columns using the same command to decrypt in Database1, however, it returns a NULL value

Here's exactly what I did to create the encyprtion and decryption keys on the restored database:
-- Create the master key encryption
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'myMasterPassword'

-- Create a symetric key
CREATE SYMMETRIC KEY myKey WITH ALGORITHM = DES
ENCRYPTION BY Password='myPassword';
Go

-- Create Card Certificate
CREATE CERTIFICATE myCert WITH SUBJECT = 'My Certificate on this Server';
GO

-- Change symmetric key
OPEN SYMMETRIC KEY myKey DECRYPTION BY PASSWORD = 'myPassword';

-- I then verified if the key is opened
SELECT * FROM sys.openkeys

If I create a new database, say Database2 from that Server2, create table, master key, certificate, and symmetric key. Encrpytion and decryption on Database2 will work!

Any suggestions gurus? I tried all searches and help for almost 2 weeks regarding this issue but nobody could resolve this.

Thanks in advance!

faiga16

3 Posts

I wish I could be more helpful, but all I have to offer is that I think it has to do with your Server master key being different. I was just at a conference where this exact scenario was mentioned and there is a way to use the password (or whatever it is called) to re-encrypt the restored database with the master key so that you can decrypt it.

[ed] sorry it looks like I missed the end of your post. It looks like you're already headed down the path I suggested.

|||

If the two servers are the same OS then I think all you will need to do is run:

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

to the new server.

hth,

-Steven Gott

S/DET

SQL Server

|||

Hi Steve,

Yes they have the same OS and SQL version. But executing

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

doesnt work too... it prompt an error saying "Create a master key in the database or open the master key in the session before performing this operation"

Is my procedure correct base on what you suggest?

1) restored the Database1

2) run ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

|||

I am getting to know my problem.

Before the alter master key command I opened the master key by open master key decryption by password command. Now it prompts an error that the decryption password I supplied was incorrect. Is there a way that I could know the master key encryption/decryption password? I just got here with all the encryption/decryption set up. Nobody remember the master key password duh!

|||

They are quite sure the master key they gave me was the one they used before.

"The key is not decrypted using the specified decryptor"

|||

From the error you are getting back, it seems like the password to decrypt the master key (DBMK) is not correct. The passwords are case sensitive; make sure it is typed exactly as it was originally created.

If you have a backup of the DBMK, you can try restoring the backup on top of the current copy (it should be the same DBMK) using the RESTORE MASTER KEY … FORCE statement.

NOTE: As you are using the force option, make sure you have a copy of the original DB in case the DBMK from the backup doesn’t match the one in your DB.

If the original server still exists, there may be one possibility: you can try to restore the DB on the original server and see if you can still access the DBMK based on the original service master key encryption. From there you can try to regenerate the DBMK (ALTER MASTER KEY REGENERATE) to establish a new DBMK password. I am not sure if this one will work, but it may be worth trying it.

I hope this helps.

-Raul Garcia

SDE/T

SQL Server Engine

|||

Thanks! Will test if this would work...

Decrypt sproc returning NULL to non DBO.

I'm still having issues with this despite my attempts to resolve. I even
have "with exec as dbo" in my sproc, and and "exec as dbo" in my execution,
but still the encrypted data returns nulls when I exec as a user other than
DBO. Below is precisely what I have done. All ideas are welcomed.

TIA, ChrisR

--If there is no master key, create one now

IF NOT EXISTS
(SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
CREATE MASTER KEY ENCRYPTION BY
PASSWORD =
'23987hxJKL95QYV4369#ghf0%94467GRdkjuw54ie5y01478d Dkjdahflkujaslekjg5k3fd117
r$$#1946kcj$n44ncjhdlj'
GO

CREATE CERTIFICATE HumanResources037
WITH SUBJECT = 'Employee Social Security Numbers';
GO

CREATE SYMMETRIC KEY SSN_Key_01
WITH ALGORITHM = DES
ENCRYPTION BY CERTIFICATE HumanResources037;
GO

USE [AdventureWorks];
GO

-- Create a column in which to store the encrypted data
ALTER TABLE HumanResources.Employee
ADD EncryptedNationalIDNumber varbinary(128);
GO

-- Open the symmetric key with which to encrypt the data
OPEN SYMMETRIC KEY SSN_Key_01
DECRYPTION BY CERTIFICATE HumanResources037;

-- Encrypt the value in column NationalIDNumber with symmetric
-- key SSN_Key_01. Save the result in column EncryptedNationalIDNumber.
UPDATE HumanResources.Employee
SET EncryptedNationalIDNumber = EncryptByKey(Key_GUID('SSN_Key_01'),
NationalIDNumber);
GO

-- Verify the encryption.
-- First, open the symmetric key with which to decrypt the data
OPEN SYMMETRIC KEY SSN_Key_01
DECRYPTION BY CERTIFICATE HumanResources037;
GO

-- Now list the original ID, the encrypted ID, and the
-- decrypted ciphertext. If the decryption worked, the original
-- and the decrypted ID will match.

alter procedure getDecryptedIDNumber
with exec as owner
as
SELECT NationalIDNumber, EncryptedNationalIDNumber
AS "Encrypted ID Number",
CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber))
AS "Decrypted ID Number"
FROM HumanResources.Employee;
GO

/*works for me, shows the decrypted data*/

exec getDecryptedIDNumber

USE [master]
GO

CREATE LOGIN [test] WITH PASSWORD=N'test',
DEFAULT_DATABASE=[AdventureWorks], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

USE [AdventureWorks]
GO

CREATE USER [test] FOR LOGIN [test]
GO

use [AdventureWorks]
GO

GRANT EXECUTE ON [dbo].[getDecryptedIDNumber] TO [test]
GO

GRANT IMPERSONATE ON USER:: dbo TO test;
GO

/*Now, open up a "file/new/DB Engine Query" and login with the test login*/
exec as user = 'dbo'
exec getDecryptedIDNumber

/*This returns NULL values where it should show the decrypted data*/I have made some changes to the scripts, but the outcome is the same. Everything needed is below, so all ideas are welcomed. Also, please make sure to use these scripts, not the last ones.

USE [AdventureWorks];
GO

IF NOT EXISTS
(SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
CREATE MASTER KEY ENCRYPTION BY
PASSWORD =
'vato'
GO

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'vato'

CREATE CERTIFICATE HumanResources037
WITH SUBJECT = 'Employee Social Security Numbers';
GO

CREATE SYMMETRIC KEY SSN_Key_01
WITH ALGORITHM = DES
ENCRYPTION BY CERTIFICATE HumanResources037;
GO

-- Create a column in which to store the encrypted data
ALTER TABLE HumanResources.Employee
ADD EncryptedNationalIDNumber varbinary(128);
GO

-- Open the symmetric key with which to encrypt the data
OPEN SYMMETRIC KEY SSN_Key_01
DECRYPTION BY CERTIFICATE HumanResources037;

-- Encrypt the value in column NationalIDNumber with symmetric
-- key SSN_Key_01. Save the result in column EncryptedNationalIDNumber.
UPDATE HumanResources.Employee
SET EncryptedNationalIDNumber = EncryptByKey(Key_GUID('SSN_Key_01'),
NationalIDNumber);
GO

-- Verify the encryption.
-- First, open the symmetric key with which to decrypt the data
OPEN SYMMETRIC KEY SSN_Key_01
DECRYPTION BY CERTIFICATE HumanResources037;
GO

-- Now list the original ID, the encrypted ID, and the
-- decrypted ciphertext. If the decryption worked, the original
-- and the decrypted ID will match.

create procedure getDecryptedIDNumber
with exec as owner
as
SELECT NationalIDNumber, EncryptedNationalIDNumber
AS "Encrypted ID Number",
CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber))
AS "Decrypted ID Number"
FROM HumanResources.Employee;
GO

/*works for me, shows the decrypted data*/

exec getDecryptedIDNumber

USE [master]
GO

CREATE LOGIN [test] WITH PASSWORD=N'test',
DEFAULT_DATABASE=[AdventureWorks], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

USE [AdventureWorks]
GO

CREATE USER [test] FOR LOGIN [test]
GO

use [AdventureWorks]
GO

GRANT EXECUTE ON [dbo].[getDecryptedIDNumber] TO [test]
GO

GRANT IMPERSONATE ON USER:: dbo TO test;
GO

/*Now, open up a "file/new/DB Engine Query" and login with the test login*/
exec as user = 'dbo'
exec getDecryptedIDNumber

/*This returns NULL values where it should show the decrypted data*/

Decode in SQL Server

Hi

I have oracle statement and I want to translate it in SQL Server:

select DECODE(count(bid_Vendor), 1, NULL, COUNT(BID_VENDOR))
from bid_total

I tried it this way:
Select case(count(bid_vendor) when 1 then null else count(bid_vendor)end) as cs from bid_total

It gave me error i.e.

Incorrect syntax near the keyword 'when'Select CASE count(bid_vendor)
WHEN 1 then null
ELSE count(bid_vendor)
END as cs
FROM bid_total-PatP

Dear group,

Dear group,
I would like to ask a brief question about NULL values and the IN operator.
The following SQL evaluates to (with ANSI_NULLS ON):
1 IN (1, 2, 3, NULL) --> Evaluates to true (makes perfect sense).
NULL IN (1, 2, 3) --> Evaluates to unknown (makes perfect sense).
4 IN (1, 2, 3) --> Evaluates to false (makes perfect sense).
ButK
4 IN (1, 2, 3, NULL) --> Evaluates to unknown and not to false.
I read that most major databases do this exactly the same and I actually
was able to find something about this behavior in the PostGreSQL
documentation (I couldnt find anything about this in the BOL, so I hoped
that the PostGreSQL might apply)
The docs state that for the IN operator:
If there are no equal right-hand values and at least one right-hand row
yields null, the result of the IN construct will be null, not false. This
is in accordance with SQL's normal rules for Boolean combinations of null
values.
So, why does SQL compare to a NULL value (if present) when no matching
values can be found for the right hand of the IN construct?
Kind regards,
Marcel
> 4 IN (1, 2, 3, NULL) --> Evaluates to unknown and not to false.
In this expression, we know for sure that 4 is not equal to 1, 2 or 3.
However, we can't say with certainty whether or not 4 is equal to or is not
equal to the unknown NULL value. The expression would be true if the
unknown value were 4 and would be false if the unknown value were 5.
Without certainty of the unknown value, standard SQL rules implemented by
various DBMS products return NULL rather than true or false.
Hope this helps.
Dan Guzman
SQL Server MVP
"Marcel van den Hof" <marcelvdh@.gmail.com> wrote in message
news:jetivog7pu0t$.jqeavwovs3nw.dlg@.40tude.net...
> Dear group,
> I would like to ask a brief question about NULL values and the IN
> operator.
> The following SQL evaluates to (with ANSI_NULLS ON):
> 1 IN (1, 2, 3, NULL) --> Evaluates to true (makes perfect sense).
> NULL IN (1, 2, 3) --> Evaluates to unknown (makes perfect sense).
> 4 IN (1, 2, 3) --> Evaluates to false (makes perfect sense).
> ButK
> 4 IN (1, 2, 3, NULL) --> Evaluates to unknown and not to false.
> I read that most major databases do this exactly the same and I actually
> was able to find something about this behavior in the PostGreSQL
> documentation (I couldnt find anything about this in the BOL, so I hoped
> that the PostGreSQL might apply)
> The docs state that for the IN operator:
> If there are no equal right-hand values and at least one right-hand row
> yields null, the result of the IN construct will be null, not false. This
> is in accordance with SQL's normal rules for Boolean combinations of null
> values.
> So, why does SQL compare to a NULL value (if present) when no matching
> values can be found for the right hand of the IN construct?
> Kind regards,
> Marcel
|||On Sat, 6 Aug 2005 19:22:01 +0100, Marcel van den Hof wrote:
(snip)
>The docs state that for the IN operator:
>If there are no equal right-hand values and at least one right-hand row
>yields null, the result of the IN construct will be null, not false. This
>is in accordance with SQL's normal rules for Boolean combinations of null
>values.
Hi Marcel,
This is from the PostGreSQL docs you mentioned, I presume?
This behaviour is in compliance with the ANSI standard. Dan has already
explained the rationale. There is only one minor mistake in the
PostGreSQL doc - the result of the IN construct with a NULL at the
right-hand side is not NULL, but UNKNOWN.
This distinction IS relevant. Null means "no valid data". Unknown is
valid data in three-valued logic.
Of course, the PostGreSQL doc is a thousand times better than the SQL
Server Books Online. BOL states:
"If the value of test_expression is equal to any value returned by
subquery or is equal to any expression from the comma-separated list,
the result value is TRUE. Otherwise, the result value is FALSE.
Using NOT IN negates the returned value."
And that is not a minor mistake - it is just plain wrong.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||> 4 IN (1, 2, 3, NULL) --> Evaluates to unknown and not to false.
Because we don't know the value represented by the NULL and therefore we
don't know whether 4 is in the list or not.
This is reasonably intuitive but ultimately you will search in vain for
satisfactory logic in SQL's handling of NULLs and three-value logic.
Consider the boolean expression:
(x=x) AND (y=y)
where x is NULL is y is non-NULL. The expected result is UNKNOWN, not TRUE.
This defies rational explanation. If the value of x is unknown then the one
thing we DO know for sure about x is that it is equal to itself! On the
other hand if the value x is deemed "inapplicable" then the comparison (x=x)
is surely a no-op and the rest of the expression should be evaluated without
it:
(y=y) = TRUE ... (but not in SQL).
Sorry, but the correct answer to your question is "because the SQL Standard
says so". :-)
David Portas
SQL Server MVP
|||Dan, Hugo and David thank you for your very clear and concise answers. You
have really helped me to improve my understanding of the three valued logic
and NULL values that are used in SQL server. A pity the BOL documentation
is somewhat inaccurate about these important matters.
If I want to further my understanding about these matters then I suppose
the best place for me is to study the ANSI SQL 92/ 99 standard?
Any links or pointers to relevant documentation (that is accurate ;-)) are
greatly appreciated.
Once again, thanks for the prompt reply to my question.
Kind regards,
Marcel van den Hof
|||Marcel van den Hof (marcelvdh@.gmail.com) writes:
> Dan, Hugo and David thank you for your very clear and concise answers.
> You have really helped me to improve my understanding of the three
> valued logic and NULL values that are used in SQL server. A pity the BOL
> documentation is somewhat inaccurate about these important matters.
I checked the SQL 2005 docs, and they are equally wrong. I submitted
a bug for this, although I believe it's too late for it to be fixed
for SQL 2005 RTM.
The bug is on
http://lab.msdn.microsoft.com/Produc...ckId=FDBK34083
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
|||On Sun, 7 Aug 2005 01:18:09 +0100, Marcel van den Hof wrote:
(snip)
>If I want to further my understanding about these matters then I suppose
>the best place for me is to study the ANSI SQL 92/ 99 standard?
Hi Marcel,
Not exactly. Studying the ANSI documentation is not a job for the faint
of heart. Seriously - they are written to define a standard, in as
concise a way as possible. They are not written to facilitate easy
understanding.

>Any links or pointers to relevant documentation (that is accurate ;-)) are
>greatly appreciated.
Most books are fairly accurate. Just keep in mind that all authors are
human, and humans can err. Also keep in mind that the more entry-level
books have to simplify things; books aimed at expert level will usually
present more of the fine details.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Dear group,

Dear group,
I would like to ask a brief question about NULL values and the IN operator.
The following SQL evaluates to (with ANSI_NULLS ON):
1 IN (1, 2, 3, NULL) --> Evaluates to true (makes perfect sense).
NULL IN (1, 2, 3) --> Evaluates to unknown (makes perfect sense).
4 IN (1, 2, 3) --> Evaluates to false (makes perfect sense).
ButK
4 IN (1, 2, 3, NULL) --> Evaluates to unknown and not to false.
I read that most major databases do this exactly the same and I actually
was able to find something about this behavior in the PostGreSQL
documentation (I couldnt find anything about this in the BOL, so I hoped
that the PostgreSQL might apply)
The docs state that for the IN operator:
If there are no equal right-hand values and at least one right-hand row
yields null, the result of the IN construct will be null, not false. This
is in accordance with SQL's normal rules for Boolean combinations of null
values.
So, why does SQL compare to a NULL value (if present) when no matching
values can be found for the right hand of the IN construct?
Kind regards,
Marcel> 4 IN (1, 2, 3, NULL) --> Evaluates to unknown and not to false.
In this expression, we know for sure that 4 is not equal to 1, 2 or 3.
However, we can't say with certainty whether or not 4 is equal to or is not
equal to the unknown NULL value. The expression would be true if the
unknown value were 4 and would be false if the unknown value were 5.
Without certainty of the unknown value, standard SQL rules implemented by
various DBMS products return NULL rather than true or false.
Hope this helps.
Dan Guzman
SQL Server MVP
"Marcel van den Hof" <marcelvdh@.gmail.com> wrote in message
news:jetivog7pu0t$.jqeavwovs3nw.dlg@.40tude.net...
> Dear group,
> I would like to ask a brief question about NULL values and the IN
> operator.
> The following SQL evaluates to (with ANSI_NULLS ON):
> 1 IN (1, 2, 3, NULL) --> Evaluates to true (makes perfect sense).
> NULL IN (1, 2, 3) --> Evaluates to unknown (makes perfect sense).
> 4 IN (1, 2, 3) --> Evaluates to false (makes perfect sense).
> ButK
> 4 IN (1, 2, 3, NULL) --> Evaluates to unknown and not to false.
> I read that most major databases do this exactly the same and I actually
> was able to find something about this behavior in the PostGreSQL
> documentation (I couldnt find anything about this in the BOL, so I hoped
> that the PostgreSQL might apply)
> The docs state that for the IN operator:
> If there are no equal right-hand values and at least one right-hand row
> yields null, the result of the IN construct will be null, not false. This
> is in accordance with SQL's normal rules for Boolean combinations of null
> values.
> So, why does SQL compare to a NULL value (if present) when no matching
> values can be found for the right hand of the IN construct?
> Kind regards,
> Marcel|||On Sat, 6 Aug 2005 19:22:01 +0100, Marcel van den Hof wrote:
(snip)
>The docs state that for the IN operator:
>If there are no equal right-hand values and at least one right-hand row
>yields null, the result of the IN construct will be null, not false. This
>is in accordance with SQL's normal rules for Boolean combinations of null
>values.
Hi Marcel,
This is from the PostgreSQL docs you mentioned, I presume?
This behaviour is in compliance with the ANSI standard. Dan has already
explained the rationale. There is only one minor mistake in the
PostGreSQL doc - the result of the IN construct with a NULL at the
right-hand side is not NULL, but UNKNOWN.
This distinction IS relevant. Null means "no valid data". Unknown is
valid data in three-valued logic.
Of course, the PostgreSQL doc is a thousand times better than the SQL
Server Books Online. BOL states:
"If the value of test_expression is equal to any value returned by
subquery or is equal to any expression from the comma-separated list,
the result value is TRUE. Otherwise, the result value is FALSE.
Using NOT IN negates the returned value."
And that is not a minor mistake - it is just plain wrong.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||> 4 IN (1, 2, 3, NULL) --> Evaluates to unknown and not to false.
Because we don't know the value represented by the NULL and therefore we
don't know whether 4 is in the list or not.
This is reasonably intuitive but ultimately you will search in vain for
satisfactory logic in SQL's handling of NULLs and three-value logic.
Consider the boolean expression:
(x=x) AND (y=y)
where x is NULL is y is non-NULL. The expected result is UNKNOWN, not TRUE.
This defies rational explanation. If the value of x is unknown then the one
thing we DO know for sure about x is that it is equal to itself! On the
other hand if the value x is deemed "inapplicable" then the comparison (x=x)
is surely a no-op and the rest of the expression should be evaluated without
it:
(y=y) = TRUE ... (but not in SQL).
Sorry, but the correct answer to your question is "because the SQL Standard
says so". :-)
David Portas
SQL Server MVP
--|||Dan, Hugo and David thank you for your very clear and concise answers. You
have really helped me to improve my understanding of the three valued logic
and NULL values that are used in SQL server. A pity the BOL documentation
is somewhat inaccurate about these important matters.
If I want to further my understanding about these matters then I suppose
the best place for me is to study the ANSI SQL 92/ 99 standard?
Any links or pointers to relevant documentation (that is accurate ;-)) are
greatly appreciated.
Once again, thanks for the prompt reply to my question.
Kind regards,
Marcel van den Hof|||Marcel van den Hof (marcelvdh@.gmail.com) writes:
> Dan, Hugo and David thank you for your very clear and concise answers.
> You have really helped me to improve my understanding of the three
> valued logic and NULL values that are used in SQL server. A pity the BOL
> documentation is somewhat inaccurate about these important matters.
I checked the SQL 2005 docs, and they are equally wrong. I submitted
a bug for this, although I believe it's too late for it to be fixed
for SQL 2005 RTM.
The bug is on
http://lab.msdn.microsoft.com/Produ...BK340
83
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Sun, 7 Aug 2005 01:18:09 +0100, Marcel van den Hof wrote:
(snip)
>If I want to further my understanding about these matters then I suppose
>the best place for me is to study the ANSI SQL 92/ 99 standard?
Hi Marcel,
Not exactly. Studying the ANSI documentation is not a job for the faint
of heart. Seriously - they are written to define a standard, in as
concise a way as possible. They are not written to facilitate easy
understanding.

>Any links or pointers to relevant documentation (that is accurate ;-)) are
>greatly appreciated.
Most books are fairly accurate. Just keep in mind that all authors are
human, and humans can err. Also keep in mind that the more entry-level
books have to simplify things; books aimed at expert level will usually
present more of the fine details.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Dear group,

Dear group,
I would like to ask a brief question about NULL values and the IN operator.
The following SQL evaluates to (with ANSI_NULLS ON):
1 IN (1, 2, 3, NULL) --> Evaluates to true (makes perfect sense).
NULL IN (1, 2, 3) --> Evaluates to unknown (makes perfect sense).
4 IN (1, 2, 3) --> Evaluates to false (makes perfect sense).
But¡K
4 IN (1, 2, 3, NULL) --> Evaluates to unknown and not to false.
I read that most major databases do this exactly the same and I actually
was able to find something about this behavior in the PostGreSQL
documentation (I couldn¡¦t find anything about this in the BOL, so I hoped
that the PostGreSQL might apply)
The docs state that for the IN operator:
If there are no equal right-hand values and at least one right-hand row
yields null, the result of the IN construct will be null, not false. This
is in accordance with SQL's normal rules for Boolean combinations of null
values.
So, why does SQL compare to a NULL value (if present) when no matching
values can be found for the right hand of the IN construct?
Kind regards,
Marcel> 4 IN (1, 2, 3, NULL) --> Evaluates to unknown and not to false.
In this expression, we know for sure that 4 is not equal to 1, 2 or 3.
However, we can't say with certainty whether or not 4 is equal to or is not
equal to the unknown NULL value. The expression would be true if the
unknown value were 4 and would be false if the unknown value were 5.
Without certainty of the unknown value, standard SQL rules implemented by
various DBMS products return NULL rather than true or false.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Marcel van den Hof" <marcelvdh@.gmail.com> wrote in message
news:jetivog7pu0t$.jqeavwovs3nw.dlg@.40tude.net...
> Dear group,
> I would like to ask a brief question about NULL values and the IN
> operator.
> The following SQL evaluates to (with ANSI_NULLS ON):
> 1 IN (1, 2, 3, NULL) --> Evaluates to true (makes perfect sense).
> NULL IN (1, 2, 3) --> Evaluates to unknown (makes perfect sense).
> 4 IN (1, 2, 3) --> Evaluates to false (makes perfect sense).
> But¡K
> 4 IN (1, 2, 3, NULL) --> Evaluates to unknown and not to false.
> I read that most major databases do this exactly the same and I actually
> was able to find something about this behavior in the PostGreSQL
> documentation (I couldn¡¦t find anything about this in the BOL, so I hoped
> that the PostGreSQL might apply)
> The docs state that for the IN operator:
> If there are no equal right-hand values and at least one right-hand row
> yields null, the result of the IN construct will be null, not false. This
> is in accordance with SQL's normal rules for Boolean combinations of null
> values.
> So, why does SQL compare to a NULL value (if present) when no matching
> values can be found for the right hand of the IN construct?
> Kind regards,
> Marcel|||On Sat, 6 Aug 2005 19:22:01 +0100, Marcel van den Hof wrote:
(snip)
>The docs state that for the IN operator:
>If there are no equal right-hand values and at least one right-hand row
>yields null, the result of the IN construct will be null, not false. This
>is in accordance with SQL's normal rules for Boolean combinations of null
>values.
Hi Marcel,
This is from the PostGreSQL docs you mentioned, I presume?
This behaviour is in compliance with the ANSI standard. Dan has already
explained the rationale. There is only one minor mistake in the
PostGreSQL doc - the result of the IN construct with a NULL at the
right-hand side is not NULL, but UNKNOWN.
This distinction IS relevant. Null means "no valid data". Unknown is
valid data in three-valued logic.
Of course, the PostGreSQL doc is a thousand times better than the SQL
Server Books Online. BOL states:
"If the value of test_expression is equal to any value returned by
subquery or is equal to any expression from the comma-separated list,
the result value is TRUE. Otherwise, the result value is FALSE.
Using NOT IN negates the returned value."
And that is not a minor mistake - it is just plain wrong.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||> 4 IN (1, 2, 3, NULL) --> Evaluates to unknown and not to false.
Because we don't know the value represented by the NULL and therefore we
don't know whether 4 is in the list or not.
This is reasonably intuitive but ultimately you will search in vain for
satisfactory logic in SQL's handling of NULLs and three-value logic.
Consider the boolean expression:
(x=x) AND (y=y)
where x is NULL is y is non-NULL. The expected result is UNKNOWN, not TRUE.
This defies rational explanation. If the value of x is unknown then the one
thing we DO know for sure about x is that it is equal to itself! On the
other hand if the value x is deemed "inapplicable" then the comparison (x=x)
is surely a no-op and the rest of the expression should be evaluated without
it:
(y=y) = TRUE ... (but not in SQL).
Sorry, but the correct answer to your question is "because the SQL Standard
says so". :-)
--
David Portas
SQL Server MVP
--|||Dan, Hugo and David thank you for your very clear and concise answers. You
have really helped me to improve my understanding of the three valued logic
and NULL values that are used in SQL server. A pity the BOL documentation
is somewhat inaccurate about these important matters.
If I want to further my understanding about these matters then I suppose
the best place for me is to study the ANSI SQL 92/ 99 standard?
Any links or pointers to relevant documentation (that is accurate ;-)) are
greatly appreciated.
Once again, thanks for the prompt reply to my question.
Kind regards,
Marcel van den Hof|||Marcel van den Hof (marcelvdh@.gmail.com) writes:
> Dan, Hugo and David thank you for your very clear and concise answers.
> You have really helped me to improve my understanding of the three
> valued logic and NULL values that are used in SQL server. A pity the BOL
> documentation is somewhat inaccurate about these important matters.
I checked the SQL 2005 docs, and they are equally wrong. I submitted
a bug for this, although I believe it's too late for it to be fixed
for SQL 2005 RTM.
The bug is on
http://lab.msdn.microsoft.com/ProductFeedback/viewFeedback.aspx?feedbackId=FDBK34083
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||On Sun, 7 Aug 2005 01:18:09 +0100, Marcel van den Hof wrote:
(snip)
>If I want to further my understanding about these matters then I suppose
>the best place for me is to study the ANSI SQL 92/ 99 standard?
Hi Marcel,
Not exactly. Studying the ANSI documentation is not a job for the faint
of heart. Seriously - they are written to define a standard, in as
concise a way as possible. They are not written to facilitate easy
understanding.
>Any links or pointers to relevant documentation (that is accurate ;-)) are
>greatly appreciated.
Most books are fairly accurate. Just keep in mind that all authors are
human, and humans can err. Also keep in mind that the more entry-level
books have to simplify things; books aimed at expert level will usually
present more of the fine details.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)