February 28, 2011 at 7:44 am
Hi,
we have a table named "Employees". It has fields company, empno, active and some additional fields like (lastname, firstname, middle name, address etc). I created an index on company, empno. When I query the table as follows, I can see that the sql server uses index.
select company, empno from dbo.Employees
However, If I use a query as follows it doesn't use the index. I checked the count of records where active = 'A'. There are 525 records. And there are 2118 records where active = 'T'. I thought that the sql server uses index to get the company and empno and then does a lookup to find out if the employee is active.
select company, empno from dbo.Employees where Active = 'A.
If we use the above query in most of our stored procedures, is it better to create an index on company, empno, active?
February 28, 2011 at 7:51 am
Since you're not including the Active field in the index, SQL Server isn't going to use it for that query. If it did, it would first scan the index, then have to go to the table to check the Active flag. That's 2 reads for every record, as opposed to just scanning the table directly. If you want SQL Server to use an index, I'd create the index on Active and have the other 2 fields as included columns. That is, unless you sometimes include them in your search criteria, i.e where Active = 'Y' and Company = 'whatever'.
C
February 28, 2011 at 7:53 am
Sridhar-137443 (2/28/2011)
Hi,we have a table named "Employees". It has fields company, empno, active and some additional fields like (lastname, firstname, middle name, address etc). I created an index on company, empno. When I query the table as follows, I can see that the sql server uses index.
select company, empno from dbo.Employees
However, If I use a query as follows it doesn't use the index. I checked the count of records where active = 'A'. There are 525 records. And there are 2118 records where active = 'T'. I thought that the sql server uses index to get the company and empno and then does a lookup to find out if the employee is active.
select company, empno from dbo.Employees where Active = 'A.
If we use the above query in most of our stored procedures, is it better to create an index on company, empno, active?
Query is asking for about 20% or the rows, it will do a full table scan which is cheaper in terms of I/O
For this particular query, by creating an index on (company,empno,active) SQL Server wouldn't even go to the table - that's the beauty of covered indexes.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.February 28, 2011 at 7:55 am
Thank you. sometimes we use queries like "select * from dbo.Employees where company = @company and empno = @empno" when we know the company and empno.
when we join to another tables we either use active field or not based on the requirement.
so, is it better to have an index with "company, empno, active" as columns in that order?
February 28, 2011 at 8:03 am
If you're using all three columns as possible criteria, go from most unique to least in the index. Of course, if this is a relatively static table, there's no real harm in creating 2 different indexes. One as I originally described, to handle the queries based solely on Active, and another index covering all three, in the order you specified.
February 28, 2011 at 8:07 am
Sridhar-137443
Can you execute your most used quires using SSMS, if you can click on the button "Show Actual Execution Plan", and execute the procedure, if production work load is such that you can not do that, then activate the "Show Estimated Execution Plan". In either case review the execution plan, Use those plans as a base, to test and compare execution plans for any query you modify. This should inform you as to what indexes are most useful. Of course if you have the facilities performing these tests on a non production DB would be the best practice.
February 28, 2011 at 9:30 am
Thank you bitbucket. I do run most of the queries using SSMS. I usually do "Include Actual Execution Plan". That is how I found about this. I will run some tests.
Colleen, We do inserts into that table rarely. We update the "Active" field when employee is termed. But we update other fields frequently which are not included in the index. so, does this change my index criteria?
February 28, 2011 at 9:41 am
Updates to fields not covered by the index would not cause any extra overhead of maintaining the index, so no concerns there.
You just want to keep in mind when you create indexes that they need to be maintained and therefore incur some overhead when doing inserts/updates/deletes. But it doesn't sound like this is really a factor in your case.
Run some tests with one or both indexes with statistics io turned on, see what's more efficient.
February 28, 2011 at 7:57 pm
Colleen M. Morrow (2/28/2011)
Updates to fields not covered by the index would not cause any extra overhead of maintaining the index, so no concerns there.
"It Depends". If an update to a row on a nearly full page occurs and that update makes the row physically bigger (like when you update a VARCHAR value to have more characters), you could get a page split which will inherently affect the clustered index even if no columns in the clustered index were updated.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply