• I did finally find some information in BOL (Creating Nonclustered Indexes) which helps explain things for me. For the benefit of people who do not know what a "heap" is (or are programmers new to SQL Server): simply (not getting into Extends, Pages, B-Trees, etc) a heap is just an "un-ordered" storage of data on the hard drive, versus a clustered index storing the data in a specified order.

    [FROM BOL]: Nonclustered indexes are implemented in the following ways:

    PRIMARY KEY and UNIQUE constraints

    When you create a PRIMARY KEY constraint, a unique clustered index on the column or columns is automatically created if a clustered index on the table does not already exist and you do not specify a unique nonclustered index. The primary key column cannot allow NULL values.

    When you create a UNIQUE constraint, a unique nonclustered index is created to enforce a UNIQUE constraint by default. You can specify a unique clustered index if a clustered index on the table does not already exist. For more information, see PRIMARY KEY Constraints and UNIQUE Constraints.

    Index independent of a constraint

    By default, a nonclustered index is created if clustered is not specified. The maximum number of nonclustered indexes that can be created per table is 249. This includes any indexes created by PRIMARY KEY or UNIQUE constraints, but does not include XML indexes.

    Nonclustered index on an indexed view

    After a unique clustered index has been created on a view, nonclustered indexes can be created. For more information, see Creating Indexed Views.

    Thanks for all of the inputs everyone.... it helped me do more search(s) online for data to explain this! 🙂 🙂

    Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)