Some corrections...
The article implies (thought doesn't outright say) that the rows in a table are physically sorted by the clustered index. This is not necessarily true, the order that the rows appear within pages and within the file can differ from the clustered index order. The clustered index enforces a logical sort order and SQL tries as much as possible to make the logical and physical sort order the same when the cluster is created or rebuilt, but it is not guaranteed.
If there is no clustered index, or if it is not unique, then non-clustered index records do have the physical address.
Only if the base table is a heap do the nonclustered index records get the RID (the row identifier). If the table has a clustered index then the nonclustered indexes get the clustering key. If the clustered index is not unique SQL adds a uiniquifier (a 4 byte int) which will also appear in the nonclustered index records.
Putting a Primary Key on a column has the effect of giving it a clustered index.
By default only. It's possible (and in some cases a good idea) to create a nonclustered primary key
When to use an index
Consider columns that have a UNIQUE constraint. Having an index on the column makes it easier for SQL Server to check whether a new value would not be unique.
No need, because unique constraints are enforced by unique indexes. Hence if you add an index to a column that has a unique constraint you're adding a duplicate index, wasting space and wasting performance.
Look at putting an index on at least one column involved in every JOIN. If you join two tables with 500 rows each, this potentially creates a set of 500 * 500 = 250000 rows - so an index on a JOIN can make a big difference.
It only creates such a large resultset if you're doing a cross join, and indexes won't help that. Indexes are recommended on foreign key columns, but not for the reason given here.
Low Specificity
Even if there is an index on a column, the query optimizer won't always use it.
If the index is covering for the query, SQL will use it even if there are only 2 values in a million rows.
The query optimizer is unlikely to use a non-clustered index for a price whose specificity is below 85%.
The figure's closer to 99%, assuming the index in question is not covering.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability