quote:

---

The mystery is why I don't get 3 1 back too...

---

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

SendID	RecvID
1	2
2	3

SendID	RecvID
1	2
2	3
3	1

2	1

3	1

2	1
1	2

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

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

A	B
1	2
2	3
3	1

A	B
3	1
2	3
1	2

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

RowID	theOrder	A	B
1	0		3	1
3	2		2	3
4	3		1	2

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

RowID	theOrder	A	B
2	1		2	1
4	3		1	2

SELECT	A,
	B
FROM	#Sample

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)

---

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

---

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