关于Where子句条件 [英] Regarding Where clause condition

问题描述

亲爱的朋友们，我对SQL Server中的where子句感到困惑.

让我用一个例子来解释它.可以说有一个名为Employee的表，其中包含两列 EMP_Name 和 EMP_City .

现在，当我运行此查询时:-

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''

然后它返回我5000条记录，并在我运行以下查询时:-

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

select * from Employee where EMP_City=''delhi''

我得到10,000条记录.所以现在当我在两个过滤器都打开的情况下运行查询时:-

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''

然后从上述两个查询中，哪个查询执行时间将更少，这意味着哪个查询将更快地返回结果以及原因.
谢谢

Varun Sareen

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

推荐答案

阅读并亲自进行评估

http://www.sqlteam.com/article/sql-server-indexes-the-basics [^ ]

Read this and evaluate this by yourself

http://www.sqlteam.com/article/sql-server-indexes-the-basics[^]

性能调整是处理SQL时要考虑的主要因素.
根据SQL理论，
性能会影响以下条件
·来自WHERE子句的条件的可用索引
·用OR条件将查询重写为UNION
·JOIN条件的可用索引
·ORDER BY子句的可用索引
·GROUP BY子句的可用索引
·从内存表中选择
·SELECT INTO与INSERT SELECT

因此，如果在条件所在的位置提供主键，标识，数字列，则搜索会更快.

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.

最后一个选择应该是最快的，因为与该城市的雇员相比，该雇员的名称应少一些.

SQL将执行第一个子句，然后执行第二个子句，因此第一个子句应始终是结果最少的那个子句，除非您仅在城市上建立索引.

我希望这能消除您的一些困惑.

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.

这篇关于关于Where子句条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！