Click here to Skip to main content
15,038,345 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
See more:
Dear Friends, I have a confusion regarding the where clause in SQL Server.

Let me explain it with an example. Lets say there is a table named Employee which contains two columns EMP_Name and EMP_City.

Now when i run this query:-

select * from Employee where EMP_Name='Arun'


Then it returns me 5000 records and when i run the following query:-

select * from Employee where EMP_City='delhi'


I get 10,000 records. So now when i run the query with both the filters ON like this :-

select * from Employee where EMP_City='delhi' and EMP_Name='Arun'

OR
select * from Employee where EMP_Name='Arun' and EMP_City='delhi'


Then from the above two queries which queyr execution time will be less means which query will return the result faster and why.

Thanks

Varun Sareen
Posted
Updated 19-Apr-12 20:35pm
v2

Read this and evaluate this by yourself

http://www.sqlteam.com/article/sql-server-indexes-the-basics[^]
   
Comments
Member 8779364 20-Apr-12 2:55am
   
It doesn't clears out my confusion dear friend.
Performance tunning is the major factor to be considered while dealing with SQL.
according to SQL theory,
Performance affect on following conditions
· Available indexes for conditions from WHERE clause
· Rewriting a query with OR conditions as a UNION
· Available indexes for JOIN conditions
· Available indexes for ORDER BY clause
· Available indexes for GROUP BY clause
· Select from in-memory tables
· SELECT INTO vs INSERT SELECT

so if you give Primary key, Identity, Numeric columns in where condition then search will be faster.
   
Comments
Member 8779364 20-Apr-12 3:04am
   
Dear Friend, Try to understand my question what i am trying to ask here that from the mentioned two queries which query execution time will be faster and why..I know the fundamentals and advantages of indexing and affect of various joins on SQL query.

I mean that what difference will be put on the query execution time if the filters applied on the query are interchanged.
Hi,

The last option should be the fastest as you should have less employees with that name then employees in that city.

SQL will perform the first clause and then the second so the first should always be the one with least results unless you are only indexing on the city.

I hope this clears some of your confusion.
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900