why doesn't this work the way I thought it should work?

I create a #Sudoku table and then create nine unique indexes (one for each column). In the select statement I get all possible combinations of 1-9. I also make sure there are no duplicates "per record".

Then I thougth the nine unique indexes should deal with duplicates per "column".

But this is not happening. I get only 1-3 records per execution. Every execution takes about 5 seconds.

DROP TABLE #Sudoku
CREATE TABLE #Sudoku
(
Row INT IDENTITY(1, 1),
Col1 TINYINT,
Col2 TINYINT,
Col3 TINYINT,
Col4 TINYINT,
Col5 TINYINT,
Col6 TINYINT,
Col7 TINYINT,
Col8 TINYINT,
Col9 TINYINT
)
CREATE UNIQUE NONCLUSTERED INDEX [IX_Col1] ON #Sudoku (Col1) WITH (IGNORE_DUP_KEY = ON)
CREATE UNIQUE NONCLUSTERED INDEX [IX_Col2] ON #Sudoku (Col2) WITH (IGNORE_DUP_KEY = ON)
CREATE UNIQUE NONCLUSTERED INDEX [IX_Col3] ON #Sudoku (Col3) WITH (IGNORE_DUP_KEY = ON)
CREATE UNIQUE NONCLUSTERED INDEX [IX_Col4] ON #Sudoku (Col4) WITH (IGNORE_DUP_KEY = ON)
CREATE UNIQUE NONCLUSTERED INDEX [IX_Col5] ON #Sudoku (Col5) WITH (IGNORE_DUP_KEY = ON)
CREATE UNIQUE NONCLUSTERED INDEX [IX_Col6] ON #Sudoku (Col6) WITH (IGNORE_DUP_KEY = ON)
CREATE UNIQUE NONCLUSTERED INDEX [IX_Col7] ON #Sudoku (Col7) WITH (IGNORE_DUP_KEY = ON)
CREATE UNIQUE NONCLUSTERED INDEX [IX_Col8] ON #Sudoku (Col8) WITH (IGNORE_DUP_KEY = ON)
CREATE UNIQUE NONCLUSTERED INDEX [IX_Col9] ON #Sudoku (Col9) WITH (IGNORE_DUP_KEY = ON)
INSERT #Sudoku
(
Col1,
Col2,
Col3,
Col4,
Col5,
Col6,
Col7,
Col8,
Col9
)
SELECT c1.Number,
c2.Number,
c3.Number,
c4.Number,
c5.Number,
c6.Number,
c7.Number,
c8.Number,
c9.Number
FROM master..spt_values AS c1
INNER JOIN master..spt_values AS c2 ON c2.Type = 'p'
INNER JOIN master..spt_values AS c3 ON c3.Type = 'p'
INNER JOIN master..spt_values AS c4 ON c4.Type = 'p'
INNER JOIN master..spt_values AS c5 ON c5.Type = 'p'
INNER JOIN master..spt_values AS c6 ON c6.Type = 'p'
INNER JOIN master..spt_values AS c7 ON c7.Type = 'p'
INNER JOIN master..spt_values AS c8 ON c8.Type = 'p'
INNER JOIN master..spt_values AS c9 ON c9.Type = 'p'
WHERE c1.Type = 'p'
AND c1.Number BETWEEN 1 AND 9
AND c2.Number BETWEEN 1 AND 9
AND c3.Number BETWEEN 1 AND 9
AND c4.Number BETWEEN 1 AND 9
AND c5.Number BETWEEN 1 AND 9
AND c6.Number BETWEEN 1 AND 9
AND c7.Number BETWEEN 1 AND 9
AND c8.Number BETWEEN 1 AND 9
AND c9.Number BETWEEN 1 AND 9
AND c1.Number NOT IN (c2.Number, c3.Number, c4.Number, c5.Number, c6.Number, c7.Number, c8.Number, c9.Number)
AND c2.Number NOT IN (c1.Number, c3.Number, c4.Number, c5.Number, c6.Number, c7.Number, c8.Number, c9.Number)
AND c3.Number NOT IN (c1.Number, c2.Number, c4.Number, c5.Number, c6.Number, c7.Number, c8.Number, c9.Number)
AND c4.Number NOT IN (c1.Number, c2.Number, c3.Number, c5.Number, c6.Number, c7.Number, c8.Number, c9.Number)
AND c5.Number NOT IN (c1.Number, c2.Number, c3.Number, c4.Number, c6.Number, c7.Number, c8.Number, c9.Number)
AND c6.Number NOT IN (c1.Number, c2.Number, c3.Number, c4.Number, c5.Number, c7.Number, c8.Number, c9.Number)
AND c7.Number NOT IN (c1.Number, c2.Number, c3.Number, c4.Number, c5.Number, c6.Number, c8.Number, c9.Number)
AND c8.Number NOT IN (c1.Number, c2.Number, c3.Number, c4.Number, c5.Number, c6.Number, c7.Number, c9.Number)
AND c9.Number NOT IN (c1.Number, c2.Number, c3.Number, c4.Number, c5.Number, c6.Number, c7.Number, c8.Number)
SELECT *
FROM #Sudoku

Exactly. Every unique index should make sure there were no more than 9 records in the table. And since I INSERT all possible combinations I should get a sudoku board of 9 rows and 9 columns. The INSERT statement itself make sure there are no duplicate value along the record itself.

Oh - you are populating a board. I thought you were preparing some sort of brute force solver. The only thing interesting with the records that get through for me are that they are (inferred from the identity values) from the "beginning" or "end" of the result set.

Almost always I get Row #1. The other one or two records are from the middle and end. They are not consistent either. Sometime I get first and last row, sometimes first and 10,000th row.

If I only create 1 of the 9 indexes, I get 9 records as expected. For 2 or more indexes, I only get 2 or 3 records even if I insert all combinations...

and I thought a Sudoku approach would be more appealing.

The link you provided give some clues but not why it doesn't work. Not even with less columns I get it to work.

CREATE TABLE #Sample
(
SendID TINYINT,
RecvID TINYINT
)
CREATE UNIQUE NONCLUSTERED INDEX IX_SendID ON #Sample (SendID) WITH (IGNORE_DUP_KEY = ON)
CREATE UNIQUE NONCLUSTERED INDEX IX_RecvID ON #Sample (RecvID) WITH (IGNORE_DUP_KEY = ON)
INSERT #Sample
(
SendID,
RecvID
)
SELECT v1.Number,
v2.Number
FROM master..spt_values AS v1
INNER JOIN master..spt_values AS v2 ON v2.Type = 'p'
WHERE v1.Type = 'p'
AND v1.Number BETWEEN 1 AND 3
AND v2.Number BETWEEN 1 AND 3
AND v1.Number <> v2.Number
ORDER BY NEWID()
SELECT SendID,
RecvID
FROM #Sample

If the problem you're trying to solve is "Six different regiments have six officers, each one belonging to different ranks. Can these 36 officers be arranged in a square formation so that each row and column contains one officer of each rank and one of each regiment?" Then I think the answer's no.

Sorry, I don't know anything about IGNORE_DUP_KEY.

"Six different regiments have six officers, each one belonging to different ranks. Can these 36 officers be arranged in a square formation so that each row and column contains one officer of each rank and one of each regiment?"

quote:The mystery for me is that why don't I always get three records back?

Heh, have you tried it with OPTION (MAXDOP 1) ?

I think the short answer to why it isn't going to insert 9 rows is simply that it it's inserting greedily (well, you can hardly expect it to backtrack!) and it's really unlikely to see the permutations in an order that allows it.

Thanks for the input, but as you can see in my simplified example posted 02/05/2008 : 12:00:10, also this doesn't work. One out of 6 executions, I get a valid result, for 1 I get "circular reference" and the other 4 executions I incomplete result.

Why does the insert order matter? Also, the order in which you create the index seems to affect whether you get the 3 rows or not.

In this example, I switch the ordering of the insert. But the same result occurs if you only switch the order of the index creates.

CREATE TABLE #Sample (SendID TINYINT, RecvID TINYINT)
CREATE UNIQUE NONCLUSTERED INDEX IX_SendID ON #Sample (SendID) WITH (IGNORE_DUP_KEY = ON)
CREATE UNIQUE NONCLUSTERED INDEX IX_RecvID ON #Sample (RecvID) WITH (IGNORE_DUP_KEY = ON)
-- insert ordered by SendID
INSERT #Sample (SendID, RecvID)
select 1, 2 union all
select 1, 3 union all
select 2, 1 union all
select 2, 3 union all
select 3, 1 union all
select 3, 2
SELECT 'SendID' [InsOrder], * FROM #Sample
delete #Sample
INSERT #Sample (SendID, RecvID)
select 2, 1 union all
select 3, 1 union all
select 1, 2 union all
select 3, 2 union all
select 1, 3 union all
select 2, 3
SELECT 'RecvID' [InsOrder], * FROM #Sample
drop table #Sample
go