May 24, 2004 at 2:21 am
Hi,
I have a table, Employee
EmpID - PK, Internal ID
EmpCode - VarChar(10)
EmpName - VarChar(50)
These are the main attributes of the table.
I want to execute 3 queries against this table
1) Select * from Employee where EmpCode like '%P%
2) Select * from Employee Where EmpName like 'K%'
3) Select * from Employee Where EmpCode like '%P%' and EmpName like 'K%'
I want to set non clustered indexes for both EmpCode and EmpName.
My question is:
1.Do I need to create two separate non clustered indexes for both the fields, EmpCode and EmpName
OR
2.I can create a single non clustered index using the combination of EmpCode and EmpName
Which is the best solution and why?
Please help
Regards,
Jag
May 24, 2004 at 3:47 am
The general answer would be to create two seperate indexes. This is because you plan to do queries which are restricted on one field only ie. queries 1 and 2 above. In this case, the query optimiser will be able to use the appropriate index for each query, and has the option of using index intersection on those occasions where you restrict on both fields.
Were you to always query on both fields, then obviously the composite index would be fine.
Having said that, this is a nice narrow table, and given that it's holding employee details, do you really need these indexes? Unless you have a LOT of employees, then the chances are that the indexes won't be used anyway. The flip side of that is that the indexes won't take up much space so why not go ahead and create them anyway - I presume you won't be doing frequent, large scale inserts/deletes/updates.
Basically, this sort of stuff isn't worth losing too much sleep over until you're dealing with large data sets, so why not go ahead and try your queries with and without the various indexes, though don't be surprised if table scans are still shwoing in the query plan.
May 25, 2004 at 2:51 am
Jag,
Keep in mind, that the optimiser won't use any indexes on
1) Select * from Employee where EmpCode like '%P%
If you want the optimiser to choose any of your indexes you should never put the % sign at the start of your search literal
Bye
Gabor
May 25, 2004 at 5:46 am
nyulg is quite correct. There is no way the optimiser can use an index to search for a string begining with a % sign.
If you are getting users performing these type of queries on your data you need to educate them. I had a similar problem in my old job where complaints were coming in about the slowness of searches. We managed to get them to search for surnames by using the first two or three characters and then the wildcard.
I don't know about sql server, but I used to be able to find out who was running these long running queries in oracle using a script. I have never seen the equivalent in sql server. Perhaps somebody out there has one you could use. And myself of course.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply