NULL and Unique IDentifier.. :-(

Question

NULL and Unique IDentifier.. :-(

Viewing 7 posts - 16 through 21 (of 21 total)

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

Jeffrey Williams SSC Guru Points: 89773 More actions · Answer 1

Florian Reischl (6/17/2009)
@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

Hmm - works fine for me. Are you sure it isn't failing because you already have a row with a 1 in the X column?

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

Florian Reischl SSC-Dedicated Points: 37299 More actions · Answer 2

Jeffrey Williams (6/17/2009)
Hmm - works fine for me. Are you sure it isn't failing because you already have a row with a 1 in the X column?

Well, I'm out now for coffee!!!

Sorry!

The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems

Jeffrey Williams SSC Guru Points: 89773 More actions · Answer 3

Florian Reischl (6/17/2009)
Jeffrey Williams (6/17/2009)
Hmm - works fine for me. Are you sure it isn't failing because you already have a row with a 1 in the X column?
Well, I'm out now for coffee!!!
Sorry!

Well - there you go. Already on my fourth cup :w00t:

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

Jeffrey Williams (6/17/2009)
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.

Should that be by definition, a table is in clustered index order but a SELECT is not guaranteed to return data in that order without an ORDER BY? 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 5

Florian Reischl (6/17/2009)
@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:
Flo

Even if that was the case, on 2008 there's a neater solution to unique indexes with multiple nulls.

CREATE TABLE dupNulls (

pk int identity(1,1) primary key,

X int NULL,

)

CREATE UNIQUE INDEX idx_UniqueX

ON dupNulls (X)

WHERE X IS NOT NULL

GO

INSERT INTO dupNulls(X) VALUES (1)

INSERT INTO dupNulls(X) VALUES (NULL)

GO

SELECT pk, X FROM dupNulls

UPDATE dupNulls SET X = 1 WHERE pk = 2

GO

SELECT pk, X FROM dupNulls

UPDATE dupNulls SET X = 2 WHERE pk = 2

SELECT pk, X FROM dupNulls

Not allowed for unique constraints, just unique indexes.

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

Jeffrey Williams SSC Guru Points: 89773 More actions · Answer 6

GilaMonster (6/17/2009)
Florian Reischl (6/17/2009)
@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:
Flo
Even if that was the case, on 2008 there's a neater solution to unique indexes with multiple nulls.
CREATE TABLE dupNulls (
pk int identity(1,1) primary key,
X int NULL,
)
CREATE UNIQUE INDEX idx_UniqueX
ON dupNulls (X)
WHERE X IS NOT NULL
GO
INSERT INTO dupNulls(X) VALUES (1)
INSERT INTO dupNulls(X) VALUES (NULL)
INSERT INTO dupNulls(X) VALUES (NULL)
GO
SELECT pk, X FROM dupNulls
UPDATE dupNulls SET X = 1 WHERE pk = 2
GO
SELECT pk, X FROM dupNulls
UPDATE dupNulls SET X = 2 WHERE pk = 2
SELECT pk, X FROM dupNulls
Not allowed for unique constraints, just unique indexes.

Oh, totally forgot about filtered indexes in 2008 - thanks.

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

RBarryYoung SSC Guru Points: 143329 More actions · Answer 7

GilaMonster (6/17/2009)
Florian Reischl (6/17/2009)
@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:
Flo
Even if that was the case, on 2008 there's a neater solution to unique indexes with multiple nulls.
CREATE TABLE dupNulls (
pk int identity(1,1) primary key,
X int NULL,
)
CREATE UNIQUE INDEX idx_UniqueX
ON dupNulls (X)
WHERE X IS NOT NULL
GO
INSERT INTO dupNulls(X) VALUES (1)
INSERT INTO dupNulls(X) VALUES (NULL)
INSERT INTO dupNulls(X) VALUES (NULL)
GO
SELECT pk, X FROM dupNulls
UPDATE dupNulls SET X = 1 WHERE pk = 2
GO
SELECT pk, X FROM dupNulls
UPDATE dupNulls SET X = 2 WHERE pk = 2
SELECT pk, X FROM dupNulls
Not allowed for unique constraints, just unique indexes.

Heh. As soon as I started reading this thread, I thouhg "filtered indexes in 2008! Now if only Gail hasn't seen this thread yet ..." Rats, I've got to quit work earlier! 🙂

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]