SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 The Yak Corral
 Just out of curiosity
 New Topic  Reply to Topic
 Printer Friendly
Previous Page
Author Previous Topic Topic Next Topic
Page: of 2

TG
Flowing Fount of Yak Knowledge

USA
5932 Posts

Posted - 02/06/2008 :  13:24:22  Show Profile  Reply with Quote
quote:
The mystery is why I don't get 3 1 back too...

It is because you have 2 independant constraints acting on the same row so the ORDER becomes important

Look at one possible output of the SELECT (without the insert)
Now go through each row and manually delete any ROW where EITHER column constraint is violated:

1           2      --first row ok
1           3      --DELETED because of column1
2           1      --ok
2           3      --DELETED because of column1
3           2      --DELETED because of column2
3           1      --DELETED because of column2



EDIT:

Sorry, Nathan. I didn't notice you said pretty much the same thing.


Be One with the Optimizer
TG

Edited by - TG on 02/06/2008 13:41:31
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 02/06/2008 :  15:51:51  Show Profile  Visit SwePeso's Homepage  Reply with Quote
I can realize that. But it does still not explain why I often only get
SendID	RecvID
1	2
2	3
back, where I should have get
SendID	RecvID
1	2
2	3
3	1
but I don't. This is the mystery.

Can the reason be that a
2	1
tried to be inserted before and was rejected because of the "2" value? And then somehow the corresponding "1" value is cached to unique index and that's why
3	1
is not inserted?


E 12°55'05.25"
N 56°04'39.16"

Edited by - SwePeso on 02/06/2008 15:55:05
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 02/06/2008 :  16:14:49  Show Profile  Visit SwePeso's Homepage  Reply with Quote
It seems SQL Server (at least 2005) do not even bother about ORDER BY.
With this setup, I always get
2	1
1	2
I have run over 200 times now with exakt same result.
CREATE TABLE	#Sample
		(
			A TINYINT,
			B TINYINT
		)

CREATE UNIQUE NONCLUSTERED INDEX IX_SendID ON #Sample (A) WITH (IGNORE_DUP_KEY = ON)
CREATE UNIQUE NONCLUSTERED INDEX IX_RecvID ON #Sample (B) WITH (IGNORE_DUP_KEY = ON)

INSERT		#Sample
SELECT		TOP 100 PERCENT
		c.A,
		c.B
FROM		(
			SELECT		TOP 100 PERCENT
					r.theOrder,
					r.A,
					r.B
			FROM		(
						SELECT	0 AS theOrder, 3 AS A, 1 AS B UNION ALL
						SELECT	1 AS theOrder, 2 AS A, 1 AS B UNION ALL
						SELECT	2 AS theOrder, 2 AS A, 3 AS B UNION ALL
						SELECT	3 AS theOrder, 1 AS A, 2 AS B
					) AS r
			ORDER BY	r.theOrder
		) AS c
ORDER BY	c.theOrder

SELECT	A,
	B
FROM	#Sample



E 12°55'05.25"
N 56°04'39.16"

Edited by - SwePeso on 02/06/2008 16:15:32
Go to Top of Page

nathans
Aged Yak Warrior

USA
933 Posts

Posted - 02/06/2008 :  16:28:05  Show Profile  Reply with Quote
Now try that with your index creates reversed:


CREATE TABLE	#Sample
		(
			A TINYINT,
			B TINYINT
		)


CREATE UNIQUE NONCLUSTERED INDEX IX_RecvID ON #Sample (B) WITH (IGNORE_DUP_KEY = ON)
CREATE UNIQUE NONCLUSTERED INDEX IX_SendID ON #Sample (A) WITH (IGNORE_DUP_KEY = ON)

INSERT		#Sample
SELECT		TOP 100 PERCENT
		c.A,
		c.B
FROM		(
			SELECT		TOP 100 PERCENT
					r.theOrder,
					r.A,
					r.B
			FROM		(
						SELECT	0 AS theOrder, 3 AS A, 1 AS B UNION ALL
						SELECT	1 AS theOrder, 2 AS A, 1 AS B UNION ALL
						SELECT	2 AS theOrder, 2 AS A, 3 AS B UNION ALL
						SELECT	3 AS theOrder, 1 AS A, 2 AS B
					) AS r
			ORDER BY	r.theOrder
		) AS c
ORDER BY	c.theOrder

SELECT	A,
	B
FROM	#Sample


Nathan Skerl
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 02/06/2008 :  16:41:13  Show Profile  Visit SwePeso's Homepage  Reply with Quote
That worked. So the ORDER of the creating of indexes are important?
But I still don't get the record inserted by theOrder column.

With the indexes created the opposite order, I now get
A	B
1	2
2	3
3	1
where I should have get
A	B
3	1
2	3
1	2
if the theOrder ORDER BY clause where working.


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 02/06/2008 :  16:44:59  Show Profile  Visit SwePeso's Homepage  Reply with Quote
I also noticed this with TG's suggestion to create the indexes the reverse order
CREATE TABLE	#Sample
		(
			RowID TINYINT IDENTITY(1, 1),
			theOrder TINYINT,
			A TINYINT,
			B TINYINT
		)

CREATE UNIQUE NONCLUSTERED INDEX IX_RecvID ON #Sample (B) WITH (IGNORE_DUP_KEY = ON)
CREATE UNIQUE NONCLUSTERED INDEX IX_SendID ON #Sample (A) WITH (IGNORE_DUP_KEY = ON)

INSERT		#Sample
		(
			theOrder,
			A,
			B
		)
SELECT		TOP 100 PERCENT
		c.theOrder,
		c.A,
		c.B
FROM		(
			SELECT		TOP 100 PERCENT
					r.theOrder,
					r.A,
					r.B
			FROM		(
						SELECT	0 AS theOrder, 3 AS A, 1 AS B UNION ALL
						SELECT	1 AS theOrder, 2 AS A, 1 AS B UNION ALL
						SELECT	2 AS theOrder, 2 AS A, 3 AS B UNION ALL
						SELECT	3 AS theOrder, 1 AS A, 2 AS B
					) AS r
			ORDER BY	r.theOrder
		) AS c
ORDER BY	c.theOrder

SELECT		RowID,
		theOrder,
		A,
		B
FROM		#Sample
ORDER BY	theOrder
The output is now (as expected)
RowID	theOrder	A	B
1	0		3	1
3	2		2	3
4	3		1	2

E 12°55'05.25"
N 56°04'39.16"

Edited by - SwePeso on 02/06/2008 17:00:56
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 02/06/2008 :  16:46:25  Show Profile  Visit SwePeso's Homepage  Reply with Quote
And compare above post with this!
CREATE TABLE	#Sample
		(
			RowID TINYINT IDENTITY(1, 1),
			theOrder TINYINT,
			A TINYINT,
			B TINYINT
		)

CREATE UNIQUE NONCLUSTERED INDEX IX_SendID ON #Sample (A) WITH (IGNORE_DUP_KEY = ON)
CREATE UNIQUE NONCLUSTERED INDEX IX_RecvID ON #Sample (B) WITH (IGNORE_DUP_KEY = ON)

INSERT		#Sample
		(
			theOrder,
			A,
			B
		)
SELECT		TOP 100 PERCENT
		c.theOrder,
		c.A,
		c.B
FROM		(
			SELECT		TOP 100 PERCENT
					r.theOrder,
					r.A,
					r.B
			FROM		(
						SELECT	0 AS theOrder, 3 AS A, 1 AS B UNION ALL
						SELECT	1 AS theOrder, 2 AS A, 1 AS B UNION ALL
						SELECT	2 AS theOrder, 2 AS A, 3 AS B UNION ALL
						SELECT	3 AS theOrder, 1 AS A, 2 AS B
					) AS r
			ORDER BY	r.theOrder
		) AS c
ORDER BY	c.theOrder

SELECT		RowID,
		theOrder,
		A,
		B
FROM		#Sample
ORDER BY	theOrder
With the indexes created the original order, the output is now
RowID	theOrder	A	B
2	1		2	1
4	3		1	2
Hey! Where did RowID 1 go?

E 12°55'05.25"
N 56°04'39.16"

Edited by - SwePeso on 02/06/2008 17:01:37
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
5932 Posts

Posted - 02/06/2008 :  16:47:27  Show Profile  Reply with Quote
your table does not have a clustered index so it is a heap. As you know the rows have no garaunteed order. Your select statement is selecting out of this heap:

SELECT	A,
	B
FROM	#Sample

has no ORDER BY clause and the results will likely have nothing to do with the order of SELECT statement that was used to populate the table.

Be One with the Optimizer
TG
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 02/06/2008 :  16:52:14  Show Profile  Visit SwePeso's Homepage  Reply with Quote
I have edited my previous two responses for more clarification.
How come RowID 1 is missing when indexes are created "original" order, and present when creating the indexes the reverse order?



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
5932 Posts

Posted - 02/06/2008 :  17:01:07  Show Profile  Reply with Quote
It was Nathan's point about the order of the index creation...But my point is that there are 2 different things you are lumping together and (I believe) are wrong about expecting:

1) the result of a select statement is not necessarily the order of the data in the table.
2) in a table without a clustered index, you don't know the order of the rows even though you attempted to control it by the INSERT statement that populated it.

Be One with the Optimizer
TG
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 02/06/2008 :  17:07:27  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Sorry Nathan, didn't mean to diminish your input.

What about RowID and theOrder correlation?

With the indexes created the reverse order, RowID and theOrder are correlated and I get the result I expect.
With the indexes created the original order, RowID and theOrder are still correlated but I don't get the result I expect. And RowID 1 is missing.


E 12°55'05.25"
N 56°04'39.16"

Edited by - SwePeso on 02/06/2008 17:09:15
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
5932 Posts

Posted - 02/06/2008 :  17:36:06  Show Profile  Reply with Quote
>>What about RowID and theOrder correlation?
Not sure what you mean. As you say both examples are correlated.

>>but I don't get the result I expect. And RowID 1 is missing.
This is pure conjecture but...
I have a feeling that this index option (ignore_dup_key) is doing stuff they don't tell you in books online. It seems like There is a seperate process for each constraint that is working through the transactions rows that are about to be inserted and removing the rows that will violate a constraint. Depending on which constraint is worked on first, a different set of rows will be removed.


EDIT:
I mean rows, not transactions

Be One with the Optimizer
TG

Edited by - TG on 02/06/2008 17:37:38
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 02/06/2008 :  17:48:10  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Yeah, I had a hunch that query engine would use unique indexes in any order when checking them, but completely omitting RowID 1?



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 02/06/2008 :  18:00:22  Show Profile  Visit SwePeso's Homepage  Reply with Quote
I got this explanation from Craig
quote:
The result depends on the order in which the rows are considered for insertion. Since this example has two unique indexes, the server independently checks for and eliminates duplicates from both unique columns. Since the target table starts out empty, the only duplicates it finds are those in the initial input set. Recall that to find duplicates in the input set, the server sorts the rows and selects only the first row from each group of rows with the same key.

Suppose that after the sort on the first column, the input set has the following order:

(1 2) (1 3) (2 3) (2 1) (3 1) (3 2)

The server discards every other row leaving:

(1 2) (2 3) (3 1)

Now the server sorts on the second column:

(3 1) (1 2) (2 3)

And since there are no duplicates on the second column, it inserts all three rows.

Now suppose that the after the sort on the first column, the last two rows are reversed:

(1 2) (1 3) (2 3) (2 1) (3 2) (3 1)

This is still a valid sort order, but the server discards a different row leaving this set instead:

(1 2) (2 3) (3 2)

Again, the server sorts on the second column:

(1 2) (3 2) (2 3)

This time there is a duplicate on the second column, so the server discards it and inserts only these two rows:

(1 2) (2 3)


It explains both test cases. And with TG and Nathan I realized why I got the same result when inserting with ORDER BY theOrder DESC.

But how to know which index it starts with?
And why is RowID 1 missing?


E 12°55'05.25"
N 56°04'39.16"

Edited by - SwePeso on 02/06/2008 18:42:25
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
5932 Posts

Posted - 02/06/2008 :  18:43:31  Show Profile  Reply with Quote
>>And why is RowID 1 missing?

Craig's explaination answers your question.
quote:
Recall that to find duplicates in the input set, the server sorts the rows and selects only the first row from each group of rows with the same key


>>But how to know which index it starts with?
My guess is it iterates through indid values. Which would explain why adding them in a different order changes the results.

Peso, I can see why you are so good...An unresolved question to you is like a meaty bone to a dog.
DROP IT...DROP IT...

Be One with the Optimizer
TG
Go to Top of Page

jdaman
Constraint Violating Yak Guru

USA
354 Posts

Posted - 02/06/2008 :  18:49:58  Show Profile  Reply with Quote
This is why Sqlteam.com > the other sql forums I browse... if something doesnt seem to make sense sure enough the experts will pound their brains into a pulp until it does. And then we lesser techs get to reap the benefits without the headache.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 02/07/2008 :  03:08:39  Show Profile  Visit SwePeso's Homepage  Reply with Quote
To wrap this up, it seems that identity values are assigned to complete insert before checking for constraints such us unique indexes.
This also explains why this leaves a gap in the identity sequence.
DECLARE	@Items TABLE (i INT IDENTITY(1, 1), j INT PRIMARY KEY)

INSERT	@Items
SELECT	1 UNION ALL
SELECT	2

SELECT	*
FROM	@Items

INSERT	@Items
SELECT	1

INSERT	@Items
SELECT	2

INSERT	@Items
SELECT	3

SELECT	*
FROM	@Items



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Previous Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000