6 added 778 characters in body
source | link

To get a visual of that, let's look at the leaf page of Index3. You can do this with the following:

dbcc ind('TestDB', 'FooBar', -1);
go
-- Index3 (index_id = 4) leaf page PID = 288
dbcc traceon(3604);
go
dbcc page('TestDB', 1, 288, 3);
go

You will get similar output like the following:

enter image description here

To get a visual of that, let's look at the leaf page of Index3. You can do this with the following:

dbcc ind('TestDB', 'FooBar', -1);
go
-- Index3 (index_id = 4) leaf page PID = 288
dbcc traceon(3604);
go
dbcc page('TestDB', 1, 288, 3);
go

You will get similar output like the following:

enter image description here

5 added 778 characters in body
source | link

It depends, and the reason behind that is because the nonclustered index leaf pages contain the clustered index key (Id) in this case. Take a look at the following example:

enter image description here

Now take a look at what happens if your query does in fact look like the following (again, table hint there just to force Index3):

select
    Id,
    FooId,
    BarId,
    QuxId
from FooBar with(index(Index3))
where FooId = 4
and BarId = 5
order by Id;

You will again have that Sort operation. Now if your index looked like the following:

create index Index3
on FooBar(FooId, BarId, Id, QuxId)
with(drop_existing = on);
go

By having Id as the third key column in the index structure, you will see the benefit of having it already ordered by Id when the Seek Predicates are on FooId and BarId. Here is proof with that altered index:

enter image description here

It depends, and the reason behind that is because the nonclustered index leaf pages contain the clustered index key (Id) in this case. Take a look at the following example:

enter image description here

It depends, and the reason behind that is because the nonclustered index leaf pages contain the clustered index key (Id). Take a look at the following example:

enter image description here

Now take a look at what happens if your query does in fact look like the following (again, table hint there just to force Index3):

select
    Id,
    FooId,
    BarId,
    QuxId
from FooBar with(index(Index3))
where FooId = 4
and BarId = 5
order by Id;

You will again have that Sort operation. Now if your index looked like the following:

create index Index3
on FooBar(FooId, BarId, Id, QuxId)
with(drop_existing = on);
go

By having Id as the third key column in the index structure, you will see the benefit of having it already ordered by Id when the Seek Predicates are on FooId and BarId. Here is proof with that altered index:

enter image description here

4 deleted 20 characters in body
source | link

No It depends, and the reason behind that is because the nonclustered index leaf pages contain the clustered index key (Id) in this case. Take a look at the following example:

No It depends, and the reason behind that is because the nonclustered index leaf pages contain the clustered index key (Id) in this case. Take a look at the following example:

It depends, and the reason behind that is because the nonclustered index leaf pages contain the clustered index key (Id) in this case. Take a look at the following example:

3 added 28 characters in body
source | link
2 added 1307 characters in body
source | link
1
source | link