|Index3. Didn't actually try it, just based it on documentation.
Creates a composite index, based on those three columns, sorted first by id0, and within that group on id1, and within that group on id2.
Super Lloyd wrote:create index index2 on Table (id0, id1, id2)
Creates a index on id2, but adds the (values of) columns id0 and id1 to the index for quick access.
Super Lloyd wrote:create index Index1 on Table (id2) include (id0, id1)
You mean "faster"? Do you mean faster read, or faster write? From what I see, they'd both need to write three fields when writing, since the index needs be updated. They'd both supply the three fields from the index (without accessing the table) when reading. The bigger difference is in the first field of the index, id2 in one, id0 in the other. Both queries seem to cover the query, but the docs point to making a composite of all three fields;
Super Lloyd wrote:What would be the better index
Redesign nonclustered indexes with a large index key size so that only columns used for searching and lookups are key columns. Make all other columns that cover the query into nonkey columns. In this way, you will have all columns needed to cover the query, but the index key itself is small and efficient.Which field comes first in that list, is also on MSDN;
List the columns to be included in the composite index, in sort-priority orderSoo.. option 2? Maybe option 3, with (id0, id1, id2); you are first looking up on the first two fields, than ordering on id2. That'd be my guess, given the example query and the docs. You could of course create a table and actually test that; MSSQLMS would give you an execution plan with timings.
Why is there an identity field? Wouldn't id0 and id1 simply be your primary key? That's what you are using to locate a unique value in the set; if you're not actively using the identity-field, then inserts would benefit from removing it. If you make id0 and id1 your primary key, then the table will have a clustered index on those fields, meaning the table is physically sorted on those fields. For large tables, that would be actually preffered;
The preferred way to build indexes on large tables is to start with the clustered index and then build any nonclustered indexesSo docs are hinting that (for large sets) a clustered index is preferred and autmatically created when defining a PK; but then the ids' can't be
NULL. If those ids' represent categories, you may even want to go for a filtered index.
I hope for you that someone posts an answer that simply says the first or the second, without all these details
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.