SET ROWCOUNT @Variable

SELECT * FROM authors

ORDER BY au_lname, au_id

SET ROWCOUNT 0

SELECT PERSON.LAST_NM, FIRST_NM, ADDRESS.Text

FROM PERSON INNER JOIN ADDRESS

ON PERSON.EMPLOYEE_ID=ADDRESS.EMPLOYEE_ID

where PERSON.LAST_NM = @LAST_NM AND FIRST_NM = @FIRST_NM

Don't know how to confirm the stats... The reindex should've taken care of it (please correct me if I'm wrong).

Parameter sniffing

http://www.w3schools.com/sql/sql_join.asp

There's the keyword... You shouldn't have hardcoding whenever possible. Imagine this app 3 years from now. You have coded that in 9-10 places and now you have to...

The real question is why are they not already in such a table??

Seems too much like an homework question...

This is just a simple inner join, there's no way a programmer can't figure this one out.

Can't work, names are repeatable so you might have 2 John Smith>>returning 2 ids.

In what context would you want to do that?

1 - Performance killer and also the db can do it for you so why bother??

2 - Yup you must know what you're doing... but even then dropping the constraint...

I'd guess about 8 .

How many rows in each table?

Are the stats up to date and was the sp recompiled after that.

Did you check...

Works great... but you now have unvalidated data in the table so you might has well drop the constraint altogether.

Can we see the code that generates this?

Can you start this from the begining??

What do you have to do exactly?

Transactions per minute.

Can you imagine the overhead with 10K TPM??????

Might be usefull, bu that would have to be in a trace flag or something...