6

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?

Improve this question
6
  • 2
    There is no rule of thumb. If you can't figure out what the clustered index should be based on a variety of factors, post a real example.
    – Aaron Bertrand
    Jun 26, 2012 at 17:28
  • Is it out of the question to use a surrogate key to use for the clustered index? It might take a little carressing to get the column in there and populated if it's already a full table, but it is an option.
    – Thomas Stringer
    Jun 26, 2012 at 17:31
  • 1
    Remi, if you have a specific scenario to ask about, feel free to edit this post and flag it for moderator attention. I'll be happy to reopen it for you. As currently written there's no way to answer it. The answer will depend entirely on the scenario, and the question as written is too generic.
    – JNK
    Jun 26, 2012 at 17:41
  • Ok, I will, later. But I have to say I personally find the question reasonable as-is - I was hoping for guidelines. Clearly, experienced DBAs base their decisions upon a set of rules or a mental model they've built up over the years. As a software dev who only occasionally needs to dive deeply into databases, a general understanding is what I'm looking for, not a quick answer to a specific scenario.
    – Remi Despres-Smyth
    Jun 26, 2012 at 17:46
  • @RemiDespres-Smyth Well I think the DBA has to consider a LOT. Books have been written on indexing strategy, and very long blog series have been written about just clustered index choices. It's a complicated topic and not something that can be easily covered in its entirety here.
    – JNK
    Jun 26, 2012 at 17:47

2 Answers 2

Reset to default
5

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.

Improve this answer
4
  • Guids are being generated and assigned in software, not in the database. Changing keys to identities is not an option. I can add an identity field to the table, which would be ignored by the software, but I admit that feels like a waste to me - tho I wouldn't hesitate if the benefits are worthwhile.
    – Remi Despres-Smyth
    Jun 26, 2012 at 19:23
  • My use of Guids is related to the software design, not the database design. I'm using event sourcing for my write models, which requires uniquely identifiable instances across all aggregate types.
    – Remi Despres-Smyth
    Jun 26, 2012 at 19:29
  • 1
    @RemiDespres-Smyth Leaning towards 2 more than 3 right now because of the size of data and fragmentation; see these two links: mssqltips.com/sqlservertip/1254/clustered-tables-vs-heap-tables technet.microsoft.com/en-us/library/cc917672.aspx
    – Cade Roux
    Jun 26, 2012 at 19:57
  • Appreciate your taking the time and your thoughts. Links were helpful as well - I immediately see a few tables where I don't need to be as concerned, given the activity on the tables, and a few others that I should absolutely add an Identity to.
    – Remi Despres-Smyth
    Jun 27, 2012 at 2:57
1

I believe for SQLServer in general not having clustered index is not a good practice. At least it's what they say

With few exceptions, every table should have a clustered index. Besides improving query performance, a clustered index can be rebuilt or reorganized on demand to control table fragmentation. A clustered index can also be created on a view.

http://msdn.microsoft.com/en-us/library/ms186342(v=sql.105).aspx

Adding identity (or implement it with sequence if you already switched to SQLServer 2012) primary key column is [almost] always an option

Improve this answer
3
  • The keys in my database are nearly all GUIDs, and (from what I've read and seen) it isn't a good idea to use them as clustered indexes. In some cases, the other fields are not at all static (as they will change regularly) or are not at all sequential (such as names or phone numbers). This is why I ask - whether it's best to define a clustered index anyhow.
    – Remi Despres-Smyth
    Jun 26, 2012 at 17:42
  • As further evidence that Microsoft expects you to put a clustered index on your tables, the "SQL Azure" online version of SQL Server requires a clustered index on every table.
    – Darin Strait
    Jun 26, 2012 at 19:02
  • 1
    I think in most cases it's better to add clustered index on (add new identity column if needed). On the other hand, having poorly chosen clustered index - assuming you are not allowed to add new columns - will have worse consequences than heap tables, especially with high load. From your table definition I feel you are working on sport stats; business domain itself implies lots of insert/updates due to live nature of data. If you already face performance issues it may be worth considering refactoring database(I know, it is painful, but doable)
    – a1ex07
    Jun 26, 2012 at 20:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.