Is there any difference between a predicate using a literal value or a parameter value when that value will...
up vote
7
down vote
favorite
I have a query that always filters on a status, is there a performance benefit of one of these ways over the other?
(This is in the context of an ad-hoc query. The datatype of UserStatus is int)
...
AND UserStatus = 1
...
or
DECLARE @userStatus int = 1
...
AND UserStatus = @userStatus
...
(p.s. please don't talk about how params and literals are different when the values are unknown/changing, that's a different topic)
sql-server performance sql-server-2016
asked Dec 13 at 17:18
JeremyWeir
1385
New contributor
JeremyWeir is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
up vote
7
down vote
favorite
I have a query that always filters on a status, is there a performance benefit of one of these ways over the other?
(This is in the context of an ad-hoc query. The datatype of UserStatus is int)
...
AND UserStatus = 1
...
or
DECLARE @userStatus int = 1
...
AND UserStatus = @userStatus
...
(p.s. please don't talk about how params and literals are different when the values are unknown/changing, that's a different topic)
sql-server performance sql-server-2016
asked Dec 13 at 17:18
JeremyWeir
1385
New contributor
JeremyWeir is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
1
Is this in the context or a stored procedure or ad-hoc T-SQL? Could you add your SQL Server version as a tag please?
– George.Palacios
Dec 13 at 17:19
@George.Palacios Thanks, I updated the question. I'd love to know more about why that would matter between ad-hoc and sproc.
– JeremyWeir
Dec 13 at 17:21
3
Yes: brentozar.com/archive/2014/06/…
– sp_BlitzErik
Dec 13 at 17:30
@JeremyWeir it shouldn't matter wheather or not the values are changing. If you are using a local variable instead of a literal you're going to get a an estimate based on denisty * # of rows in a table instead of using the histogram in order to calculate your cardinality estimate.
– Zane
Dec 13 at 18:17
add a comment |
up vote
7
down vote
favorite
up vote
7
down vote
favorite
I have a query that always filters on a status, is there a performance benefit of one of these ways over the other?
(This is in the context of an ad-hoc query. The datatype of UserStatus is int)
...
AND UserStatus = 1
...
or
DECLARE @userStatus int = 1
...
AND UserStatus = @userStatus
...
(p.s. please don't talk about how params and literals are different when the values are unknown/changing, that's a different topic)
sql-server performance sql-server-2016
asked Dec 13 at 17:18
JeremyWeir
1385
New contributor
JeremyWeir is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
I have a query that always filters on a status, is there a performance benefit of one of these ways over the other?
(This is in the context of an ad-hoc query. The datatype of UserStatus is int)
...
AND UserStatus = 1
...
or
DECLARE @userStatus int = 1
...
AND UserStatus = @userStatus
...
(p.s. please don't talk about how params and literals are different when the values are unknown/changing, that's a different topic)
sql-server performance sql-server-2016
sql-server performance sql-server-2016
asked Dec 13 at 17:18
JeremyWeir
1385
New contributor
JeremyWeir is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked Dec 13 at 17:18
JeremyWeir
1385
New contributor
JeremyWeir is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
edited Dec 13 at 18:04
asked Dec 13 at 17:18
JeremyWeir
1385
New contributor
JeremyWeir is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked Dec 13 at 17:18
JeremyWeir
1385
asked Dec 13 at 17:18
JeremyWeir
1385
1385
New contributor
JeremyWeir is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
JeremyWeir is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
JeremyWeir is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
1
Is this in the context or a stored procedure or ad-hoc T-SQL? Could you add your SQL Server version as a tag please?
– George.Palacios
Dec 13 at 17:19
@George.Palacios Thanks, I updated the question. I'd love to know more about why that would matter between ad-hoc and sproc.
– JeremyWeir
Dec 13 at 17:21
3
Yes: brentozar.com/archive/2014/06/…
– sp_BlitzErik
Dec 13 at 17:30
@JeremyWeir it shouldn't matter wheather or not the values are changing. If you are using a local variable instead of a literal you're going to get a an estimate based on denisty * # of rows in a table instead of using the histogram in order to calculate your cardinality estimate.
– Zane
Dec 13 at 18:17
add a comment |
1
Is this in the context or a stored procedure or ad-hoc T-SQL? Could you add your SQL Server version as a tag please?
– George.Palacios
Dec 13 at 17:19
@George.Palacios Thanks, I updated the question. I'd love to know more about why that would matter between ad-hoc and sproc.
– JeremyWeir
Dec 13 at 17:21
3
Yes: brentozar.com/archive/2014/06/…
– sp_BlitzErik
Dec 13 at 17:30
@JeremyWeir it shouldn't matter wheather or not the values are changing. If you are using a local variable instead of a literal you're going to get a an estimate based on denisty * # of rows in a table instead of using the histogram in order to calculate your cardinality estimate.
– Zane
Dec 13 at 18:17
1
1
Is this in the context or a stored procedure or ad-hoc T-SQL? Could you add your SQL Server version as a tag please?
– George.Palacios
Dec 13 at 17:19
Is this in the context or a stored procedure or ad-hoc T-SQL? Could you add your SQL Server version as a tag please?
– George.Palacios
Dec 13 at 17:19
@George.Palacios Thanks, I updated the question. I'd love to know more about why that would matter between ad-hoc and sproc.
– JeremyWeir
Dec 13 at 17:21
@George.Palacios Thanks, I updated the question. I'd love to know more about why that would matter between ad-hoc and sproc.
– JeremyWeir
Dec 13 at 17:21
3
3
Yes: brentozar.com/archive/2014/06/…
– sp_BlitzErik
Dec 13 at 17:30
Yes: brentozar.com/archive/2014/06/…
– sp_BlitzErik
Dec 13 at 17:30
@JeremyWeir it shouldn't matter wheather or not the values are changing. If you are using a local variable instead of a literal you're going to get a an estimate based on denisty * # of rows in a table instead of using the histogram in order to calculate your cardinality estimate.
– Zane
Dec 13 at 18:17
@JeremyWeir it shouldn't matter wheather or not the values are changing. If you are using a local variable instead of a literal you're going to get a an estimate based on denisty * # of rows in a table instead of using the histogram in order to calculate your cardinality estimate.
– Zane
Dec 13 at 18:17
add a comment |
2 Answers
2
active
oldest
votes
up vote
11
down vote
accepted
Alright assuming that you are talking about a local variable running from a query in SSMS since it hasn't been specified otherwise. Even if you use the same value for the
AND UserStatus = @userStatus that you would use in the literal AND UserStatus = 1 you will see a difference in your execution plan due to how the cardinality estimate is generated.
When you use a literal value SQL Server will go out to the histogram for that table and see where that value of fits within the range key. The estimate gathered on that will result in one of two scenarios.
HISTOGRAM DIRECT HIT Essentially this means there is a RANGE_HI_KEY(Upper column value for any step in the histogram) value for that specific literal value in your query and therefore the estimate will match the number of EQ_ROWS(# of rows whose value equals the RANGE_HI_KEY) in the histogram. This means your estimate will be the number of rows that match that value based on the last time you updated statistics.
HISTOGRAM INTRA-STEP HIT This is when the value exists in a range between two RANGE_HI_KEY values. When your literal value is between that range it is calculated by the RANGE_ROWS(number of rows between two histogram steps), DINSTINCT_RANGE(the number of distinct values within that histogram step), and AVG_RANGE_ROWS (RANGE_ROWS/DISTINCT_RANGE_ROWS) and that will give you your estimate.
However when you run with a local variable you will no longer go to the Histogram for these values since the @Variable isn't known at runtime.
For more information on this topic I recommend reading this white paper by Joe Sack.
DENSITY VECTOR When there is no specific value to go with then SQL server instead uses a Density to be able to determine the estimated number of rows returned for that predicate. Density being 1/ the number of distinct values within that column. So then your cardinality estimation will be Density * the number of rows in the table.
So long story short no. Even if you run the same value over and over with a local variable you will not get the same results for reasons further explained in the link Eric Provided from Kendra Little.
edited Dec 14 at 14:58
hot2use
8,09452055
answered Dec 13 at 18:45
Zane
2,65321740
I added a slight correction. Feel free to roll-back if it doesn't describe what you meant.
– hot2use
Dec 14 at 15:00
Looks good Man.
– Zane
Dec 14 at 15:23
add a comment |
up vote
7
down vote
A literal value is known to the optimizer (so it can estimate selectivity based on that value).
A parameter value (stored procedure, function) is sniffed at execution time. A subsequent execution might have some other value, for which the previously compiled plan might not be optimal.
For a variable, the value is not known to the optimizer. It might be able to look at density (on average we have this many rows for a certain value), or it uses hard-wired estimates (like for instance > result in 10% selectivity, or whatever those ward-wired percentages might be).
answered Dec 13 at 17:53
Tibor Karaszi
1,4466
So if the value will never change, does a literal act differently than a param?
– JeremyWeir
Dec 13 at 18:02
1
Since the param will be sniffed it will behave the same as the literal, assuming you always have the same value. But a variable is a totally different thing!
– Tibor Karaszi
Dec 13 at 18:12
Got it, so there's a difference between local variable used as a parameterized value and a sproc parameter used as a parameterized value?
– JeremyWeir
Dec 13 at 18:17
3
@JeremyWeir: Yes, there is, that's why Erik suggested reading Brent Ozar's article above.
– Andriy M
Dec 13 at 19:18
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',
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
});
}
});
JeremyWeir is a new contributor. Be nice, and check out our Code of Conduct.
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%2f224913%2fis-there-any-difference-between-a-predicate-using-a-literal-value-or-a-parameter%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
11
down vote
accepted
Alright assuming that you are talking about a local variable running from a query in SSMS since it hasn't been specified otherwise. Even if you use the same value for the
AND UserStatus = @userStatus that you would use in the literal AND UserStatus = 1 you will see a difference in your execution plan due to how the cardinality estimate is generated.
When you use a literal value SQL Server will go out to the histogram for that table and see where that value of fits within the range key. The estimate gathered on that will result in one of two scenarios.
HISTOGRAM DIRECT HIT Essentially this means there is a RANGE_HI_KEY(Upper column value for any step in the histogram) value for that specific literal value in your query and therefore the estimate will match the number of EQ_ROWS(# of rows whose value equals the RANGE_HI_KEY) in the histogram. This means your estimate will be the number of rows that match that value based on the last time you updated statistics.
HISTOGRAM INTRA-STEP HIT This is when the value exists in a range between two RANGE_HI_KEY values. When your literal value is between that range it is calculated by the RANGE_ROWS(number of rows between two histogram steps), DINSTINCT_RANGE(the number of distinct values within that histogram step), and AVG_RANGE_ROWS (RANGE_ROWS/DISTINCT_RANGE_ROWS) and that will give you your estimate.
However when you run with a local variable you will no longer go to the Histogram for these values since the @Variable isn't known at runtime.
For more information on this topic I recommend reading this white paper by Joe Sack.
DENSITY VECTOR When there is no specific value to go with then SQL server instead uses a Density to be able to determine the estimated number of rows returned for that predicate. Density being 1/ the number of distinct values within that column. So then your cardinality estimation will be Density * the number of rows in the table.
So long story short no. Even if you run the same value over and over with a local variable you will not get the same results for reasons further explained in the link Eric Provided from Kendra Little.
edited Dec 14 at 14:58
hot2use
8,09452055
answered Dec 13 at 18:45
Zane
2,65321740
I added a slight correction. Feel free to roll-back if it doesn't describe what you meant.
– hot2use
Dec 14 at 15:00
Looks good Man.
– Zane
Dec 14 at 15:23
add a comment |
up vote
11
down vote
accepted
Alright assuming that you are talking about a local variable running from a query in SSMS since it hasn't been specified otherwise. Even if you use the same value for the
AND UserStatus = @userStatus that you would use in the literal AND UserStatus = 1 you will see a difference in your execution plan due to how the cardinality estimate is generated.
When you use a literal value SQL Server will go out to the histogram for that table and see where that value of fits within the range key. The estimate gathered on that will result in one of two scenarios.
HISTOGRAM DIRECT HIT Essentially this means there is a RANGE_HI_KEY(Upper column value for any step in the histogram) value for that specific literal value in your query and therefore the estimate will match the number of EQ_ROWS(# of rows whose value equals the RANGE_HI_KEY) in the histogram. This means your estimate will be the number of rows that match that value based on the last time you updated statistics.
HISTOGRAM INTRA-STEP HIT This is when the value exists in a range between two RANGE_HI_KEY values. When your literal value is between that range it is calculated by the RANGE_ROWS(number of rows between two histogram steps), DINSTINCT_RANGE(the number of distinct values within that histogram step), and AVG_RANGE_ROWS (RANGE_ROWS/DISTINCT_RANGE_ROWS) and that will give you your estimate.
However when you run with a local variable you will no longer go to the Histogram for these values since the @Variable isn't known at runtime.
For more information on this topic I recommend reading this white paper by Joe Sack.
DENSITY VECTOR When there is no specific value to go with then SQL server instead uses a Density to be able to determine the estimated number of rows returned for that predicate. Density being 1/ the number of distinct values within that column. So then your cardinality estimation will be Density * the number of rows in the table.
So long story short no. Even if you run the same value over and over with a local variable you will not get the same results for reasons further explained in the link Eric Provided from Kendra Little.
edited Dec 14 at 14:58
hot2use
8,09452055
answered Dec 13 at 18:45
Zane
2,65321740
I added a slight correction. Feel free to roll-back if it doesn't describe what you meant.
– hot2use
Dec 14 at 15:00
Looks good Man.
– Zane
Dec 14 at 15:23
add a comment |
up vote
11
down vote
accepted
up vote
11
down vote
accepted
Alright assuming that you are talking about a local variable running from a query in SSMS since it hasn't been specified otherwise. Even if you use the same value for the
AND UserStatus = @userStatus that you would use in the literal AND UserStatus = 1 you will see a difference in your execution plan due to how the cardinality estimate is generated.
When you use a literal value SQL Server will go out to the histogram for that table and see where that value of fits within the range key. The estimate gathered on that will result in one of two scenarios.
HISTOGRAM DIRECT HIT Essentially this means there is a RANGE_HI_KEY(Upper column value for any step in the histogram) value for that specific literal value in your query and therefore the estimate will match the number of EQ_ROWS(# of rows whose value equals the RANGE_HI_KEY) in the histogram. This means your estimate will be the number of rows that match that value based on the last time you updated statistics.
HISTOGRAM INTRA-STEP HIT This is when the value exists in a range between two RANGE_HI_KEY values. When your literal value is between that range it is calculated by the RANGE_ROWS(number of rows between two histogram steps), DINSTINCT_RANGE(the number of distinct values within that histogram step), and AVG_RANGE_ROWS (RANGE_ROWS/DISTINCT_RANGE_ROWS) and that will give you your estimate.
However when you run with a local variable you will no longer go to the Histogram for these values since the @Variable isn't known at runtime.
For more information on this topic I recommend reading this white paper by Joe Sack.
DENSITY VECTOR When there is no specific value to go with then SQL server instead uses a Density to be able to determine the estimated number of rows returned for that predicate. Density being 1/ the number of distinct values within that column. So then your cardinality estimation will be Density * the number of rows in the table.
So long story short no. Even if you run the same value over and over with a local variable you will not get the same results for reasons further explained in the link Eric Provided from Kendra Little.
edited Dec 14 at 14:58
hot2use
8,09452055
answered Dec 13 at 18:45
Zane
2,65321740
Alright assuming that you are talking about a local variable running from a query in SSMS since it hasn't been specified otherwise. Even if you use the same value for the
AND UserStatus = @userStatus that you would use in the literal AND UserStatus = 1 you will see a difference in your execution plan due to how the cardinality estimate is generated.
When you use a literal value SQL Server will go out to the histogram for that table and see where that value of fits within the range key. The estimate gathered on that will result in one of two scenarios.
HISTOGRAM DIRECT HIT Essentially this means there is a RANGE_HI_KEY(Upper column value for any step in the histogram) value for that specific literal value in your query and therefore the estimate will match the number of EQ_ROWS(# of rows whose value equals the RANGE_HI_KEY) in the histogram. This means your estimate will be the number of rows that match that value based on the last time you updated statistics.
HISTOGRAM INTRA-STEP HIT This is when the value exists in a range between two RANGE_HI_KEY values. When your literal value is between that range it is calculated by the RANGE_ROWS(number of rows between two histogram steps), DINSTINCT_RANGE(the number of distinct values within that histogram step), and AVG_RANGE_ROWS (RANGE_ROWS/DISTINCT_RANGE_ROWS) and that will give you your estimate.
However when you run with a local variable you will no longer go to the Histogram for these values since the @Variable isn't known at runtime.
For more information on this topic I recommend reading this white paper by Joe Sack.
DENSITY VECTOR When there is no specific value to go with then SQL server instead uses a Density to be able to determine the estimated number of rows returned for that predicate. Density being 1/ the number of distinct values within that column. So then your cardinality estimation will be Density * the number of rows in the table.
So long story short no. Even if you run the same value over and over with a local variable you will not get the same results for reasons further explained in the link Eric Provided from Kendra Little.
edited Dec 14 at 14:58
hot2use
8,09452055
answered Dec 13 at 18:45
Zane
2,65321740
edited Dec 14 at 14:58
hot2use
8,09452055
edited Dec 14 at 14:58
hot2use
8,09452055
edited Dec 14 at 14:58
hot2use
8,09452055
8,09452055
answered Dec 13 at 18:45
Zane
2,65321740
answered Dec 13 at 18:45
Zane
2,65321740
answered Dec 13 at 18:45
Zane
2,65321740
2,65321740
I added a slight correction. Feel free to roll-back if it doesn't describe what you meant.
– hot2use
Dec 14 at 15:00
Looks good Man.
– Zane
Dec 14 at 15:23
add a comment |
I added a slight correction. Feel free to roll-back if it doesn't describe what you meant.
– hot2use
Dec 14 at 15:00
Looks good Man.
– Zane
Dec 14 at 15:23
I added a slight correction. Feel free to roll-back if it doesn't describe what you meant.
– hot2use
Dec 14 at 15:00
I added a slight correction. Feel free to roll-back if it doesn't describe what you meant.
– hot2use
Dec 14 at 15:00
Looks good Man.
– Zane
Dec 14 at 15:23
Looks good Man.
– Zane
Dec 14 at 15:23
add a comment |
up vote
7
down vote
A literal value is known to the optimizer (so it can estimate selectivity based on that value).
A parameter value (stored procedure, function) is sniffed at execution time. A subsequent execution might have some other value, for which the previously compiled plan might not be optimal.
For a variable, the value is not known to the optimizer. It might be able to look at density (on average we have this many rows for a certain value), or it uses hard-wired estimates (like for instance > result in 10% selectivity, or whatever those ward-wired percentages might be).
answered Dec 13 at 17:53
Tibor Karaszi
1,4466
So if the value will never change, does a literal act differently than a param?
– JeremyWeir
Dec 13 at 18:02
1
Since the param will be sniffed it will behave the same as the literal, assuming you always have the same value. But a variable is a totally different thing!
– Tibor Karaszi
Dec 13 at 18:12
Got it, so there's a difference between local variable used as a parameterized value and a sproc parameter used as a parameterized value?
– JeremyWeir
Dec 13 at 18:17
3
@JeremyWeir: Yes, there is, that's why Erik suggested reading Brent Ozar's article above.
– Andriy M
Dec 13 at 19:18
add a comment |
up vote
7
down vote
A literal value is known to the optimizer (so it can estimate selectivity based on that value).
A parameter value (stored procedure, function) is sniffed at execution time. A subsequent execution might have some other value, for which the previously compiled plan might not be optimal.
For a variable, the value is not known to the optimizer. It might be able to look at density (on average we have this many rows for a certain value), or it uses hard-wired estimates (like for instance > result in 10% selectivity, or whatever those ward-wired percentages might be).
answered Dec 13 at 17:53
Tibor Karaszi
1,4466
So if the value will never change, does a literal act differently than a param?
– JeremyWeir
Dec 13 at 18:02
1
Since the param will be sniffed it will behave the same as the literal, assuming you always have the same value. But a variable is a totally different thing!
– Tibor Karaszi
Dec 13 at 18:12
Got it, so there's a difference between local variable used as a parameterized value and a sproc parameter used as a parameterized value?
– JeremyWeir
Dec 13 at 18:17
3
@JeremyWeir: Yes, there is, that's why Erik suggested reading Brent Ozar's article above.
– Andriy M
Dec 13 at 19:18
add a comment |
up vote
7
down vote
up vote
7
down vote
A literal value is known to the optimizer (so it can estimate selectivity based on that value).
A parameter value (stored procedure, function) is sniffed at execution time. A subsequent execution might have some other value, for which the previously compiled plan might not be optimal.
For a variable, the value is not known to the optimizer. It might be able to look at density (on average we have this many rows for a certain value), or it uses hard-wired estimates (like for instance > result in 10% selectivity, or whatever those ward-wired percentages might be).
answered Dec 13 at 17:53
Tibor Karaszi
1,4466
A literal value is known to the optimizer (so it can estimate selectivity based on that value).
A parameter value (stored procedure, function) is sniffed at execution time. A subsequent execution might have some other value, for which the previously compiled plan might not be optimal.
For a variable, the value is not known to the optimizer. It might be able to look at density (on average we have this many rows for a certain value), or it uses hard-wired estimates (like for instance > result in 10% selectivity, or whatever those ward-wired percentages might be).
answered Dec 13 at 17:53
Tibor Karaszi
1,4466
answered Dec 13 at 17:53
Tibor Karaszi
1,4466
answered Dec 13 at 17:53
Tibor Karaszi
1,4466
answered Dec 13 at 17:53
Tibor Karaszi
1,4466
1,4466
So if the value will never change, does a literal act differently than a param?
– JeremyWeir
Dec 13 at 18:02
1
Since the param will be sniffed it will behave the same as the literal, assuming you always have the same value. But a variable is a totally different thing!
– Tibor Karaszi
Dec 13 at 18:12
Got it, so there's a difference between local variable used as a parameterized value and a sproc parameter used as a parameterized value?
– JeremyWeir
Dec 13 at 18:17
3
@JeremyWeir: Yes, there is, that's why Erik suggested reading Brent Ozar's article above.
– Andriy M
Dec 13 at 19:18
add a comment |
So if the value will never change, does a literal act differently than a param?
– JeremyWeir
Dec 13 at 18:02
1
Since the param will be sniffed it will behave the same as the literal, assuming you always have the same value. But a variable is a totally different thing!
– Tibor Karaszi
Dec 13 at 18:12
Got it, so there's a difference between local variable used as a parameterized value and a sproc parameter used as a parameterized value?
– JeremyWeir
Dec 13 at 18:17
3
@JeremyWeir: Yes, there is, that's why Erik suggested reading Brent Ozar's article above.
– Andriy M
Dec 13 at 19:18
So if the value will never change, does a literal act differently than a param?
– JeremyWeir
Dec 13 at 18:02
So if the value will never change, does a literal act differently than a param?
– JeremyWeir
Dec 13 at 18:02
1
1
Since the param will be sniffed it will behave the same as the literal, assuming you always have the same value. But a variable is a totally different thing!
– Tibor Karaszi
Dec 13 at 18:12
Since the param will be sniffed it will behave the same as the literal, assuming you always have the same value. But a variable is a totally different thing!
– Tibor Karaszi
Dec 13 at 18:12
Got it, so there's a difference between local variable used as a parameterized value and a sproc parameter used as a parameterized value?
– JeremyWeir
Dec 13 at 18:17
Got it, so there's a difference between local variable used as a parameterized value and a sproc parameter used as a parameterized value?
– JeremyWeir
Dec 13 at 18:17
3
3
@JeremyWeir: Yes, there is, that's why Erik suggested reading Brent Ozar's article above.
– Andriy M
Dec 13 at 19:18
@JeremyWeir: Yes, there is, that's why Erik suggested reading Brent Ozar's article above.
– Andriy M
Dec 13 at 19:18
add a comment |
JeremyWeir is a new contributor. Be nice, and check out our Code of Conduct.
JeremyWeir is a new contributor. Be nice, and check out our Code of Conduct.
JeremyWeir is a new contributor. Be nice, and check out our Code of Conduct.
JeremyWeir is a new contributor. Be nice, and check out our Code of Conduct.
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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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%2f224913%2fis-there-any-difference-between-a-predicate-using-a-literal-value-or-a-parameter%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
1
Is this in the context or a stored procedure or ad-hoc T-SQL? Could you add your SQL Server version as a tag please?
– George.Palacios
Dec 13 at 17:19
@George.Palacios Thanks, I updated the question. I'd love to know more about why that would matter between ad-hoc and sproc.
– JeremyWeir
Dec 13 at 17:21
3
Yes: brentozar.com/archive/2014/06/…
– sp_BlitzErik
Dec 13 at 17:30
@JeremyWeir it shouldn't matter wheather or not the values are changing. If you are using a local variable instead of a literal you're going to get a an estimate based on denisty * # of rows in a table instead of using the histogram in order to calculate your cardinality estimate.
– Zane
Dec 13 at 18:17