Menu
Suppose I'm running a query that has:
WHERE column1 = "value1"
AND column2 = "value2"
column1 is indexed, and column2 is not. Does the order of my WHERE clause matter? Should I run a subquery over the indexed column first? Or, is SQL smart enough to automatically query over the indexed column first?
811
The order of columns is critical. Now which order is correct it depends on how you are going to query it. An index can be used to do an exact seek or an range scan. An exact seek is when values for all columns in the index are specified and the query lands exactly on the row is interested in.
Figure 1 shows a properly ordered index in relation to its table. If, on the other hand, an improper order is selected for the columns in the index, then the index will not be a linear match to the source table, and the result will be a large clustering factor that is closer to the number of rows in the table.
Answer: No. Sr. Developer in my organization asked me the following question about WHERE clause. In SQL Server order does not matter in the WHERE condition.
The column-Name that you specify in the ORDER BY clause does not need to be the SELECT list. An integer that identifies the number of the column in the SelectItems in the underlying query of the SELECT statement.
The order in the SQL statement does not matter, certainly not for indexes that are not covering indexes (more than one column).
Covering indexes require that there be a reference in the query for at least one column, starting from the left of the list. IE: A covering index defined as "column1, column2, column3" needs queries to at least reference column1 in order to use the index. A query that only has references to either column2, or a combination of column2 and column3 would not use the covering index.
That said, index decisions by the optimizer are determined by table statistics & how fragmented the index is at the time of the query. Neither of these is self-maintaining, because depending on the amount of data can be very time consuming (so you wouldn't want it happening all the time). Having an index doesn't guarantee the index will always be used.
Indexes are also not ANSI, but surprisingly vendors (MySQL, Oracle, etc) have relatively similar syntax & naming.
95
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
