One reason to prefer `INCLUDE` over key-columns **if you don't need that column in the key** is documentation. That makes evolving indexes much more easy in the future.
Considering your example:
~~~
CREATE INDEX idx1 ON MyTable (Col1) INCLUDE (Col2, Col3)
~~~
That index is best if your query looks like this:
~~~
SELECT col2, col3
FROM MyTable
WHERE col1 = ...
~~~
Of course you should not put columns in `INCLUDE` if you can get an additional benefit from having them in the key part. Both of the following queries would actually prefer the `col2` column in the key of the index.
~~~
SELECT col2, col3
FROM MyTable
WHERE col1 = ...
AND col2 = ...
~~~
~~~
SELECT TOP 1 col2, col3
FROM MyTable
WHERE col1 = ...
ORDER BY col2
~~~
Let's assume this is **not** the case and we have `col2` in the `INCLUDE` clause because there is just no benefit of having it in the tree part of the index.
Fast forward some years.
You need to tune this query:
~~~
SELECT TOP 1 col2
FROM MyTable
WHERE col1 = ...
ORDER BY another_col
~~~
To optimize that query, the following index would be great:
~~~
CREATE INDEX idx1 ON MyTable (Col1, another_col) INCLUDE (Col2)
~~~
If you check what indexes you have on that table already, your previous index might still be there:
~~~
CREATE INDEX idx1 ON MyTable (Col1) INCLUDE (Col2, Col3)
~~~
Now you know that `Col2` and `Col3` are not part of the index tree and are thus not used to narrow the read index range nor for ordering the rows. Is is rather safe to add `another_column` to the end of the key-part of the index (after `col1`). There is little risk to break anything:
~~~
DROP INDEX idx1 ON MyTable;
CREATE INDEX idx1 ON MyTable (Col1, another_col) INCLUDE (Col2, Col3);
~~~
That index will become bigger, which still has some risks, but it is generally better to extend existing indexes compared to introducing new ones.
If you would have an index without `INCLUDE`, you could not know what queries you would break by adding `another_col` right after `Col1`.
~~~
CREATE INDEX idx1 ON MyTable (Col1, Col2, Col3)
~~~
What happens if you add `another_col` between `Col1` and `Col2`? Will other queries suffer?
There are other "benefits" of `INCLUDE` vs. key columns **if you add those columns just to avoid fetching them from the table**. However, I consider the documentation aspect the most important one.
To answer your question:
> what guidelines would you suggest in determining whether to create a covering index with or without the INCLUDE clause?
If you add a column to the index for the sole purpose to have that column available in the index without visiting the table, put it into the `INCLUDE` clause.
If adding the column to the index key brings additional benefits (e.g. for `order by` or because it can narrow the read index range) add it to the key.
You can read a longer discussion about this here:
[To see links please register here]