June 16, 2009 at 6:56 am
Hi friends,
I understood that if the column is defined as UNIQUE , it will contains the values such that each one is different from other.
Also the column defined as UNIQUE can take NULL value (one of the difference from PRIMARY KEY) , and I have seen that it can take ONE AND ONLY ONE NULL value...
But my doubt is if we set SET ANSI_NULLS ON , it mean NULL != NULL , in that case why the column (which is defined as UNIQUE) cant take more than one NULL value... ?
It will be a great help if any one can clear my doubt/ correct my understanding if it is wrong
Thanks in advance...
Regards,
MC
Thanks & Regards,
MC
June 16, 2009 at 8:36 pm
It's simply because that setting only affects comparisons... not the way a UNIQUE index works.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 17, 2009 at 7:01 am
K, thanks
I have one more doubt...
Here is the table named test with coulmns ID and Name
create table test
(id int identity(1,1) primary key ,
name varchar(30) unique
)
when i insert records
insert into test (name) values('abc')
insert into test (name) values('xyz')
insert into test (name) values(null)
select * from test
Im getting the result as
diname
3NULL
1abc
2xyz
since by default the unique identifire is non-clustured , the records should be in the same order I have inserted na..?
Thanks & Regards,
MC
June 17, 2009 at 7:14 am
You can handle this with CHECK constraint and a user defined function. Create a function which determines if a specified name is NULL or does not exist in the table and return an INT. Then create a CHECK constraint on your Name column which calls the function and handles the return value.
-- Test table
CREATE TABLE Test
(
Id INT IDENTITY
PRIMARY KEY CLUSTERED,
Name VARCHAR(35) NULL
)
CREATE INDEX IX_Test_Name ON Test (Name)
GO
-- Function to validate the name
CREATE FUNCTION dbo.ufn_ck_TestName_Unique
(
@Id INT,
@Name VARCHAR(30)
)
RETURNS INT
AS
BEGIN
IF (@Name IS NULL)
RETURN 1
IF EXISTS (SELECT TOP(1) 1 FROM Test WHERE Id != @Id AND Name = @Name)
RETURN 0
RETURN 1
END
GO
-- Create a CHECK constraint
ALTER TABLE Test
ADD CONSTRAINT
CK_Test_Name_Unique
CHECK (dbo.ufn_ck_TestName_Unique(Id, Name) = 1)
GO
-- Try to insert some data
INSERT INTO Test SELECT 'Hello'
INSERT INTO Test SELECT NULL
INSERT INTO Test SELECT 'Hello'
INSERT INTO Test SELECT NULL
SELECT * FROM Test
GO
-- Clean up
ALTER TABLE Test DROP CONSTRAINT CK_Test_Name_Unique
DROP FUNCTION dbo.ufn_ck_TestName_Unique
DROP TABLE Test
Flo
June 17, 2009 at 8:57 am
I believe a function as a check constraint is going to slow down INSERTs quite a bit. Wouldn't it be better to setup a little DRI instead?
--Jeff Moden
Change is inevitable... Change for the better is not.
June 17, 2009 at 9:08 am
Hi Jeff!
My solution definitely may become a performance issue with huge load. I just have no idea how you would handle this with DRI? (Maybe depends on the fact that I had tea this morning instead of coffee...) Do you have a little sample or a reference?
Flo
June 17, 2009 at 9:17 am
The Records are inserted in the same order.
AS you can see by the ID values assigned.
I think you mean that rows are returned in a order that you are not expecting.
The query plan that I get shows that the IX index is being used and not the clustered index which means there is no guarantee of sort order.
If you force the query to use the clustered index you will see they come in order 1,2,3 (I'm not saying this is what you should do but it illustrates the point)
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
June 17, 2009 at 9:40 am
Flo, if you are trying to allow more than a single null, but everything else is unique there are two methods that I would recommend:
1) Use a nullbuster column (coined and attributed to Steve Kass)
CREATE TABLE dupNulls (
pk int identity(1,1) primary key,
X int NULL,
nullbuster as (case when X is null then pk else 0 end),
CONSTRAINT dupNulls_uqX UNIQUE (X,nullbuster)
)
INSERT INTO dupNulls(X) VALUES (1)
INSERT INTO dupNulls(X) VALUES (NULL)
INSERT INTO dupNulls(X) VALUES (NULL)
GO
SELECT pk, X, nullbuster FROM dupNulls
UPDATE dupNulls SET X = 1 WHERE pk = 2
GO
SELECT pk, X, nullbuster FROM dupNulls
UPDATE dupNulls SET X = 2 WHERE pk = 2
SELECT pk, X, nullbuster FROM dupNulls
DROP TABLE dupNulls
2) Use an indexed view and exclude rows where that column is null. Create a unique index on the indexed view for that column.
Either of the above are going to perform a lot better than using a function.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 17, 2009 at 9:41 am
only4mithunc (6/17/2009)
since by default the unique identifire is non-clustured , the records should be in the same order I have inserted na..?
By definition, a table has no order. The only way to guarantee the order data is returned is to provide an ORDER BY on the query. Without an ORDER BY - SQL Server can return the results in any order.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 17, 2009 at 9:48 am
Heh... Congrats Jeffrey; a new bookmark in browser for this topic 🙂
Using an indexed view is a solution I really had to see. Let me justify with the lack of coffee...
But the "nullbuster" is definitely a completely new word/way I've never heard/seen.
Thanks a lot!
Flo
June 17, 2009 at 9:53 am
hi friends, thanks for this bunch of reply.... 🙂
but the solution is like keeping unique value in the column and allowing more than one NULL value....
but my doubt was....
when we set SET ANSI_NULLS ON , then we can see that NULL != NULL (by this I think one NULL is different from another NULL ), so in that case why the unique identifier column is still not allowing more than one NULL value... ?
(Im not asking is there any way to allow more than one NULL and keep other value as unique...
🙂 )
Thanks,
MC
Thanks & Regards,
MC
June 17, 2009 at 10:07 am
only4mithunc (6/17/2009)
hi friends, thanks for this bunch of reply.... 🙂but the solution is like keeping unique value in the column and allowing more than one NULL value....
but my doubt was....
when we set SET ANSI_NULLS ON , then we can see that NULL != NULL (by this I think one NULL is different from another NULL ), so in that case why the unique identifier column is still not allowing more than one NULL value... ?
(Im not asking is there any way to allow more than one NULL and keep other value as unique...
🙂 )
Thanks,
MC
Sorry - we went off on a little tangent. Your question was already answered - but, here it is again:
It's simply because that setting only affects comparisons... not the way a UNIQUE index works.
--Jeff Moden
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 17, 2009 at 10:17 am
Florian Reischl (6/17/2009)
Heh... Congrats Jeffrey; a new bookmark in browser for this topic 🙂Using an indexed view is a solution I really had to see. Let me justify with the lack of coffee...
But the "nullbuster" is definitely a completely new word/way I've never heard/seen.
Thanks a lot!
Flo
You can thank Steve Kass for that term - I just copied his code and remembered that it exists. :w00t:
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 17, 2009 at 10:18 am
Ok.Ok.. than you thanks a lot....
Regards,
MC
Thanks & Regards,
MC
June 17, 2009 at 10:34 am
@Jeffrey:
Erm... Just played with this nullbuster. This might be an important information for you:
This feature works find on SSE2k5 but it does not work anymore on SSE2k8 for bulk inserts like:
INSERT INTO dupNulls (X)
SELECT 1
UNION ALL SELECT NULL
UNION ALL SELECT NULL
Flo
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply