How to create a really useful index in SQL Server?

Question

I am learning to optimize queries and one of the tasks is to optimize this query (I warn you, it is VERY simple)

Select Emp.Emp_Id
   ,Emp.First_Name
   ,Emp.Last_Name
   ,Emp.Title
   ,Dep.Name Dept_Name
   ,Br.NAME Branch_Name
From   Employee   Emp
   join Department Dep on Emp.Dept_Id = Dep.Dept_Id
   join BRANCH Br on emp.ASSIGNED_BRANCH_ID=Br.BRANCH_ID

I tried creating indexes:

CREATE INDEX index_dept3 ON Employee (Dept_Id);
CREATE INDEX index_branch3 ON Employee (ASSIGNED_BRANCH_ID);

but they don't show up in the query plan and don't affect the execution time. Are there any other ideas?

question from:https://stackoverflow.com/questions/65643856/how-to-create-a-really-useful-index-in-sql-server

Answer 1

Solution path A

Don't optimize until you need to. When queries start getting slow on the data structures and data volume you have, have a look at the execution plan (Ctrl-L); it will probably tell you where an index may be helpful.

Solution path B

If you already have an idea about your data volume and the selections you'll need, you might create indexes in advance. For large tables, an index per foreign key will probably be useful. Same for columns that will be used as selection criteria, in particular if they allow to reduce the result set significantly. So, an index on the last name will probably have more effect than one on the department number - but only if you select by the last name at all.

Years ago, they would have told you not to define too many indexes to avoid the overhead in keeping them up to date. In my experience, with tables of a few million rows, building an index from scratch takes no more than a few seconds, so the overhead argument is only valid if you have really huge tables or extreme performance requirements. So the main reason to define only those indexes which are really needed is to keep your database design tidy and slim. When you leave the project, your successors won't know whether this or that part of the database definition is crucial or not. No one will dare to remove any part of it, and your DDL will grow and grow, ultimately creating maintenance hell. That's one of the reasons to recommend path A: the premature optimisation anti-pattern.