Okay, I've officially got too much time on my hands with this vacation thingy. I ran the following modified code on my laptop (8GB RAM, SQL 2008 Express) :

-- Creating...

OCTom (10/23/2012)

---

The question asks how many duplicate keys will be created, not how many can be created. No one can truly have an infinite number of anything. Resources will run...

Which gives you 2, 0 with an error...

Yes, me too. So to make sure whether the second select...

1. Query 1 fails because there are no values provided to INSERT.

2. Query 2 fails because the syntax for SET IDENTITY_INSERT requires a table name.

3. Query 3 succeeds because DEFAULT...

For a deeper understanding of what's going on, try this code:

CREATE TABLE #TABLE1

(

Col1 INTEGER

,Desc1 char(3)

)

CREATE TABLE #TABLE2

(

Col2 INTEGER

,Desc2 char(3)

)

INSERT INTO #TABLE1 VALUES...

Another QotD which does not test what it purports to test.

Even if you know TABLESAMPLE inside and out, you cannot expect to get this question correct without knowing the...

Hugo Kornelis (9/6/2012)

---

Rewriting them with APPLY makes them valid:

FROM Table1 AS t CROSS APPLY (correlated subquery) AS s ON s.Col1 = t.Col1

FROM Table1 AS t CROSS APPLY dbo.MyFunction(t.SomeColumn) AS f...

mbova407 (9/6/2012)

---

I guess I don't understand the question. How is using cross-apply with a where join any different then an inner join with the ON (or where) join

SELECT

...

derek.colley (9/6/2012)

---

Good question, I had it in my head that CROSS APPLY acted like an INNER JOIN and OUTER APPLY acted like an OUTER JOIN, but in fact the CROSS...

GRRR! ANOTHER answer without a Books Online reference! How are we supposed to verify this against the official Microsoft documentation!!!?

😀

Agreed. Also, if you haven't voted on the Connect submission for this issue,...

Hugo Kornelis (8/30/2012)

---

Thanks, Koen! (EDIT: And Braindonor, who added a reply while I was typing)

I submitted this question quite a long time ago - and I found that I had...

mtassin (8/23/2012)

---

I...

For once I am going to say that the table declarations were best left out -- they would not have helped, and just might have muddied the waters.

From the repetitive...