q; unique which allows multiple nulls

Hi,
I have a non-cluster index on the filed, I need to make it non-cluster index
and unique which allows multiple nulls, how should I do this with a sql
statement?Check constraint? Trigger?
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:1D7BAC44-AFE0-409D-8446-57179013153E@.microsoft.com...
> Hi,
> I have a non-cluster index on the filed, I need to make it non-cluster
> index
> and unique which allows multiple nulls, how should I do this with a sql
> statement?
>
>|||How should I write that check contraint? Any example?
"Mike C#" wrote:
> Check constraint? Trigger?
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:1D7BAC44-AFE0-409D-8446-57179013153E@.microsoft.com...
> > Hi,
> >
> > I have a non-cluster index on the filed, I need to make it non-cluster
> > index
> > and unique which allows multiple nulls, how should I do this with a sql
> > statement?
> >
> >
> >
>
>|||Hi Jim
What is your goal? If it is primarily to enforce uniqueness of all non-NULL
values you can create a view that excludes the NULLs, and then build a
unique index on the view.
CREATE TABLE T1
(
key_col INT NULL,
data_col CHAR(1) NOT NULL
)
GO
CREATE VIEW V1 WITH SCHEMABINDING
AS
SELECT key_col FROM dbo.T1 WHERE key_col IS NOT NULL
GO
CREATE UNIQUE CLUSTERED INDEX idx_uc_key_col ON V1(key_col)
INSERT INTO T1 VALUES(1, 'a')
INSERT INTO T1 VALUES(1, 'b')
INSERT INTO T1 VALUES(2, 'a')
INSERT INTO T1 VALUES(NULL, 'a')
INSERT INTO T1 VALUES(NULL, 'a')
INSERT INTO T1 VALUES(NULL, 'b')
SELECT * FROM T1
GO
--
HTH
Kalen Delaney, SQL Server MVP
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:756F641A-0B2B-4AB6-8BDE-CB9F38372886@.microsoft.com...
> How should I write that check contraint? Any example?
> "Mike C#" wrote:
>> Check constraint? Trigger?
>> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
>> news:1D7BAC44-AFE0-409D-8446-57179013153E@.microsoft.com...
>> > Hi,
>> >
>> > I have a non-cluster index on the filed, I need to make it non-cluster
>> > index
>> > and unique which allows multiple nulls, how should I do this with a sql
>> > statement?
>> >
>> >
>> >
>>|||A UNIQUE index (CONSTRAINT) will only allow 1 NULL value per field
combination included in the index.
One field index = one NULL value, two field index = 3 rows with NULL index
values, etc.
--
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:1D7BAC44-AFE0-409D-8446-57179013153E@.microsoft.com...
> Hi,
> I have a non-cluster index on the filed, I need to make it non-cluster
> index
> and unique which allows multiple nulls, how should I do this with a sql
> statement?
>
>|||>> two field index = 3 rows with NULL index <<
Maybe I'm not understanding what you're saying here, but by my understanding
of your statement, this is not true at all.
For a two-column unique index, there can be only one row where both columns
are null, but there is no limit to the number of nulls in either one of the
columns.
The following code will only give you one error, and all the other rows will
be inserted just fine:
create table two_column_index (col1 int null, col2 int null, unique (col1,
col2))
go
insert into two_column_index select null, null
insert into two_column_index select null, null -- error
insert into two_column_index select 1, null
insert into two_column_index select 2, null
insert into two_column_index select 3, null
insert into two_column_index select 4, null
insert into two_column_index select null, 1
insert into two_column_index select null, 2
insert into two_column_index select null, 3
insert into two_column_index select null, 4
go
select * from two_column_index
--
HTH
Kalen Delaney, SQL Server MVP
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:OqC977TqGHA.928@.TK2MSFTNGP04.phx.gbl...
>A UNIQUE index (CONSTRAINT) will only allow 1 NULL value per field
>combination included in the index.
> One field index = one NULL value, two field index = 3 rows with NULL index
> values, etc.
> --
> Arnie Rowland
> "To be successful, your heart must accompany your knowledge."
>
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:1D7BAC44-AFE0-409D-8446-57179013153E@.microsoft.com...
>> Hi,
>> I have a non-cluster index on the filed, I need to make it non-cluster
>> index
>> and unique which allows multiple nulls, how should I do this with a sql
>> statement?
>>
>|||You're right Kalen.
I sat down after coming in from a wonderful hike in the Cascades, and my
brain was perhaps a bit fried. Mr. Henry (a representative of the Full Sail
Ale Company) and I did not explain ourselves well.
What I meant to convey is that given the same set of values (i.e., NULL and
[1], NULL and [2], etc.) that there are 3 possible combinations that would
be allowed.
I see how my comment indicated something quite untoward. I did NOT intend to
convey that only 3 rows would be allowed to contain NULL values in the
entire table -that would be quite unfortunate if it were the reality.
Thanks for calling it to attention lest anyone become confused from my
comment.
--
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:e1TEB9UqGHA.4208@.TK2MSFTNGP04.phx.gbl...
>> two field index = 3 rows with NULL index <<
> Maybe I'm not understanding what you're saying here, but by my
> understanding of your statement, this is not true at all.
> For a two-column unique index, there can be only one row where both
> columns are null, but there is no limit to the number of nulls in either
> one of the columns.
> The following code will only give you one error, and all the other rows
> will be inserted just fine:
> create table two_column_index (col1 int null, col2 int null, unique (col1,
> col2))
> go
> insert into two_column_index select null, null
> insert into two_column_index select null, null -- error
> insert into two_column_index select 1, null
> insert into two_column_index select 2, null
> insert into two_column_index select 3, null
> insert into two_column_index select 4, null
> insert into two_column_index select null, 1
> insert into two_column_index select null, 2
> insert into two_column_index select null, 3
> insert into two_column_index select null, 4
> go
> select * from two_column_index
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:OqC977TqGHA.928@.TK2MSFTNGP04.phx.gbl...
>>A UNIQUE index (CONSTRAINT) will only allow 1 NULL value per field
>>combination included in the index.
>> One field index = one NULL value, two field index = 3 rows with NULL
>> index values, etc.
>> --
>> Arnie Rowland
>> "To be successful, your heart must accompany your knowledge."
>>
>> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
>> news:1D7BAC44-AFE0-409D-8446-57179013153E@.microsoft.com...
>> Hi,
>> I have a non-cluster index on the filed, I need to make it non-cluster
>> index
>> and unique which allows multiple nulls, how should I do this with a sql
>> statement?
>>
>>
>