| Author |
Topic |
|
krisrajz
Starting Member
19 Posts |
 Posted - 2010-11-16 : 01:45:47
|
| Hello,How do I write index covering query that involves multiple tables?Thank youRegardsRaj |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-11-16 : 03:09:14
|
| More information needed.A covering index is an index that contains within it all the columns that a query needs from that table. I don't know what you mean by 'index covering query'--Gail ShawSQL Server MVP |
 |
|
|
krisrajz
Starting Member
19 Posts |
Posted - 2010-11-16 : 05:04:24
|
| Want to create a covering index for the below query:select Dname,Ename,Designation,Salary from Emp,Deptwhere Emp.Deptno=Dept.Deptno and ZipCode in ('Z01','Z02','Z03','Z04')order by Emp.employeenoIndex for EmployeeNo, Deptno are already createdNow I need to create index covering query for the columns in the above SQL query. The able has huge amount of data and is going to be joined with few other tables too!Thank youRegardsRaj |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-11-16 : 05:30:28
|
| First things first, write the query properly.select Dname,Ename,Designation,Salary from Emp inner join Dept on Emp.Deptno=Dept.Deptnowhere ZipCode in ('Z01','Z02','Z03','Z04')order by Emp.employeenoIf you want index help, post the definitions of all the indexes that are currently on the tables please.--Gail ShawSQL Server MVP |
|
|
|
krisrajz
Starting Member
19 Posts |
Posted - 2010-11-18 : 00:35:58
|
| Employee number is primary key in Emp table and hence Clustered Index was already createdDeptno number is primary key in Dept table and hence Clustered Index was already createdAdditionally I have created non-unique non-clustered index on deptno column of emp tableRaj |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2010-11-18 : 00:46:03
|
| Looks like for this query, you want to create an index on ZipCode, and Include any other columns in the Select list. I don't know which columns are in which table so I can't say for sure what goes in it, but that is the only useful index I can see outside of the clustered indexes. (Looking at this query in isolation)-Chad |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-11-18 : 03:16:00
|
| Which table are the following in?ZipCodeDesignationSalary--Gail ShawSQL Server MVP |
|
|
|
krisrajz
Starting Member
19 Posts |
Posted - 2010-11-18 : 22:52:42
|
| Dname is from Department's(DEPT) table and the rest from Employee's(EMP) tableZipCode is in Department(DEPT) tableRaj |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-11-19 : 02:44:41
|
| Create an index on DEPT on ZipCode, DeptNo Include DNameChange the index on deptNo on Emp and include EName, Designation, SalaryDo note that this is creating two indexes tailored for one query. It's generally a better idea to try and create indexes so that they are usable by as many queries as possible.--Gail ShawSQL Server MVP |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2010-11-19 : 23:45:11
|
quote:
Create an index on DEPT on ZipCode, DeptNo Include DName
No need to have DeptNo as part of the key, it is there automatically because it is the clustered index key.-Chad |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-11-20 : 07:07:55
|
quote:
Originally posted by chadmat
No need to have DeptNo as part of the key, it is there automatically because it is the clustered index key.
I would still recommend putting it there for a number of reasons1) SQL's smart enough not to add it again2) It makes it very clear to anyone reading the index what the required keys are3) If the index is widened in the future, the position of DeptNo will not change--Gail ShawSQL Server MVP |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-11-20 : 12:18:07
|
| "1) SQL's smart enough not to add it again"I didn't know that, and it is certainly useful to know it! Thanks. |
|
|
|
krisrajz
Starting Member
19 Posts |
Posted - 2010-11-24 : 00:59:52
|
| Thanks indeed Gail Shaw!Raj |
|
|
|
krisrajz
Starting Member
19 Posts |
Posted - 2010-11-24 : 01:05:49
|
| I still have some queries>>Create an index on DEPT on ZipCode, DeptNo Include DName>>Change the index on deptNo on Emp and include EName, Designation, Salary>>Do note that this is creating two indexes tailored for one query. It's generally a better idea >>to try and create indexes so that they are usable by as many queries as possible.You suggested to create two more indexes, 1 each on EMP and DEPT table.I thought of having 3 indexes on DEPT1. Deptno - Clustered (default)2. DName - Unique, Non Clustered3. ZipCode - Non-Unique, Non ClusteredRaj |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-11-24 : 02:43:18
|
| Create whatever indexes you like. The ones I suggested are optimal for this query (do you understand why?)Single column nonclustered indexes are generally not all that useful. Wider (within reason) is usually better. You asked for covering indexes. None of the indexes you proposed are covering for this query.--Gail ShawSQL Server MVP |
|
|
|
krisrajz
Starting Member
19 Posts |
Posted - 2010-11-24 : 04:26:24
|
| I understand, for deterministic queries (fixed columns) index based on multiple columns is better choice both storage and as well as performance wise. Thank you for all the suggestionsRegardsRaj |
|
|
|
|
Index Covering Query