Sql-server – Should a table have a clustered index even if it doesn’t have appropriate fields for it

clustered-indexindexsql server

If you have a table without a good candidate field or fields for a clustered index (stable, sequential), is it better to have a clustered index on a bad field or is it better to make all table indices non-clustered? What's the rule of thumb?

UPDATE

As per feedback, here is a very specific example to make the question more concrete. Assume I have a 'PlayerStatsView' table, that looks like the following:

CREATE TABLE [PlayerStatsView](
    [PlayerId] [uniqueidentifier] NOT NULL,
    [TeamId] [uniqueidentifier] NOT NULL,
    [FirstName] [nvarchar](255) NOT NULL,
    [LastName] [nvarchar](255) NOT NULL,
    [Status] [nvarchar](255) NOT NULL,
    [BattingAvg] [int] NOT NULL,
    [RBIs] [int] NOT NULL,
    [HomeRuns] [int] NOT NULL,
    [PercentageOnBase] [int] NOT NULL,
    CONSTRAINT [PK_PlayerStatsView] PRIMARY KEY NONCLUSTERED ([PlayerId] ASC)
)

So in more concrete terms…

PlayerId, the primary key, is a GUID, as is TeamId. FirstName and LastName combined are unique (or just about), but will be nowhere near sequential, not to mention they're too big to both include in a single index. (For the purpose of this exercise, assume that player names change occasionally – not often, but from time to time.) The rest of the fields will be updated with every write.

I'm mainly going to be querying by PlayerId and TeamId, I will have a non-clustered index on each.

The table currently contains tens of thousands of records, and will eventually hit hundreds of thousands.

Back to the question:

Am I better off without a clustered index, or should I add a clustered index even though there are no fields that are really suitable for one?

Best Answer

1) IF PlayerId is assigned with NEWSEQUENTIALID, you could consider that as the clustered index.

2) Otherwise, you can add an IDENTITY and make that clustered (questionable benefit, since all access will be through the PK you have already established).

3) Or you can leave it as a heap - with appropriate non-clustered indexes.

My order of preference would be 1, 3, 2 assuming you can't change the uniqueidentifier to an IDENTITY instead.

Can you explain why you are using uniqueidentifier in the first place? - that may have some bearing on this.

Related Question