That's true.. almost all of the select (well, the time-critical ones) are done on the [RandomOrder],[CampaignID],[Status] columns.. so it's prefferable to have the table clustered on these columns.

The primary key...

oki, willdo that

Ouch, good spotting!

I've this was a compilation from the creation script of the DB. It seems the table is initially created w/ a clustered PK , then that index is...

I was also suspecting a CASCADE because of the indexes somewhere..

What kills me is that everytime I recreate this situation "by hand", I don't get this behaviour. Only if...

Yes, all foreign keys in the table are pointing to PKs in the other tables.. and those tables are all clustered on their respective PK. It's just your basic table...

Well, that's what I don't understand:

The columns that I modify are not part of a foreign key or a constraint. .. And they are not the PK either.

The PK...

Nope, no triggers.

5 Keys: 1 primary and 4 foreign

3 constraints : all of them about default values on some columns

0 triggers

3 indexes :

IX_GetByRandom (clustered) on [RandomOrder],[Status],[CampaignID]

...

Looks great ! Let me test this on the real DB for a while. Sounds just like what I needed !

Forever in debt !;)

Edit:Actually, it needs a bit of editing....

It seems to work indeed. Haven't used CTE before :-), looks nice.

Only problem is the MustHaveNotOrdered table. In my real-life case it can mean a list of 1.000.000 rows (ok,...

Well, in reality there are a few more tables involved, but for the sake of this problem YES, it applies to all clients in the #ClientProduct table.

Ok, good tip on the SQL scripts.

Let's see :

CREATE TABLE #ClientProduct (ClientID INT, ProductID INT)

INSERT #ClientProduct VALUES (1,30)

INSERT #ClientProduct VALUES (1,35)

INSERT #ClientProduct VALUES (2,31)

INSERT #ClientProduct VALUES (3,40)

INSERT #ClientProduct VALUES...