Clustered vs Nonclustered

Both are B-trees. The difference is what sits at the leaf level.

• Clustered index — the leaf level is the table. The rows are stored in clustered-key order. A table can have exactly one, because rows can only be physically ordered one way. A table with no clustered index is a heap: rows in no particular order.
• Nonclustered index — the leaf level holds the index key columns, any INCLUDE columns, and a pointer back to the row (the clustered key, or a RID for a heap). A table can have many.

That "pointer back to the row" is the source of Key Lookups from Module 2. It is also why the choice of clustered key affects every nonclustered index on the table: the clustered key is duplicated into all of them.

What makes a good clustered key

Four properties, in priority order:

1. Narrow. It is copied into every nonclustered index and every row of them. An int costs 4 bytes; a uniqueidentifier costs 16, everywhere, multiplied by rows and by index count.
2. Ever-increasing. New rows append at the end rather than inserting into the middle, which avoids page splits. IDENTITY and datetime2 insertion timestamps do this naturally; a random NEWID() does not. (NEWSEQUENTIALID() exists for when you need a GUID that still appends.)
3. Unique. If you declare a non-unique clustered index, SQL Server silently adds a 4-byte uniquifier to duplicates. Better to be explicit.
4. Static. Updating a clustered key physically moves the row and updates the pointer in every nonclustered index.

The common default — int IDENTITY primary key, clustered — satisfies all four, which is why it is the default. It is not always the best choice for a reporting table, though. If a fact table is always queried by date range, a clustered index on (OrderDate, OrderId) puts the rows a report needs physically adjacent, so a month of data is a contiguous read rather than scattered pages. That is often the single biggest structural win available to a reporting workload.

Note that PRIMARY KEY and CLUSTERED are separate decisions that happen to default to the same column:

-- Primary key on the surrogate, but cluster on the reporting access path
CREATE TABLE dbo.Orders (
    OrderId    int IDENTITY(1,1) NOT NULL,
    OrderDate  date              NOT NULL,
    CustomerId int               NOT NULL,
    Total      decimal(18,2)     NOT NULL,
    CONSTRAINT PK_Orders PRIMARY KEY NONCLUSTERED (OrderId)
);

CREATE CLUSTERED INDEX CX_Orders_OrderDate ON dbo.Orders (OrderDate, OrderId);

Heaps

A table with no clustered index. Occasionally justified for staging tables that are bulk-loaded and truncated, where you never seek. In a reporting database they are usually an accident, and they cause RID lookups and forwarded records (a row that grows past its page leaves a pointer behind, so reads follow a chain).

Find them:

SELECT  OBJECT_SCHEMA_NAME(t.object_id) AS schema_name,
        t.name                          AS table_name,
        p.rows,
        SUM(a.total_pages) * 8 / 1024   AS size_mb
FROM sys.tables      AS t
JOIN sys.indexes     AS i ON i.object_id = t.object_id AND i.index_id = 0   -- 0 = heap
JOIN sys.partitions  AS p ON p.object_id = t.object_id AND p.index_id = i.index_id
JOIN sys.allocation_units AS a ON a.container_id = p.hobt_id
GROUP BY t.object_id, t.name, p.rows
ORDER BY size_mb DESC;

A multi-gigabyte heap that a report reads from is worth a conversation.

Seeing what you already have

Before designing anything, inventory the table. This shows every index, its key columns in order, its INCLUDE columns and its filter:

DECLARE @table sysname = N'dbo.Orders';   -- placeholder: your report's main table

SELECT  i.name                     AS index_name,
        i.type_desc,
        i.is_unique,
        i.is_primary_key,
        i.filter_definition,
        STUFF((SELECT ', ' + c.name + CASE WHEN ic.is_descending_key = 1 THEN ' DESC' ELSE '' END
               FROM sys.index_columns AS ic
               JOIN sys.columns       AS c ON c.object_id = ic.object_id AND c.column_id = ic.column_id
               WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id
                 AND ic.is_included_column = 0
               ORDER BY ic.key_ordinal
               FOR XML PATH('')), 1, 2, '')  AS key_columns,
        STUFF((SELECT ', ' + c.name
               FROM sys.index_columns AS ic
               JOIN sys.columns       AS c ON c.object_id = ic.object_id AND c.column_id = ic.column_id
               WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id
                 AND ic.is_included_column = 1
               ORDER BY c.name
               FOR XML PATH('')), 1, 2, '')  AS included_columns,
        p.rows,
        SUM(a.total_pages) * 8 / 1024 AS size_mb
FROM sys.indexes AS i
JOIN sys.partitions AS p ON p.object_id = i.object_id AND p.index_id = i.index_id
JOIN sys.allocation_units AS a ON a.container_id = p.hobt_id
WHERE i.object_id = OBJECT_ID(@table)
GROUP BY i.object_id, i.index_id, i.name, i.type_desc, i.is_unique, i.is_primary_key, i.filter_definition, p.rows
ORDER BY i.index_id;

Run this every time before you add an index. A large fraction of "missing index" recommendations turn out to be near-duplicates of an index that already exists with the columns in a different order.

Rule of thumb

The clustered index is the table, so the key wants to be narrow, increasing, unique and stable — it is duplicated into every nonclustered index. On a reporting fact table, consider clustering on the date rather than the identity, because that makes a date-range report a contiguous read instead of scattered pages.