0

I have multiple-field index on columns a,b,c,d and another unique index on column id. Is my query like below after adding id to ORDER BY slows large or i don't need to worry ? Query plan shows that sort get only 1%.

SELECT *
FROM
  (SELECT *,
          row_number() over (
                             ORDER BY a,b,c,d,id ) AS ROW
   FROM table1
   WHERE /* ... */) a
WHERE ROW > 0
  AND ROW <= 20000;

enter image description here

2
  • 1
    It is going to depend on the number of rows, and a few other factors such as if that id column is your clustering key for that table. You should show the entire query plan (brentozar.com/pastetheplan is very useful for that) as that sort step may not be the only effect of the change. Apr 3, 2020 at 10:54
  • index id is unique, multi-field index is not unique, table table1 has about 5-10 millions records. all indexes are not clustered and created by CREATE [UNIQUE] INDEX idx_name ...
    – marioosh
    Apr 3, 2020 at 12:35

1 Answer 1

1

If ID is the clustered index, on SQL Server this index will have ID added as the last index key.

For a non-unique non-clustered index on a clustered index table the clustered index keys that aren't explicitly listed as non-clustered index keys will be added as additional non-clustered index keys. And the non-clustered index will actually be stored as a unique index. EG your index on (a,b,c,d) is implemented as a unique index on (a,b,c,d,id).

If the non-clustered index is unique, the clustered index keys will be present on the leaf level of the index, but will not be index keys.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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