Does it make sense to create index with all columns included

If

• your query uses all those fields,
• the sort order needs to be different than that of the clustered index, AND
• you can't change the clustered index,

an additional (covering) index with a different column order may work for you. As with anything index-related, more (and wider) indexes means slower write/update performance. This is particularly true if you're effectively doubling the size of the table.

If you just index OtherTableId without including the other columns, you'll get a smaller index but your query may end up either just using the clustered index with expensive Scan or Sort operators, or using Key Lookups from the non-clustered index, which may be great if you're querying just a few rows at a time but disastrous if you're scanning the entire table.

This is general advice. For specifics, edit your question to include query, table definitions and a query plan.

Isn't there tons of information out there about this? I learned SQL on MySQL and parlayed that into SQL Server, though, so maybe the open source documentation is just better.

The basic thing you need to know is what indexes are for. Then you'll know if you "should" use them or not.

Basically, ask yourself if you will be using a column anywhere other than the SELECT clause of the SQL statement. If yes, consider indexing it. If not, there's much less reason to. As far as I know, the columns listed in the SELECT are far less "expensive" than the ones in JOIN, WHERE, GROUP BY, ORDER BY, etc.

And if yes, the next question is harder to answer, because as mentioned in other answers, there are costs and benefits to indexing. I think it is untrue that indexing increases the table size by the size of the columns you index. The size increase, I believe, is proportional to the cardinality of the index. But in any case it does increase time for inserts. So, if your table is rarely modified and mainly just read from, the. By all means add every possible index "just in case."

But if the table is frequently written to, be more careful. Again most of my knowledge is from MySQL but I believe these apply here too.

1. In MySQL, and probably in SQL Server as well, the first column in a compound index does not need its own separate index.
2. The order of the columns in a compound index is important. It should match the orders used in your statements.
3. If a query is rarely used, the cost of adding an index to speed it up might not be worth the small benefit.
4. If a query is frequently used and the table is large, definitely consider indexing to speed it up.

MySQL and SQL Server both allow you to view query execution plans. You can use that to help determine which column to index (use Google to find more on that)

MySQL has a "slow query log" and I'm sure SQL Server has something similar to help you determine which queries are taking too long (Google it)