SQL Indexes

P

Phylum

Well-known member
Joined
Jun 20, 2003
Messages
105
Location
Canada
I have created a table with a primary key and 2 indexes. If I want to lookup information using an index do I have to tell SQL somehow in my SELECT statement? If not, then how does it know to use an index other than the primary key?
 
You *can* give SQL Server optimizer "hints", but I wouldnt recommend it. You can place the hints in your SQL code to tell the compiler to use a particular index but the optimizer should be picking the correct index in just about every case (assuming youre keeping your indexes fairly up to date). The rare cases are when you perform a large bulk insert that doesnt update the indexes, then the optimizer may choose badly. But hopefully youre having SQL Server rebuild the indexes after a large bulk load. In fact, if I remember right, there are options that rebuild after large loads (a table grows by more than 25% or something like that). Dont hold me to that - you may have to turn that on manually, but Im pretty sure it *can* be made to update them like that automatically.

There are other hints you can give, such as NOLOCK, on a query. The NOLOCK hint is really a directive - it tells SQL Server to disregard any other higher orders to lock on the SELECT. Useful in a transaction where you need to read some values that can be "dirty" reads but you also need locking on other SELECTS.

-nerseus
 
Nerseus said:
You *can* give SQL Server optimizer "hints", but I wouldnt recommend it. You can place the hints in your SQL code to tell the compiler to use a particular index but the optimizer should be picking the correct index in just about every case (assuming youre keeping your indexes fairly up to date). The rare cases are when you perform a large bulk insert that doesnt update the indexes, then the optimizer may choose badly. But hopefully youre having SQL Server rebuild the indexes after a large bulk load. In fact, if I remember right, there are options that rebuild after large loads (a table grows by more than 25% or something like that). Dont hold me to that - you may have to turn that on manually, but Im pretty sure it *can* be made to update them like that automatically.

There are other hints you can give, such as NOLOCK, on a query. The NOLOCK hint is really a directive - it tells SQL Server to disregard any other higher orders to lock on the SELECT. Useful in a transaction where you need to read some values that can be "dirty" reads but you also need locking on other SELECTS.

-nerseus
Click to expand...

So if I understand you correctly, if I create an index in MS SQL, I dont have to do anything programatically to use it. SQL sees that it CAN use it, and does.
 
@TheWizardofInt: That is absolutely right. SQL Server has a piece called an optimizer, which analyses many things to determine which index(es) to use. I dont claim to be the most knowledable in that area, but I recently got a chance to work with one of the original programmers of the optimizer code. He apologized for earlier versions (before version 7), but had tremendous insight into how SQL Server will pick an index.

In the simplest case, if you have an index on LastName and your query is something like "SELECT * FROM CustName WHERE LastName LIKE SMI%", then the optimizer will likely pick it. Theres a chance it wont, but I wouldnt worry about it.

There are a couple of ways to see what indexes are being used, if any. Theres a button to show execution plan, but its a bit hard to read if you ask me. There are a couple of SET options that will turn on better plan analysis if you want. It will show what indexes are being used, in what order, how many records each index will filter to, and whether the index is SEEKing (good) or SCANing (bad). It will also show you if there are no indexes being used (table scan, usually bad).

-ner
 
Nerseus said:
@TheWizardofInt: That is absolutely right. SQL Server has a piece called an optimizer, which analyses many things to determine which index(es) to use. I dont claim to be the most knowledable in that area, but I recently got a chance to work with one of the original programmers of the optimizer code. He apologized for earlier versions (before version 7), but had tremendous insight into how SQL Server will pick an index.

In the simplest case, if you have an index on LastName and your query is something like "SELECT * FROM CustName WHERE LastName LIKE SMI%", then the optimizer will likely pick it. Theres a chance it wont, but I wouldnt worry about it.

There are a couple of ways to see what indexes are being used, if any. Theres a button to show execution plan, but its a bit hard to read if you ask me. There are a couple of SET options that will turn on better plan analysis if you want. It will show what indexes are being used, in what order, how many records each index will filter to, and whether the index is SEEKing (good) or SCANing (bad). It will also show you if there are no indexes being used (table scan, usually bad).

-ner
Click to expand...

Thanks - you saved me a lot of steps in learning how to do this, and I really appreciate it
 
Back
Top