|
|
Previous examples showed advantages of "Index Seek" operator and how one can
gain it, instead of "Table Scan" and other slow creatures. But we have
to remind that use of index can not be taken as ultimate solution for every
optimization plan. Uncorrectly defined or unnecessary index can harm and
cause slow query execution. Lets look at tiny Departments table.
| DeptNo | DeptName | BuildingNum |
| 1 | management | 1
| | 2 | logistic | 2
| | 3 | advertizing | 3
| | 4 | accounting | 4
| | 5 | sales | 4
| | 6 | product design | 1
|
Imagine that we are frequently looking for Department number.
SELECT DeptNo
FROM dbo.Departments
WHERE DeptName LIKE '%logistic%';
|
Adding index on [DeptName AND/OR DeptNo] columns will not boost performance
of the query. For such a small table "Table Scan" is as fast or better than
use of index.
Trying to identify best performance schema for the Query, beeing aware that
Building-Number is frequently updated column in Departments table:
SELECT a.EmpNo, a.FirstName, a.LastName,
b.BuildingNum AS works_in_building_no
FROM Employees AS a
INNER JOIN Departments AS b
ON a.DeptNo = b.DeptNo
WHERE a.EmpNo = 5;
|
It will be nice to have DeptNo columns indexed in both tables. But i
suppose that it will be unnecessary to index BuildingNum, cause a need
to constantly update such index will waste server engine resources, with a
prospect of tiny/or zero time saving for this specific query.
|