Internal Working mechanism in SQl Server

[SqlHunter]

Iam using a stored procedure where the @emp_number is the input parameter and I want to know how internally this both queries work and which gives more performance....

SELECT
emp_name,emp_number,emp_state,emp_address,emp_status
FROM
tbl_emp_info
WHERE
emp_id = (
select max(emp_id) from tbl_emp_info WHERE emp_number = @emp_number
and emp_status in ('Active')
)

Here I declare a variable and get the max emp_id first and then pass it to the main query..Which one gives better performance and how does that internally fire in SQL server

Delcare @max_emp_id INT
SET @max_emp_id = select max(emp_id) from tbl_emp_info WHERE emp_number = @emp_number
and emp_status in ('Active')

SELECT
emp_name,emp_number,emp_state,emp_address,emp_status
FROM tbl_emp_info
WHERE emp_id=@max_emp_id

 

[SQLSister]

SELECT
emp_name,emp_number,emp_state,emp_address,emp_status
FROM
tbl_emp_info ei
join (select max(emp_id) from tbl_emp_info WHERE emp_number = @emp_number 
and emp_status in ('Active')) a  
onei.emp_id = a.emp_id

This is how I would probably do this as the join is usually faster than the correlated subquery. However as with anything in performance tuning, there are no absolutes, you need to test against your own system and see which works better.

If you want to see how SQL server is interpreting how to run these statements look at the execution plans.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[chiph]

The execution plan is the first place I would start too. But I'd follow up with doing a trace with representative data.

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first