dwain.c (5/19/2015)

---

Part of the issue I had with your question is in this paragraph. You say "indexed on the appropriate fields" but unfortunately that is a rather subjective statement....

That was a question, not a conclusion. Notice the question mark at the end of the first sentence. The second was simply a restatement of what I'm trying to do,...

dwain.c (5/18/2015)

---

Another way:

SELECT *

INTO #Test

FROM (Values

(1, 'A'),

(1, 'B'),

(1, 'C'),

(1, 'D'),

(1, 'E'),

(2, 'A'),

(2, 'C'),

(2, 'D'),

(2, 'E'),

(3, 'A'),

(3, 'B'),

(3, 'C'),

(3, 'F'),

(4, 'A'),

(4, 'B'),

(4, 'D'),

(4, 'F'),

(4, 'G'))x(id, value);

SELECT id

...

Got it - fat-fingered an edit when I copied out some parts of the query for my original post. Works great - many thanks, Luis. Complex execution plan, that's going...

Well, here's the actual query, if you want to take a stab at it, but I haven't assembled all the necessary TSQL to give you the entire data environment on...

That looks promising, but I'm getting an error:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when...

Thank you everyone for the input and suggestions. I've decided to go with a trigger, largely because everything is in one place. The check constraint option has the disadvantage of...

Emil Bialobrzeski (12/11/2014)

---

Yes the parameters are from the incoming record while you do an insert

ALTER TABLE Test ADD CONSTRAINT CK_Test CHECK (dbo.CKTest(ID, Letter, Number, OptionalLetter) = 0)

EDIT:

Use the code...

Emil Bialobrzeski (12/11/2014)

---

Don't be to harsh on me i just done it quickly while doing my other work. In the example below i'm assuming that if the optional letter does...

Hm, that's an interesting thought, but how would you code it? The function would need the infro from the...

Brandie Tarvin (12/11/2014)

---

As much as it pains me to say this (because it could cause performance issues), I think your best bet is to put in an AFTER trigger to...

Brandie Tarvin (12/11/2014)

---

pdanes (12/11/2014)

---

I didn't specify schema, and in fact, have no idea how a schema...

I didn't specify schema, and in fact, have no idea how a schema could be used...

Thanks, but neither of those queries worked as written - they have scope errors with the aliases. I'll take a look at them later today and see if I can...

Excellent script - many thanks. Already found and fixed some indexing screw-ups in my databases, just on the first run-through. This is something I will be studying in detail, and...