4

I have table that is more or less as follows:

table Sample (
    location bigint,
    device bigint,
    timestamp datetime,
    type bigint,
    value bigint,
    )

For performance reasons we also have one mighty index with same columns in identical order, except that value is included rather than used in index.

This works as expected, except that AFAIU data is kept twice in database - once in the table and once in an index.

Question: is there some way to, conceptually, keep data only in the index? I believe that would require keeping table organized in the same way index is organized, effectively removing need for the index. I tried to create composed primary key that would emulate aforementioned index, but it boiled down to automatic creation of identical index.

8

What you ask for is what we call a "clustered index" in the SQL Server world. The clustered index is the data. I.e., the b-tree for the index is you actual table data.

A clustered index don't have the concept of included columns since all columns are in the clustered index - the clustered index being the data. So you have the key columns (what you use to "drive the query", like the WHERE clause, so to speak); and the rest of the columns are in the leaf.

You can think of the non-key columns in a clustered index just like included columns in a non-clustered index.

Don't confuse the PK with the clustered index. You decide whether the PK should come with a clustered index or a non-clustered index.

You might have a better candidate for the clustered index than the PK. Fine! Just make the PK non-clustered and create the clustered index on that better candidate.

3
  • Oooh, so if I have clustered index with all columns there is no data "in the table"? That is there is no extra storage used? If this is so then it would be absolutely ideal. I believe I tried creating non-clustered index for this data and storage used was almost twice as much, but maybe I did something wrong. I'll check that again. Thanks for an answer. – Jędrzej Dudkiewicz Jan 8 at 9:19
  • Yes, correct. The clustered indes is your data. No extra storage. – Tibor Karaszi Jan 8 at 9:40
  • 1
    If you need supplementary (non-clustered) indexes, a wide clustering key can be problematical in terms of space use. A fuller description became too long for a comment, so I've added it as an answer below. – David Spillett Jan 8 at 10:29
3

As Tibor describes in his answer, what you are looking for is a clustered index with a wide key. If all you need is this index then that will do the job exactly as you want with no extra storage needed.

There is a space consuming danger with wide clustering keys though: any supplementary non-clustered indexes will include the clustering key. So in this example if you later add an index on only value you effectively have an index containing everything again (i.e. your original concern). Off the top of my head I'm not sure if this would behave as an index on value including location, device, timestamp, type or an index on value, location, device, timestamp, type. If you also added a non-clustered index on device this would include everything except value (unless you also have value in your clustering key in which case it'll include everything). Using these big non-clustered indexes would be efficient though (no extra row lookups needed) assuming you have enough memory for them not be a causes of IO thrashing.

3
  • Thanks for an explanation. I have a lot of memory, but still much more data :) But this index covers 90% of our use cases so we should be ok. – Jędrzej Dudkiewicz Jan 8 at 11:49
  • @DavidSpillett Great point about the nonclustered indexes and their space concerns when designing a wide clustered index. Also from my experience, I think your example with adding a nonclustered index on value would result in your second guess of behaving as if the index was on value, location, device, timestamp, type. (I'm not exactly sure how this works under the hood, but I have seen my queries predicates leverage the fields of the clustered index reference in the nonclustered index the query plan was using, so I'd have to assume it's more than just treated as an INCLUDE). – J.D. Jan 8 at 13:06
  • 1
    @J.D. - I've taken a note and plan to have a play to test what the engine actually does in these cases. When/if I get around to that, or stumble upon definitive information one way or the other elsewhere, I'll update the answer. – David Spillett Jan 8 at 15:32
-1

Just use a clustered index on all the key columns in the table

2
  • 1
    can you please elaborate your idea further – nbk Jan 8 at 9:22
  • 1
    Tibor explains it much better – Stephen Morris - Mo64 Jan 8 at 9:25

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.