3

My goal is to design a table, that can be queried trough an external id(uniqueidentifier), an internal id(bigint), always in combination with companyId(bigint), userId(bigint) and dashboardId(bigint) or in combination with (dashboardId IN @0, ..., @n, n=0,10), both of which conditions play the role of an ownership check.

I came up with the following index compositions:

CREATE CLUSTERED INDEX Mytable_createdBy_cix ON Mytable(companyId, createdBy, dashboardId)

CREATE UNIQUE NONCLUSTERED INDEX Mytable_extId_nix ON Mytable(extId) INCLUDE (valueD, valueN)

CREATE UNIQUE NONCLUSTERED INDEX Mytable_chartId_nix ON Mytable (chartId) INCLUDE (valueD, valueN)

I do not know the answer of the following questions:

  • Is the clustered index bad due to being non-unique? Should I add segregate key and not use the auto-assigned uniqueifier?
  • Are 3 * 8-byte columns + 4 byte uniqueifier(28bytes total) too much for a clustered index? I read it gets included in the include pages of each unique nonclustered index (where additional 16 or 8 bytes are added according to the key used).
  • Does this index design even make sense for the queries below?

I plan on running queries, similar to those:

SELECT chartId, valueD, valueN FROM Mytable WHERE companyId = @companyId AND createdBy = @userId

SELECT chartId, valueD, valueN FROM Mytable WHERE companyId = @companyId AND createdBy = @userId AND dashboardId = @dashboardId

SELECT chartId, valueD, valueN FROM Mytable WHERE dashboardId IN (@0, @1, @2)

SELECT chartId, valueD, valueN FROM Mytable WHERE (companyId = @companyId AND createdBy = @userId AND dashboardId = @dashboardId) OR dashboardId IN (@0, @1, @2)

UPDATE Mytable SET valueD = @valueD WHERE companyId = @companyId AND createdBy = @userId AND chartId = @chartId

UPDATE Mytable SET valueD = @valueD WHERE companyId = @companyId AND createdBy = @userId AND extChartId= @extId

UPDATE Mytable SET valueD = @valueD WHERE ((companyId = @companyId AND createdBy = @userId) OR dashboardId IN (@0, @1, @2)) AND extChartId= @extId

I do know, it is better to test, evaluate execution plans, share them when asking questions on stackexchange, but this is the design phase so no actual data or tables exist yet.

I can adjust the keys/indexes/table structure to better fit the queries. I just hope to get it at least partially right this first time while creating them, so the question does not get revisited.

Big thanks for any help in advance.

6
  • Where does extId come in, is that a typo for extChartId? And why two unique non-clustered indexes, how does that make sense? Jun 26 at 13:40
  • extId = extChartId, it is a typo on my side. The reasoning for the multiple unique indexes is: isn't it better like this? Provide more info to the DB engine? The officials docs said indexes are better when unique; I need multiple for the 2 queries - using internal id and external id. Please tell me if this does not make sense
    – fmi21
    Jun 26 at 13:43
  • Are then both chartId and extChartId guaranteed unique columns? Jun 26 at 13:44
  • Yes, they refer to the same entity in the table Charts. In a previous iteration, we had one only one surrogate key in this table, but then thought it will be better to take the speed of having int keys for internal queries, and expose the uniqueidentifier to the users
    – fmi21
    Jun 26 at 13:45
  • Bit strange then that those updates use other predicates also? Why would you need companyId = @companyId AND createdBy = @userId AND chartId = @chartId if chartId is already unique? Also, unlikely another whole surrogate key will make things much faster Jun 26 at 13:47

2 Answers 2

4

Firstly, note that the uniqueifier on a non-unique key is only added in cases where there are actually duplicate values. It doesn't take up any space if there are no duplicates on the same index page. So unless there are two rows with the exact same companyId, createdBy, dashboardId then this is not going to happen.


Wide clustering keys can be problematic, but they also solve some deadlocking issues, so that may be a factor. It is not by any means clear that your choice of clustering key is right, but on the other hand: how do two UNIQUE non-clustered indexes make sense given the table design? If they are unique then why are all the predicates necessary in these queries?

It seems from your comments that the extra chartId is simply to have a smaller index column. I think this is probably a premature optimization: it simply adds extra indexing costs because you now need to index that column also. I would recommend you remove it, and rely purely on the exrChartId even though it's wider.


For the given queries, you need to work through them and decide how best to satisfy them with indexes. The question of which one should be the clustering index is somewhat orthogonal, as a clustering index effectively INCLUDEs all columns automatically.

Each one may be able to use an index that also satisfies a different one, as long as the leading key columns are the same, irrespective of any additional columns in the key or INCLUDE.

  1. SELECT extChartId, valueD, valueN FROM Mytable WHERE companyId = @companyId AND createdBy = @userId
    This can be satisfied with the following index
    (companyId, createdBy) INCLUDE (extChartId, valueD, valueN)

  1. SELECT extChartId, valueD, valueN FROM Mytable WHERE companyId = @companyId AND createdBy = @userId AND dashboardId = @dashboardId
    This can be satisfied with the following index
    (companyId, createdBy, dashboardId) INCLUDE (extChartId, valueD, valueN)

  1. SELECT extChartId, valueD, valueN FROM Mytable WHERE dashboardId IN (@0, @1, @2)
    This can be satisfied with the following index
    (dashboardId) INCLUDE (extChartId, valueD, valueN)

  1. SELECT extChartId, valueD, valueN FROM Mytable WHERE (companyId = @companyId AND createdBy = @userId AND dashboardId = @dashboardId) OR dashboardId IN (@0, @1, @2) This one is more difficult, and needs an index union (might need to rewrite the query to get that). The required indexes would be the same as #1 and #3

  1. We change this one to only use the natural key, so it's exactly the same as #6

  1. UPDATE Mytable SET valueD = @valueD WHERE companyId = @companyId AND createdBy = @userId AND extChartId = @extId
    Because extChartId is unique, the other columns can go in the INCLUDE
    This therefore needs an index (extChartId) INCLUDE (companyId, createdBy, valueD)

  1. UPDATE Mytable SET valueD = @valueD WHERE ((companyId = @companyId AND createdBy = @userId) OR dashboardId IN (@0, @1, @2)) AND extChartId = @extId
    Again this is difficult to satisfy, due to the OR. It may have been necessary to split this into two separate updates. But given that extChartId is unique, we can again just rely on that same index.

Looking at those indexes, we arrive at the following conclusions:

  • The predicates are all = equality predicates, or IN on a short list, so the key columns can be in any order. This help us immensely in combining the indexes.
  • An index suitable for #1 could have extra columns to suit #2, but not #3. Equally one suitable for #3 could also work for #2 but not for #1. So we need separate indexes. The question remains which of those combinations could also satisfy other queries.
  • #4 can work with the same indexes as the first three, so we won't worry about that.
  • #6 and #7 need extChartId, which you say is unique. Therefore all the rest of the columns can go in the INCLUDE with little performance impact.

So it follows that the best combination of indexes is something like this

(companyId, createdBy, dashboardId) INCLUDE (extChartId, valueD, valueN)
(dashboardId) INCLUDE (extChartId, valueD, valueN)
(extChartId) INCLUDE (companyId, createdBy, dashboardId, valueD)

The question remains which of these you choose for your clustering key. Whichever you choose will INCLUDE all other columns.

The first or third index make the most sense to me. Given that extChartId is unique by itself, it could make more sense to use that due to the size, as you rightly noted.

But deadlocking may also be a problem, depending on the complexity of your transactional updates etc. Start with that, and switch the clustering key if you see it's a problem.

5
  • Thanks for the great answer! One thing I don't understand: Because extChartId is unique, the other columns can go in the INCLUDE This therefore needs an index (extChartId) INCLUDE (companyId, createdBy, valueD). Isn't the rule "If it is in the WHERE, it must be a key column of the index, if it is in the SELECT, it must be an included column of the index"? This is my current understanding...
    – fmi21
    Jun 26 at 14:25
  • 1
    Not necessarily: a predicate on a unique column means you are always reading a maximum of one row, so it's not worth indexing the other columns. If there were joins then that could understanding change, as a merge join might become worthwhile. But you have no joins. Jun 26 at 14:27
  • 2
    A clarification: Internally, there is always a uniquifier column on a non-unique clustered index. It is not only added when needed - it doesn't work that way internally as we have to create that at the time that the table is created (before we know if duplicates may exist - we assume they do since you did not prevent it in the schema). Jun 27 at 0:26
  • @ConorCunninghamMSFT I know the column is defined but as far as I am aware, it doesn't actually take physical space on the data pages unless it's needed for those rows. See sqlquantumleap.com/2017/09/18/…. If you think that article is not true, please provide evidence. Jun 27 at 9:14
  • @Charlieface - I re-read the code and you are correct - I was mistaken, apologies. I had not seen the optimization path they put in for not needing it. Jun 27 at 22:24
2

I do know, it is better to test, evaluate execution plans, share them when asking questions on stackexchange, but this is the design phase so no actual data or tables exist yet.

In the design phase add a unique index for every key, and an additional non-clustered index for every foreign key not supported by a key.

Then as you develop, evaluate query execution and consider additional indexes.

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.