..which in turn means that there *IS* a difference performance-wise between being lazy with 'select *' or spelling out the actual columns called for...

Well, interview questions can be... 'funny' in many ways...

In answer to 5) - which is actually two questions in one..?

(and I agree that the wording is vague at best..)

Yes,...

Yes, that's correct.

declare @err1 int, @err2 int

EXEC @err1 = myProc @val1

SET  @err2 = @@ERROR

..now @err1 will contain the return code from the proc

@err2 will contain the result of...

The problem posed stated that the table1.column to be compared against was indeed unique.

It also said that the size was about 800 million.

Table2 size around a billion (20%) larger, and...

It happens

As to which would be best in this case, one has to try both and also preferrably look at the plans produced...

In this query:

Select top 100 field1, field2

from table1 t1

inner join table2 t2 on t1.field1 = t2.field1

order by t2.field1

.. you are requesting the top 100 *ordered* rows, which means that SQL...

SELECT     t1.column

FROM       table1 t1

LEFT JOIN  table2 t2

ON         t1.column = t2.column

WHERE      t2.column IS NULL

..or..

SELECT t1.column

FROM   table1 t1

WHERE NOT EXISTS ( SELECT * FROM table2 t2 WHERE t1.column...

Yes, @@ERROR is reset with *every* command, so it may be easiest to get used to always catch @@ERROR into your own local variable - then do whatever errorchecking stuff.

(in...

Is 25 mins for 10 million rows really that awful?

In any case, there's two parts to optimize.

The first is usually easier than the second.

First part is the search part of...

And still the same strange messages?

It seems to be networkrelated in any case (as the text also says)

Try googling on 'SQLSTATE=08S01' and see if any of the links may fit...

Check your logs, jobs etc if you have some recurring scheduled activity going on at 5pm each day.

Another option may be to set up a a trace and see if...

Since the use of dynamic SQL seems to be warranted only by not wanting 'to type too much', do also read this, so you're aware of the cosequences of introducing dynamic...

Which version are you on?

See if this adresses your problem:

http://support.microsoft.com/kb/872842

Also, to make your log easier to read, you can omit all those information-only messages

by including the 'with no_infomsgs'...

You'll find plenty by reading the docs (Books On Line aka BOL)

You can find it here:

http://www.microsoft.com/downloads/details.aspx?FamilyID=be6a2c5d-00df-4220-b133-29c1e0b6585f&DisplayLang=en

/Kenneth

Ususally, there are two reasons to why a server grinds to a halt.

You've found a 'feature' (aka bug - the thing hangs/crashes) or you've hit a bottleneck (ie resources are...