Unique key concept:Shubham Saxena@Bajaj Capital

Question

Unique key concept:Shubham Saxena@Bajaj Capital

shubham.saxena67

Default port

Points: 1490
More actions
February 1, 2012 at 8:36 am

#250111

Dear Friends,
help me find out null comparision logic
for example:
select case when null=null then 1 else 0 end
returns:0 ->this means null is not equal to null ok
now we switch to another concept that prove null is equal to null
create table t (id int unique )
insert into t values(null)
1 row affacted
again
insert into t values(null)
returns
Msg 2627, Level 14, State 1, Line 1
Violation of UNIQUE KEY constraint 'UQ__t__4830B400'. Cannot insert duplicate key in object 'dbo.t'.
The statement has been terminated.
this means null compares to values in table t for unique key that shows null is equal to null that is already present in
given table..
i am very confuse is null equal to null or not
i have test it in oracle ..
that allows null duplicacy in a table having unique key
both are dbms but why they works diferently what is the key concept behind this...
plz plz tell me the answer..
thanks ..

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Null is not equal to null, however a unique constraint only allows one null. It simply means that unique constraints only allow a single null, it does not mean that nulls equal each other (they don't)

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 2

Under the default settings, each time that you do any compression with null, the results will false. You can checkout this code:

if 5 = null

select 'true'

else

select 'fales'

if 5 <> null

select 'true'

else

select 'false'

if 5 > null

select 'true'

else

select 'false'

if 5 < null

select 'true'

else

select 'false'

if null = null

select 'true'

else

select 'false'

You can change this behavior by modifying the setting options (I don't recommend of doing so. I'm just showing it so you'll know). For example:

set ansi_nulls off

go

if 5 = null

select 'true'

else

select 'fales'

if 5 <> null

select 'true'

else

select 'false'

if 5 > null

select 'true'

else

select 'false'

if 5 < null

select 'true'

else

select 'false'

if null = null

select 'true'

else

select 'false'

There are few cases that are not fallowing this rule. When a column has to be unique, you can insert only one null regardless of the set options that are being used. When ever you sort by a column that has null, all the nulls will apear together at the beginning or end of the records (depending on the sorting desc or asc). Also when you use aggregations all the nulls are treated as one value.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

jcb SSCertifiable Points: 6574 More actions · Answer 3

A common misconception about null lies in most people thinks: null = nothing or even null = 0.

Null sounds more "I dont know".

"null = null" sounds like "what's in my left hand? = what's in my right hand?" (if I not showing you my hands)

Well in boolean logic there's no room for "maybe" so "null = null" returns false.

When you try to insert two nulls in a unique key, well no one can guarantee null <> null or even null = null.

Try this:

select case when null=null then 1 else 0 end

select case when not(null=null) then 1 else 0 end

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

CELKO (2/1/2012)
In the current ANSI/ISO standards have the IS [NOT] DISTINCT FROM theta operator, but only DB2 might have it -- I am not sure. We can use
COALESCE <expr1>, '?????') = COALESCE <expr2>, '?????')
But it is better not to have code like this.

It might be worth noting that while SQL Server doesn't have the operator, it does refer to that DISTINCT concept in a few places, specifically when grouping. To paraphrase: if NULL were not distinct from all values, it wouldn't be a separate entry when grouping (i.e. when grouping by values in a column allowing NULL). The BOL entry about this specifically points out that within that context NULL is not distinct from NULL, but is distinct from any actual value.

----------------------------------------------------------------------------------
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?

Paul White SSC Guru Points: 150467 More actions · Answer 5

CELKO (2/1/2012)
In the current ANSI/ISO standards have the IS [NOT] DISTINCT FROM theta operator, but only DB2 might have it -- I am not sure.

Anyone wanting to vote for SQL Server to implement this can do so here:

https://connect.microsoft.com/SQLServer/feedback/details/286422/add-language-and-optimizer-support-for-iso-distinct-predicate or https://connect.microsoft.com/SQLServer/feedback/details/307476/operator-for-nullable-column-comparison

We can use COALESCE <expr1>, '?????') = COALESCE <expr2>, '?????')
But it is better not to have code like this.

I much prefer:

...EXISTS (SELECT expr1 INTERSECT SELECT expr2)

See http://sqlblog.com/blogs/paul_white/archive/2011/06/22/undocumented-query-plans-equality-comparisons.aspx

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi