Just out of curiosity - SQL Server Forums

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.

All Forums

Site Related Forums

The Yak Corral

Just out of curiosity

Author

Topic

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-05 : 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 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

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

pootle_flump

1064 Posts

Posted - 2008-02-05 : 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 - 2008-02-05 : 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 - 2008-02-05 : 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 - 2008-02-05 : 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 - 2008-02-05 : 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 - 2008-02-05 : 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
Yak-finder General

1961 Posts

Posted - 2008-02-05 : 11:14:46

Is this inspired by Craig Freedman's recent blog on IGNORE_DUP_KEY?

http://blogs.msdn.com/craigfr/archive/2008/01/30/maintaining-unique-indexes-with-ignore-dup-key.aspx

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-05 : 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 back

SendID	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
Yak-finder General

1961 Posts

Posted - 2008-02-05 : 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 - 2008-02-05 : 12:16:54

I think I can, because 1 out of 15 execution I do get full formation back, as

SendID	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 are

SendID	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 - 2008-02-05 : 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
Yak-finder General

1961 Posts

Posted - 2008-02-05 : 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

Posted - 2008-02-05 : 12:27:57

these are Euler squares and the only impossible Euler squares are of order two and six.
http://mathdl.maa.org/convergence/1/?pa=content&sa=viewDocument&nodeId=1434&pf=1

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-05 : 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
Yak-finder General

1961 Posts

Posted - 2008-02-05 : 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 - 2008-02-05 : 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 - 2008-02-05 : 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 are
SendID 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 - 2008-02-06 : 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 - 2008-02-06 : 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 - 2008-02-06 : 12:55:56

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

Nathan Skerl

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources