There are two points in the article that I wanted to respond to:
Avoid creating an index in response to the poor performance of a single query.
This needs to be fleshed out more - indexes are created primarily in response to poor query performance. The question is, which queries should be indexed? If a given query is ad hoc and never used by a calling application then there is no immediate benefit to indexing it, regardless of its performance. If the same query was then called hundreds of times a minute by different users then we should create indexes to improve the performance of that query.
Index optimization is a complex topic, and ultimately I don't think it can be reduced to blanket statements such as this one.
When queries against a table do not include a WHERE clause there is no benefit to using a
nonclustered index.
That's incorrect. For example, JOIN or ORDER BY conditions can benefit from a nonclustered index. Imagine a table of contacts where the most frequent query was:
SELECT ContactID, FirstName, LastName FROM Contact ORDER BY LastName ASC, FirstName ASC
If you only have a clustered index on the IDENTITY column then SQL Server will have to scan and sort the results for these two columns every time the query is executed. However if you define a nonclustered index on LastName ASC, FirstName ASC, then SQL Server will be able to read just that index to satisfy the entire query, without needing to do any sorting as this is already done whenever the index is updated.
This will scale particularly well as the table size increases as there is no increasing cost for sorting as there is when only a clustered index is available. There is also now support for seeking values in these columns more efficiently as well.
As others have said, any kind of data access path can potentially benefit from a nonclustered index, and the best way to know what those paths are is to look at the execution plan.
Also for a beginner article, there really needs to be a discussion of how to actually create an index in the first place, either via Management Studio or T-SQL.