Enlighten me about INCLUDE on indexes
Well, After reading Erik Darling's post ( https://www.brentozar.com/archive/2015/12/filtered-indexes-just-add-includes/ ) i'm really curious and sad about how I use INCLUDES:
he's giving some examples and etc about INCLUDE.
But the point is:
This is the query:
SELECT Id,DisplayName
FROM Users
WHERE Reputation > 400000;
What I would do is:
CREATE INDEX ix_bla on USERS (Reputation)
INCLUDE (Id, DisplayName)
I thought we should use what's in the WHERE on the index, and use INCLUDE on those fields in the SELECT.
But Erik did this:
CREATE UNIQUE NONCLUSTERED INDEX Users_400k_Club
ON dbo.Users ( DisplayName, Id )
INCLUDE ( Reputation )
WHERE Reputation > 400000
WITH ( DROP_EXISTING = ON );
the problem here is not the filtering index. My question is why he's using DISPLAYNAME and ID in the index, and REPUTATION in the include clause?
And just for a simple test, running this query for a test in a table with millions of rows
:
select COL1
,COL2
,COL3
from MyTable
where COL2 > 4513516
SQL tells me to create this index:
CREATE NONCLUSTERED INDEX ix_nada
ON MyTable
(
[COL2]
)
INCLUDE
(
[COL1],
[COL3]
)
GO
sql-server sql-server-2008-r2 index-tuning
edited 34 mins ago
Paul White♦
49.4k14260414
asked 12 hours ago
Racer SQLRacer SQL
2,97142458
add a comment |
Well, After reading Erik Darling's post ( https://www.brentozar.com/archive/2015/12/filtered-indexes-just-add-includes/ ) i'm really curious and sad about how I use INCLUDES:
he's giving some examples and etc about INCLUDE.
But the point is:
This is the query:
SELECT Id,DisplayName
FROM Users
WHERE Reputation > 400000;
What I would do is:
CREATE INDEX ix_bla on USERS (Reputation)
INCLUDE (Id, DisplayName)
I thought we should use what's in the WHERE on the index, and use INCLUDE on those fields in the SELECT.
But Erik did this:
CREATE UNIQUE NONCLUSTERED INDEX Users_400k_Club
ON dbo.Users ( DisplayName, Id )
INCLUDE ( Reputation )
WHERE Reputation > 400000
WITH ( DROP_EXISTING = ON );
the problem here is not the filtering index. My question is why he's using DISPLAYNAME and ID in the index, and REPUTATION in the include clause?
And just for a simple test, running this query for a test in a table with millions of rows
:
select COL1
,COL2
,COL3
from MyTable
where COL2 > 4513516
SQL tells me to create this index:
CREATE NONCLUSTERED INDEX ix_nada
ON MyTable
(
[COL2]
)
INCLUDE
(
[COL1],
[COL3]
)
GO
sql-server sql-server-2008-r2 index-tuning
edited 34 mins ago
Paul White♦
49.4k14260414
asked 12 hours ago
Racer SQLRacer SQL
2,97142458
2
Related: Should the filtering column(s) always be in the keys / includes?
– jadarnel27
11 hours ago
add a comment |
Well, After reading Erik Darling's post ( https://www.brentozar.com/archive/2015/12/filtered-indexes-just-add-includes/ ) i'm really curious and sad about how I use INCLUDES:
he's giving some examples and etc about INCLUDE.
But the point is:
This is the query:
SELECT Id,DisplayName
FROM Users
WHERE Reputation > 400000;
What I would do is:
CREATE INDEX ix_bla on USERS (Reputation)
INCLUDE (Id, DisplayName)
I thought we should use what's in the WHERE on the index, and use INCLUDE on those fields in the SELECT.
But Erik did this:
CREATE UNIQUE NONCLUSTERED INDEX Users_400k_Club
ON dbo.Users ( DisplayName, Id )
INCLUDE ( Reputation )
WHERE Reputation > 400000
WITH ( DROP_EXISTING = ON );
the problem here is not the filtering index. My question is why he's using DISPLAYNAME and ID in the index, and REPUTATION in the include clause?
And just for a simple test, running this query for a test in a table with millions of rows
:
select COL1
,COL2
,COL3
from MyTable
where COL2 > 4513516
SQL tells me to create this index:
CREATE NONCLUSTERED INDEX ix_nada
ON MyTable
(
[COL2]
)
INCLUDE
(
[COL1],
[COL3]
)
GO
sql-server sql-server-2008-r2 index-tuning
edited 34 mins ago
Paul White♦
49.4k14260414
asked 12 hours ago
Racer SQLRacer SQL
2,97142458
Well, After reading Erik Darling's post ( https://www.brentozar.com/archive/2015/12/filtered-indexes-just-add-includes/ ) i'm really curious and sad about how I use INCLUDES:
he's giving some examples and etc about INCLUDE.
But the point is:
This is the query:
SELECT Id,DisplayName
FROM Users
WHERE Reputation > 400000;
What I would do is:
CREATE INDEX ix_bla on USERS (Reputation)
INCLUDE (Id, DisplayName)
I thought we should use what's in the WHERE on the index, and use INCLUDE on those fields in the SELECT.
But Erik did this:
CREATE UNIQUE NONCLUSTERED INDEX Users_400k_Club
ON dbo.Users ( DisplayName, Id )
INCLUDE ( Reputation )
WHERE Reputation > 400000
WITH ( DROP_EXISTING = ON );
the problem here is not the filtering index. My question is why he's using DISPLAYNAME and ID in the index, and REPUTATION in the include clause?
And just for a simple test, running this query for a test in a table with millions of rows
:
select COL1
,COL2
,COL3
from MyTable
where COL2 > 4513516
SQL tells me to create this index:
CREATE NONCLUSTERED INDEX ix_nada
ON MyTable
(
[COL2]
)
INCLUDE
(
[COL1],
[COL3]
)
GO
sql-server sql-server-2008-r2 index-tuning
sql-server sql-server-2008-r2 index-tuning
edited 34 mins ago
Paul White♦
49.4k14260414
asked 12 hours ago
Racer SQLRacer SQL
2,97142458
edited 34 mins ago
Paul White♦
49.4k14260414
asked 12 hours ago
Racer SQLRacer SQL
2,97142458
edited 34 mins ago
Paul White♦
49.4k14260414
edited 34 mins ago
Paul White♦
49.4k14260414
edited 34 mins ago
Paul White♦
49.4k14260414
49.4k14260414
asked 12 hours ago
Racer SQLRacer SQL
2,97142458
asked 12 hours ago
Racer SQLRacer SQL
2,97142458
asked 12 hours ago
Racer SQLRacer SQL
2,97142458
2,97142458
2
Related: Should the filtering column(s) always be in the keys / includes?
– jadarnel27
11 hours ago
add a comment |
2
Related: Should the filtering column(s) always be in the keys / includes?
– jadarnel27
11 hours ago
2
2
Related: Should the filtering column(s) always be in the keys / includes?
– jadarnel27
11 hours ago
Related: Should the filtering column(s) always be in the keys / includes?
– jadarnel27
11 hours ago
add a comment |
1 Answer
1
active
oldest
votes
Erik's intent wasn't to show that the index leading with DisplayName was a GOOD index for that query.
His point was just to show that it WOULD use that index. It's doing a scan rather than a seek, which isn't great in this scenario.
If you really wanted to create a perfect index for the query in the blog post, you'd create it on Reputation first as the key (since the WHERE clause is on Reputation.)
answered 11 hours ago
Brent OzarBrent Ozar
34.1k19101229
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f227511%2fenlighten-me-about-include-on-indexes%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Erik's intent wasn't to show that the index leading with DisplayName was a GOOD index for that query.
His point was just to show that it WOULD use that index. It's doing a scan rather than a seek, which isn't great in this scenario.
If you really wanted to create a perfect index for the query in the blog post, you'd create it on Reputation first as the key (since the WHERE clause is on Reputation.)
answered 11 hours ago
Brent OzarBrent Ozar
34.1k19101229
add a comment |
Erik's intent wasn't to show that the index leading with DisplayName was a GOOD index for that query.
His point was just to show that it WOULD use that index. It's doing a scan rather than a seek, which isn't great in this scenario.
If you really wanted to create a perfect index for the query in the blog post, you'd create it on Reputation first as the key (since the WHERE clause is on Reputation.)
answered 11 hours ago
Brent OzarBrent Ozar
34.1k19101229
add a comment |
Erik's intent wasn't to show that the index leading with DisplayName was a GOOD index for that query.
His point was just to show that it WOULD use that index. It's doing a scan rather than a seek, which isn't great in this scenario.
If you really wanted to create a perfect index for the query in the blog post, you'd create it on Reputation first as the key (since the WHERE clause is on Reputation.)
answered 11 hours ago
Brent OzarBrent Ozar
34.1k19101229
Erik's intent wasn't to show that the index leading with DisplayName was a GOOD index for that query.
His point was just to show that it WOULD use that index. It's doing a scan rather than a seek, which isn't great in this scenario.
If you really wanted to create a perfect index for the query in the blog post, you'd create it on Reputation first as the key (since the WHERE clause is on Reputation.)
answered 11 hours ago
Brent OzarBrent Ozar
34.1k19101229
answered 11 hours ago
Brent OzarBrent Ozar
34.1k19101229
answered 11 hours ago
Brent OzarBrent Ozar
34.1k19101229
answered 11 hours ago
Brent OzarBrent Ozar
34.1k19101229
34.1k19101229
add a comment |
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f227511%2fenlighten-me-about-include-on-indexes%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
2
Related: Should the filtering column(s) always be in the keys / includes?
– jadarnel27
11 hours ago