Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author 
Topic 
SwePeso
Patron Saint of Lost Yaks
30421 Posts 
Posted  20080205 : 06:41:23

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 19. 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 13 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
E 12°55'05.25" N 56°04'39.16" 

pootle_flump
1064 Posts 
Posted  20080205 : 06:48:22

Yeah  that's right. You sure you don't want composite indexes? At max this table can have nine rows... 


pootle_flump
1064 Posts 
Posted  20080205 : 06:55:21

Sorry  I'm wrong  I don't know why only 3. But I can see why no more than 9...
EDIT  duh  yes I can. Each unique index omits rows. 


SwePeso
Patron Saint of Lost Yaks
30421 Posts 
Posted  20080205 : 07:11:21

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.
Any more ideas?
E 12°55'05.25" N 56°04'39.16" 


pootle_flump
1064 Posts 
Posted  20080205 : 07:46:16

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. 


SwePeso
Patron Saint of Lost Yaks
30421 Posts 
Posted  20080205 : 08:53:49

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.
E 12°55'05.25" N 56°04'39.16" 


SwePeso
Patron Saint of Lost Yaks
30421 Posts 
Posted  20080205 : 10:49:53

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...
E 12°55'05.25" N 56°04'39.16" 


Arnold Fribble
Yakfinder General
1961 Posts 

SwePeso
Patron Saint of Lost Yaks
30421 Posts 
Posted  20080205 : 12:00:10

No, I am optimizing my application for with Belbin usage http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=68606
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 I often get this result backSendID RecvID 1 2 2 3 The mystery is why I don't get 3 1 back too...
E 12°55'05.25" N 56°04'39.16" 


Arnold Fribble
Yakfinder General
1961 Posts 
Posted  20080205 : 12:08:39

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. 


SwePeso
Patron Saint of Lost Yaks
30421 Posts 
Posted  20080205 : 12:16:54

I think I can, because 1 out of 15 execution I do get full formation back, asSendID RecvID 1 2 2 3 3 1  Often missing record The mystery for me is that why don't I always get three records back?
I know if 2 first records areSendID RecvID 1 2 2 1 We have a circular reference. I also can't put 3 3 to the resultset because then Sender and Receiver is same person.
E 12°55'05.25" N 56°04'39.16" 


SwePeso
Patron Saint of Lost Yaks
30421 Posts 
Posted  20080205 : 12:19:24

quote: Originally posted by Arnold Fribble
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.
Please don't say it can't be done.
E 12°55'05.25" N 56°04'39.16" 


Arnold Fribble
Yakfinder General
1961 Posts 
Posted  20080205 : 12:27:00

quote: Please don't say it can't be done.
If you Tarry longer, I shall give worse payment! 


spirit1
Cybernetic Yak Master
11752 Posts 

SwePeso
Patron Saint of Lost Yaks
30421 Posts 
Posted  20080205 : 12:34:37

Yes, but I only need Latin squares. For me
A B B A
is a valid result, because I don't care where the officers are located (color). I only need to combine their skills "A or "B" (ranks).
E 12°55'05.25" N 56°04'39.16" 


Arnold Fribble
Yakfinder General
1961 Posts 
Posted  20080205 : 16:43:06

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. 


SwePeso
Patron Saint of Lost Yaks
30421 Posts 
Posted  20080205 : 16:46:44

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.
E 12°55'05.25" N 56°04'39.16" 


nathans
Aged Yak Warrior
938 Posts 
Posted  20080205 : 22:59:06

quote: Originally posted by Peso
The mystery for me is that why don't I always get three records back?
I know if 2 first records areSendID RecvID 1 2 2 1 We have a circular reference.
Why not order by v2.Number to ensure your first two rows are:
2 1 3 1
What is purpose of newid() ?
Nathan Skerl 


SwePeso
Patron Saint of Lost Yaks
30421 Posts 
Posted  20080206 : 03:26:50

2 1 3 1
Is not a valid resultset, since an ID can only be present once in any column.
I think I will stick with my while loop for the time being.
E 12°55'05.25" N 56°04'39.16" 


nathans
Aged Yak Warrior
938 Posts 
Posted  20080206 : 12:46:07

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
Nathan Skerl 


nathans
Aged Yak Warrior
938 Posts 
Posted  20080206 : 12:55:56

Ok, Craigs most recent post [see Arnolds link above] explains this pretty well.
Nathan Skerl 


Next Page




