CODEWITHSUNDEEP
sqlsql-servert-sql
user4451265
user4451265

Reputation:

Is there a need to add index in a column if it's already in order?

To my knowledge indexes arrange items in the column to speed up the search operations, but what if the column is already ordered alphabetically, and I want to look for a name in the LastName column, does the index improve the search in this case?

Upvotes: 2

Views: 372

Answers (4)

paparazzo
paparazzo

Reputation: 45096

If a column just happens to be sorted but not indexed then a search on that column will be a table scan. Without an index the query optimizer cannot take advantage of the sorted data.

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521389

There is a big difference between an alphabetical index on a name column and the name column being sorted alphabetically.

Querying on an alphabetical index:
When you run the query SELECT LastName FROM TABLE... the database engine will take the LastName you specified and enter it into an index (hashing) function. The output of this index function tells the database exactly where to look in memory to find the record containing the LastName, and this lookup is extremely fast O(1).

Querying on an alphabetically sorted LastName column without an index:
When you run the same SELECT query as above, the database has to look through the entire table to find the record you want. Even though the LastName column may be in perfect alphabetical order, the database engine won't know this, and therefore won't be able to take advantage of the order. The lookup time will be much slower in this case for a large table.

So the answer to your question is yes, placing an index on the column will make a difference, even if all records are already alphabetically sorted on the LastName.

Upvotes: 0

dario
dario

Reputation: 5269

Only a CLUSTERED INDEX order the data physically.

Anyway you can't rely on the CLUSTERED INDEX order. Only because you "see" ordered data, it doesn't mean it will always be.

Order is guaranteed only by placing an ORDER BY clause in your queries.

So, if you use that column in WHERE clauses, creating an index for that column would speed up your queries. But beware this really depends on several other factors.

Upvotes: 3

idstam
idstam

Reputation: 2878

All indexes except one (the Clustered index) are stored outside of the table and doesn't change the order of rows in the actual table.

Upvotes: 0

Related Questions