Whether or and in can use Indexes

Let's talk about database issues. I have seen several statements on the Internet. Let's take a look at whether they are correct or not, and whether they are ambiguous or misleading.

1. or may cause a full table scan.

2. in may cause a full table scan.

3. in may cause a full table scan, which is equivalent to or.

4. Improper use of or statements may cause full table scan.

To avoid misunderstandings and narrow down the scope of the discussion, first explain a term and provide a prerequisite. Glossary:Full table Scan:In a database, querying a table without an index is generally called a full table scan. Full table scan is used by the database server to search for each record of the table until all records meeting the given conditions are returned.Lead: http://baike.baidu.com/view/2010124.htm? Fr = ala0_1_1

Prerequisites:

Database: SQL Server2000 + sp4 Note: The sp4 patch package must be installed. If no patch package is installed, the execution plan may be different from that when sp4 is installed)

Other databases have not been studied, so we will not discuss it here.

Well, the terms and prerequisites have been both agreed. Let's start to discuss them.

The fourth statement is that I used google to search for it and it is very clear. Or "improper use" can cause full table scanning. if used properly, full table scanning can be avoided. The example in this article is also very clear. Http://www.zbitedu.com /? Action-viewthread-tid-39219

Here, I have to praise the strength of google. The structure searched by google is clear and can put it first. Baidu, regardless of November 21, takes care of whether you are right or not, and all of them are included. Then you can analyze and think for yourself. Bs baidu.

However, the first three statements are not clear, but they can be used together with the statements of the fortune teller. That is to say, if you do not explicitly say "yes", it will cause a full table scan, and do not say whether there are any exceptions. It is vague and easy to mislead people. Q: Do you subconsciously add a certain attribute or it will definitely cause a full table scan? If you have not added the attribute "yes", have you ever wondered whether there is a counterexample?

If there is no counterexample, it is correct to add "yes". Why not add it?

If there is a counterexample, the original statement is completely unclear.

There are no counterexamples. This is a very ambiguous and misleading place.

Of course -- in and or are equivalent -- I agree with this sentence. In And or are indeed equivalent. The database converts in to or.

Start Analysis

Take the Employees table of a Northwind database as an example. This is the database that comes with SQL Server2000.) analyze the execution plans of several SQL statements.

 
 

  
  
SELECT *   
  
  
FROM Employees   
  
  
WHERE (EmployeeID IN (2, 4, 5))   
  
  
SELECT *   
  
  
FROM Employees   
  
  
WHERE EmployeeID = 2 or EmployeeID =  4 or EmployeeID =  5  
 
 

The execution results of these two SQL statements are consistent, and the Execution Plan is also consistent. Let's take a look at whether the EmployeeID field has an index or what type of index the execution plan looks like.

1. There is no clustered index or non-clustered index for the employee ID instead of the primary key)

It can be clearly seen from the execution plan that full table scanning is indeed triggered without indexing. Please don't try to draw conclusions. I still haven't read the results in two cases .)

2. Primary Key clustered index)

When it is a primary key and a clustered index, the execution plan changes to avoid full table scanning.

3. A non-clustered index is set instead of a primary key.

This execution plan has changed again, but it still does not cause full table scanning, but adds a step to use tags)

I wanted to see what the execution plan looks like when there is only a primary key, but the primary key field does not set index clustering and non-aggregation), but I found a small problem, I don't know how to make the fields set to the primary key have no indexes? The Enterprise Manager forcibly binds the primary key and clustered index together, sets a field as the primary key, and sets the clustered index to this field. Currently, I have not found how to remove the index of this primary key. Maybe you should set a primary key for the table using SQL statements. This will not be studied first.

Summary: Will in and or cause full table scan? Depends on the situation. That is, it depends on whether the index can be used.

Title of the original article: or, in, whether or in can use indexes?

Link: http://www.cnblogs.com/jyk/archive/2010/04/10/1708945.html

Edit recommendations]