SQL Indexes

[Phylum]

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?

[Phylum]

I think I found the answer myself. Query analyzer determines which index to use based on speed.

[Nerseus]

You *can* give SQL Server optimizer "hints", but I wouldn't 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 you're keeping your indexes fairly up to date). The rare cases are when you perform a large bulk insert that doesn't update the indexes, then the optimizer may choose badly. But hopefully you're 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). Don't hold me to that - you may have to turn that on manually, but I'm 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

[TheWizardofInt]

You *can* give SQL Server optimizer "hints", but I wouldn't 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 you're keeping your indexes fairly up to date). The rare cases are when you perform a large bulk insert that doesn't update the indexes, then the optimizer may choose badly. But hopefully you're 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). Don't hold me to that - you may have to turn that on manually, but I'm 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

 

So if I understand you correctly, if I create an index in MS SQL, I don't have to do anything programatically to use it. SQL sees that it CAN use it, and does.

[Nerseus]

@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 don't 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. There's a chance it won't, but I wouldn't worry about it.

 

There are a couple of ways to see what indexes are being used, if any. There's a button to show execution plan, but it's 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

[TheWizardofInt]

@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 don't 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. There's a chance it won't, but I wouldn't worry about it.

 

There are a couple of ways to see what indexes are being used, if any. There's a button to show execution plan, but it's 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

 

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