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?
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?