The comment from @afzaal-Ahmad is the right way forward.
There are many articles here on CodeProject that will help you analyse the performance of your queries. Here is a small selection
-
SQL Server Profiler Step by Step[
^]
-
How to Analyze SQL Server Performance[
^]
-
SQL Tuning Tutorial - Understanding a Database Execution Plan (1)[
^]
And this one gives tips on how to improve performance
-
SQL Server Performance Tips and Guidelines[
^]
A couple of things to point out with your examples
- Your first example does not compile in SQL Server - it's a good idea to tag the actual RDBMS that you are using.
- If tableX has more than one row that matches the criteria then you are going to get an error with the line
set @xx= (select * from tableX where fieldX =@input1);
namely
Quote:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
- Similarly if tableX has more than one column you are then going to get the error
Quote:
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
My advice is to get your logic right first, then worry about the performance