Mixing
Is there any limit for IN results in SQL Server?
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ margin-bottom:0;
}
Is there any limit for the content that the IN filter can handle? For example:
SELECT Name
FROM People
WHERE Job IN (All the values goes here)
Microsoft docs for IN says:
"Explicitly including an extremely large number of values (many
thousands of values separated by commas) within the parentheses, in an
IN clause can consume resources and return errors 8623 or 8632. To
work around this problem, store the items in the IN list in a table,
and use a SELECT subquery within an IN clause."
but is there any exact or approximate number for
many thousands of values
sql-server t-sql
asked Feb 1 at 15:38
UnnamedUnnamed
283
add a comment |
Is there any limit for the content that the IN filter can handle? For example:
SELECT Name
FROM People
WHERE Job IN (All the values goes here)
Microsoft docs for IN says:
"Explicitly including an extremely large number of values (many
thousands of values separated by commas) within the parentheses, in an
IN clause can consume resources and return errors 8623 or 8632. To
work around this problem, store the items in the IN list in a table,
and use a SELECT subquery within an IN clause."
but is there any exact or approximate number for
many thousands of values
sql-server t-sql
asked Feb 1 at 15:38
UnnamedUnnamed
283
I had an issue last year where the in clause had more than 30,000 values, it was a query generated dinamically from the application. But seems to be an issue not with the values but something else, as later we hit the error with more than 20,000 values, we ended asking the developers to use the select subquery instead of using all the values.
– dbamex
Feb 1 at 15:46
I though the select query would be translated to the actual values separated by commas. but I think I´m wrong, probably it does some other "behind scenes" stuff to optimized that.
– Unnamed
Feb 1 at 15:49
1
Subquery will translate to a join typically, not the materialization of a set of comma-separated values.
– Aaron Bertrand♦
Feb 1 at 15:53
add a comment |
Is there any limit for the content that the IN filter can handle? For example:
SELECT Name
FROM People
WHERE Job IN (All the values goes here)
Microsoft docs for IN says:
"Explicitly including an extremely large number of values (many
thousands of values separated by commas) within the parentheses, in an
IN clause can consume resources and return errors 8623 or 8632. To
work around this problem, store the items in the IN list in a table,
and use a SELECT subquery within an IN clause."
but is there any exact or approximate number for
many thousands of values
sql-server t-sql
asked Feb 1 at 15:38
UnnamedUnnamed
283
Is there any limit for the content that the IN filter can handle? For example:
SELECT Name
FROM People
WHERE Job IN (All the values goes here)
Microsoft docs for IN says:
"Explicitly including an extremely large number of values (many
thousands of values separated by commas) within the parentheses, in an
IN clause can consume resources and return errors 8623 or 8632. To
work around this problem, store the items in the IN list in a table,
and use a SELECT subquery within an IN clause."
but is there any exact or approximate number for
many thousands of values
sql-server t-sql
sql-server t-sql
asked Feb 1 at 15:38
UnnamedUnnamed
283
asked Feb 1 at 15:38
UnnamedUnnamed
283
asked Feb 1 at 15:38
UnnamedUnnamed
283
asked Feb 1 at 15:38
UnnamedUnnamed
283
asked Feb 1 at 15:38
UnnamedUnnamed
283
283
I had an issue last year where the in clause had more than 30,000 values, it was a query generated dinamically from the application. But seems to be an issue not with the values but something else, as later we hit the error with more than 20,000 values, we ended asking the developers to use the select subquery instead of using all the values.
– dbamex
Feb 1 at 15:46
I though the select query would be translated to the actual values separated by commas. but I think I´m wrong, probably it does some other "behind scenes" stuff to optimized that.
– Unnamed
Feb 1 at 15:49
1
Subquery will translate to a join typically, not the materialization of a set of comma-separated values.
– Aaron Bertrand♦
Feb 1 at 15:53
add a comment |
I had an issue last year where the in clause had more than 30,000 values, it was a query generated dinamically from the application. But seems to be an issue not with the values but something else, as later we hit the error with more than 20,000 values, we ended asking the developers to use the select subquery instead of using all the values.
– dbamex
Feb 1 at 15:46
I though the select query would be translated to the actual values separated by commas. but I think I´m wrong, probably it does some other "behind scenes" stuff to optimized that.
– Unnamed
Feb 1 at 15:49
1
Subquery will translate to a join typically, not the materialization of a set of comma-separated values.
– Aaron Bertrand♦
Feb 1 at 15:53
I had an issue last year where the in clause had more than 30,000 values, it was a query generated dinamically from the application. But seems to be an issue not with the values but something else, as later we hit the error with more than 20,000 values, we ended asking the developers to use the select subquery instead of using all the values.
– dbamex
Feb 1 at 15:46
I had an issue last year where the in clause had more than 30,000 values, it was a query generated dinamically from the application. But seems to be an issue not with the values but something else, as later we hit the error with more than 20,000 values, we ended asking the developers to use the select subquery instead of using all the values.
– dbamex
Feb 1 at 15:46
I though the select query would be translated to the actual values separated by commas. but I think I´m wrong, probably it does some other "behind scenes" stuff to optimized that.
– Unnamed
Feb 1 at 15:49
I though the select query would be translated to the actual values separated by commas. but I think I´m wrong, probably it does some other "behind scenes" stuff to optimized that.
– Unnamed
Feb 1 at 15:49
1
1
Subquery will translate to a join typically, not the materialization of a set of comma-separated values.
– Aaron Bertrand♦
Feb 1 at 15:53
Subquery will translate to a join typically, not the materialization of a set of comma-separated values.
– Aaron Bertrand♦
Feb 1 at 15:53
add a comment |
2 Answers
2
active
oldest
votes
It depends. Seriously. That’s why the docs can’t be specific about where you will notice degradation in your environment.
The solution is to just stop doing this (and worrying about it) and use a table-valued parameter.
If you have the values in C# in such a way that you can build a comma-separated list as a string to concatenate together in a query, you have the values in C# in such a way that you could stuff them in a DataTable or other structure (maybe that's where they're coming from in the first place), and pass that structure as a parameter to the stored procedure.
answered Feb 1 at 15:43
Aaron Bertrand♦Aaron Bertrand
154k18298493
based on what it depends?, maybe the field type? I´ve read thatJoiningthe tables is better (and that is what I usually do), but also I´ve seen some queries where the only possible fix is the IN filter. Maybe break apart the parameters?.
– Unnamed
Feb 1 at 15:46
3
Data type, size of actual values, size of overall text, network speed, concurrency, ... what do you mean by “the only possible fix” - fixing what exactly?
– Aaron Bertrand♦
Feb 1 at 15:54
3
If you have the values in C#, you can put them into a DataTable, and pass that as a parameter to the stored procedure.
– Aaron Bertrand♦
Feb 1 at 16:38
3
@Joshua I feel like if you're hitting that kind of limit, there is probably a better way. A user isn't picking 30,000 individual items, are they picking an entire category, or all of the subordinates of a manager, or all of the parts in a manufacturing process, or just picking "select all" on a massive drop-down? Or everything except a handful of items? Seems like you could handle those cases differently instead of throwing 30,000 items at the database in any form.
– Aaron Bertrand♦
Feb 1 at 17:53
1
@Joshua Well, you can create a TVP with a (clustered or not) primary key or unique constraint, so you don't have to rely on the default behavior, might be worth testing it out instead of dealing with branching logic or temp tables sometimes...
– Aaron Bertrand♦
Feb 1 at 18:16
|
show 3 more comments
Strictly speaking you're guaranteed for the query to fail with 65536 values. With that said, I think it's fairly safe to consider 32768 as an upper bound in practice, but it's not the least upper bound. The least upper bound depends on what else is going on in the query and other local factors. You can see this with a simple example. First put 100k rows into a heap:
DROP TABLE IF EXISTS dbo.Q228695;
CREATE TABLE dbo.Q228695 (ID BIGINT);
INSERT INTO dbo.Q228695 WITH (TABLOCK)
SELECT TOP (100000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
OPTION (MAXDOP 1);
Suppose I want to put 32768 values in the IN clause for a query of the following form: SELECT COUNT(*) FROM dbo.Q228695 WHERE ID IN (); This is easy to do in SQL Server 2017 with STRING_AGG:
DECLARE @nou VARCHAR(MAX);
SELECT @nou = 'SELECT COUNT(*) FROM dbo.Q228695 WHERE ID IN (' + STRING_AGG(CAST(RN AS VARCHAR(MAX)), ',') + ')'
FROM
(
SELECT TOP (32768) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
) q
OPTION (MAXDOP 1);
EXEC (@nou);
I get the following error:
Msg 8632, Level 17, State 2, Line 1
Internal error: An expression services limit has been reached. Please look for potentially complex expressions in your query, and try to simplify them.
If I remove one value from the IN clause the query succeeds after 24 seconds. For this incredibly simple query, 32767 is the maximum number of values that still allows the query to execute. Note that Microsoft documentation on error 8632 says the following:
This issue occurs because SQL Server limits the number of identifiers
and constants that can be contained in a single expression of a query.
This limit is 65,535.
32767 works and 32768 doesn't work. I don't think that it's a coincidence that 65535/2 = 32767.5. For whatever reason, the observed behavior is that each constant in the IN clause counts as two towards the limit.
I do think that this is the wrong question to ask. If I put those same values into a temp table then the query executes in 0 seconds:
DROP TABLE IF EXISTS #t;
CREATE TABLE #t (ID BIGINT);
INSERT INTO #t WITH (TABLOCK)
SELECT TOP (32768) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
OPTION (MAXDOP 1);
SELECT COUNT(*)
FROM dbo.Q228695
WHERE ID IN (
SELECT t.ID
FROM #t t
);
Even if your query doesn't throw an error you're going to pay a heavy performance price once you put too many values into an IN clause.
answered Feb 6 at 2:16
Joe ObbishJoe Obbish
21.9k43291
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%2f228695%2fis-there-any-limit-for-in-results-in-sql-server%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
It depends. Seriously. That’s why the docs can’t be specific about where you will notice degradation in your environment.
The solution is to just stop doing this (and worrying about it) and use a table-valued parameter.
If you have the values in C# in such a way that you can build a comma-separated list as a string to concatenate together in a query, you have the values in C# in such a way that you could stuff them in a DataTable or other structure (maybe that's where they're coming from in the first place), and pass that structure as a parameter to the stored procedure.
answered Feb 1 at 15:43
Aaron Bertrand♦Aaron Bertrand
154k18298493
based on what it depends?, maybe the field type? I´ve read thatJoiningthe tables is better (and that is what I usually do), but also I´ve seen some queries where the only possible fix is the IN filter. Maybe break apart the parameters?.
– Unnamed
Feb 1 at 15:46
3
Data type, size of actual values, size of overall text, network speed, concurrency, ... what do you mean by “the only possible fix” - fixing what exactly?
– Aaron Bertrand♦
Feb 1 at 15:54
3
If you have the values in C#, you can put them into a DataTable, and pass that as a parameter to the stored procedure.
– Aaron Bertrand♦
Feb 1 at 16:38
3
@Joshua I feel like if you're hitting that kind of limit, there is probably a better way. A user isn't picking 30,000 individual items, are they picking an entire category, or all of the subordinates of a manager, or all of the parts in a manufacturing process, or just picking "select all" on a massive drop-down? Or everything except a handful of items? Seems like you could handle those cases differently instead of throwing 30,000 items at the database in any form.
– Aaron Bertrand♦
Feb 1 at 17:53
1
@Joshua Well, you can create a TVP with a (clustered or not) primary key or unique constraint, so you don't have to rely on the default behavior, might be worth testing it out instead of dealing with branching logic or temp tables sometimes...
– Aaron Bertrand♦
Feb 1 at 18:16
|
show 3 more comments
It depends. Seriously. That’s why the docs can’t be specific about where you will notice degradation in your environment.
The solution is to just stop doing this (and worrying about it) and use a table-valued parameter.
If you have the values in C# in such a way that you can build a comma-separated list as a string to concatenate together in a query, you have the values in C# in such a way that you could stuff them in a DataTable or other structure (maybe that's where they're coming from in the first place), and pass that structure as a parameter to the stored procedure.
answered Feb 1 at 15:43
Aaron Bertrand♦Aaron Bertrand
154k18298493
based on what it depends?, maybe the field type? I´ve read thatJoiningthe tables is better (and that is what I usually do), but also I´ve seen some queries where the only possible fix is the IN filter. Maybe break apart the parameters?.
– Unnamed
Feb 1 at 15:46
3
Data type, size of actual values, size of overall text, network speed, concurrency, ... what do you mean by “the only possible fix” - fixing what exactly?
– Aaron Bertrand♦
Feb 1 at 15:54
3
If you have the values in C#, you can put them into a DataTable, and pass that as a parameter to the stored procedure.
– Aaron Bertrand♦
Feb 1 at 16:38
3
@Joshua I feel like if you're hitting that kind of limit, there is probably a better way. A user isn't picking 30,000 individual items, are they picking an entire category, or all of the subordinates of a manager, or all of the parts in a manufacturing process, or just picking "select all" on a massive drop-down? Or everything except a handful of items? Seems like you could handle those cases differently instead of throwing 30,000 items at the database in any form.
– Aaron Bertrand♦
Feb 1 at 17:53
1
@Joshua Well, you can create a TVP with a (clustered or not) primary key or unique constraint, so you don't have to rely on the default behavior, might be worth testing it out instead of dealing with branching logic or temp tables sometimes...
– Aaron Bertrand♦
Feb 1 at 18:16
|
show 3 more comments
It depends. Seriously. That’s why the docs can’t be specific about where you will notice degradation in your environment.
The solution is to just stop doing this (and worrying about it) and use a table-valued parameter.
If you have the values in C# in such a way that you can build a comma-separated list as a string to concatenate together in a query, you have the values in C# in such a way that you could stuff them in a DataTable or other structure (maybe that's where they're coming from in the first place), and pass that structure as a parameter to the stored procedure.
answered Feb 1 at 15:43
Aaron Bertrand♦Aaron Bertrand
154k18298493
It depends. Seriously. That’s why the docs can’t be specific about where you will notice degradation in your environment.
The solution is to just stop doing this (and worrying about it) and use a table-valued parameter.
If you have the values in C# in such a way that you can build a comma-separated list as a string to concatenate together in a query, you have the values in C# in such a way that you could stuff them in a DataTable or other structure (maybe that's where they're coming from in the first place), and pass that structure as a parameter to the stored procedure.
answered Feb 1 at 15:43
Aaron Bertrand♦Aaron Bertrand
154k18298493
edited Feb 1 at 16:42
answered Feb 1 at 15:43
Aaron Bertrand♦Aaron Bertrand
154k18298493
answered Feb 1 at 15:43
Aaron Bertrand♦Aaron Bertrand
154k18298493
answered Feb 1 at 15:43
Aaron Bertrand♦Aaron Bertrand
154k18298493
154k18298493
based on what it depends?, maybe the field type? I´ve read thatJoiningthe tables is better (and that is what I usually do), but also I´ve seen some queries where the only possible fix is the IN filter. Maybe break apart the parameters?.
– Unnamed
Feb 1 at 15:46
3
Data type, size of actual values, size of overall text, network speed, concurrency, ... what do you mean by “the only possible fix” - fixing what exactly?
– Aaron Bertrand♦
Feb 1 at 15:54
3
If you have the values in C#, you can put them into a DataTable, and pass that as a parameter to the stored procedure.
– Aaron Bertrand♦
Feb 1 at 16:38
3
@Joshua I feel like if you're hitting that kind of limit, there is probably a better way. A user isn't picking 30,000 individual items, are they picking an entire category, or all of the subordinates of a manager, or all of the parts in a manufacturing process, or just picking "select all" on a massive drop-down? Or everything except a handful of items? Seems like you could handle those cases differently instead of throwing 30,000 items at the database in any form.
– Aaron Bertrand♦
Feb 1 at 17:53
1
@Joshua Well, you can create a TVP with a (clustered or not) primary key or unique constraint, so you don't have to rely on the default behavior, might be worth testing it out instead of dealing with branching logic or temp tables sometimes...
– Aaron Bertrand♦
Feb 1 at 18:16
|
show 3 more comments
based on what it depends?, maybe the field type? I´ve read thatJoiningthe tables is better (and that is what I usually do), but also I´ve seen some queries where the only possible fix is the IN filter. Maybe break apart the parameters?.
– Unnamed
Feb 1 at 15:46
3
Data type, size of actual values, size of overall text, network speed, concurrency, ... what do you mean by “the only possible fix” - fixing what exactly?
– Aaron Bertrand♦
Feb 1 at 15:54
3
If you have the values in C#, you can put them into a DataTable, and pass that as a parameter to the stored procedure.
– Aaron Bertrand♦
Feb 1 at 16:38
3
@Joshua I feel like if you're hitting that kind of limit, there is probably a better way. A user isn't picking 30,000 individual items, are they picking an entire category, or all of the subordinates of a manager, or all of the parts in a manufacturing process, or just picking "select all" on a massive drop-down? Or everything except a handful of items? Seems like you could handle those cases differently instead of throwing 30,000 items at the database in any form.
– Aaron Bertrand♦
Feb 1 at 17:53
1
@Joshua Well, you can create a TVP with a (clustered or not) primary key or unique constraint, so you don't have to rely on the default behavior, might be worth testing it out instead of dealing with branching logic or temp tables sometimes...
– Aaron Bertrand♦
Feb 1 at 18:16
based on what it depends?, maybe the field type? I´ve read that
Joining the tables is better (and that is what I usually do), but also I´ve seen some queries where the only possible fix is the IN filter. Maybe break apart the parameters?.– Unnamed
Feb 1 at 15:46
based on what it depends?, maybe the field type? I´ve read that
Joining the tables is better (and that is what I usually do), but also I´ve seen some queries where the only possible fix is the IN filter. Maybe break apart the parameters?.– Unnamed
Feb 1 at 15:46
3
3
Data type, size of actual values, size of overall text, network speed, concurrency, ... what do you mean by “the only possible fix” - fixing what exactly?
– Aaron Bertrand♦
Feb 1 at 15:54
Data type, size of actual values, size of overall text, network speed, concurrency, ... what do you mean by “the only possible fix” - fixing what exactly?
– Aaron Bertrand♦
Feb 1 at 15:54
3
3
If you have the values in C#, you can put them into a DataTable, and pass that as a parameter to the stored procedure.
– Aaron Bertrand♦
Feb 1 at 16:38
If you have the values in C#, you can put them into a DataTable, and pass that as a parameter to the stored procedure.
– Aaron Bertrand♦
Feb 1 at 16:38
3
3
@Joshua I feel like if you're hitting that kind of limit, there is probably a better way. A user isn't picking 30,000 individual items, are they picking an entire category, or all of the subordinates of a manager, or all of the parts in a manufacturing process, or just picking "select all" on a massive drop-down? Or everything except a handful of items? Seems like you could handle those cases differently instead of throwing 30,000 items at the database in any form.
– Aaron Bertrand♦
Feb 1 at 17:53
@Joshua I feel like if you're hitting that kind of limit, there is probably a better way. A user isn't picking 30,000 individual items, are they picking an entire category, or all of the subordinates of a manager, or all of the parts in a manufacturing process, or just picking "select all" on a massive drop-down? Or everything except a handful of items? Seems like you could handle those cases differently instead of throwing 30,000 items at the database in any form.
– Aaron Bertrand♦
Feb 1 at 17:53
1
1
@Joshua Well, you can create a TVP with a (clustered or not) primary key or unique constraint, so you don't have to rely on the default behavior, might be worth testing it out instead of dealing with branching logic or temp tables sometimes...
– Aaron Bertrand♦
Feb 1 at 18:16
@Joshua Well, you can create a TVP with a (clustered or not) primary key or unique constraint, so you don't have to rely on the default behavior, might be worth testing it out instead of dealing with branching logic or temp tables sometimes...
– Aaron Bertrand♦
Feb 1 at 18:16
|
show 3 more comments
Strictly speaking you're guaranteed for the query to fail with 65536 values. With that said, I think it's fairly safe to consider 32768 as an upper bound in practice, but it's not the least upper bound. The least upper bound depends on what else is going on in the query and other local factors. You can see this with a simple example. First put 100k rows into a heap:
DROP TABLE IF EXISTS dbo.Q228695;
CREATE TABLE dbo.Q228695 (ID BIGINT);
INSERT INTO dbo.Q228695 WITH (TABLOCK)
SELECT TOP (100000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
OPTION (MAXDOP 1);
Suppose I want to put 32768 values in the IN clause for a query of the following form: SELECT COUNT(*) FROM dbo.Q228695 WHERE ID IN (); This is easy to do in SQL Server 2017 with STRING_AGG:
DECLARE @nou VARCHAR(MAX);
SELECT @nou = 'SELECT COUNT(*) FROM dbo.Q228695 WHERE ID IN (' + STRING_AGG(CAST(RN AS VARCHAR(MAX)), ',') + ')'
FROM
(
SELECT TOP (32768) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
) q
OPTION (MAXDOP 1);
EXEC (@nou);
I get the following error:
Msg 8632, Level 17, State 2, Line 1
Internal error: An expression services limit has been reached. Please look for potentially complex expressions in your query, and try to simplify them.
If I remove one value from the IN clause the query succeeds after 24 seconds. For this incredibly simple query, 32767 is the maximum number of values that still allows the query to execute. Note that Microsoft documentation on error 8632 says the following:
This issue occurs because SQL Server limits the number of identifiers
and constants that can be contained in a single expression of a query.
This limit is 65,535.
32767 works and 32768 doesn't work. I don't think that it's a coincidence that 65535/2 = 32767.5. For whatever reason, the observed behavior is that each constant in the IN clause counts as two towards the limit.
I do think that this is the wrong question to ask. If I put those same values into a temp table then the query executes in 0 seconds:
DROP TABLE IF EXISTS #t;
CREATE TABLE #t (ID BIGINT);
INSERT INTO #t WITH (TABLOCK)
SELECT TOP (32768) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
OPTION (MAXDOP 1);
SELECT COUNT(*)
FROM dbo.Q228695
WHERE ID IN (
SELECT t.ID
FROM #t t
);
Even if your query doesn't throw an error you're going to pay a heavy performance price once you put too many values into an IN clause.
answered Feb 6 at 2:16
Joe ObbishJoe Obbish
21.9k43291
add a comment |
Strictly speaking you're guaranteed for the query to fail with 65536 values. With that said, I think it's fairly safe to consider 32768 as an upper bound in practice, but it's not the least upper bound. The least upper bound depends on what else is going on in the query and other local factors. You can see this with a simple example. First put 100k rows into a heap:
DROP TABLE IF EXISTS dbo.Q228695;
CREATE TABLE dbo.Q228695 (ID BIGINT);
INSERT INTO dbo.Q228695 WITH (TABLOCK)
SELECT TOP (100000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
OPTION (MAXDOP 1);
Suppose I want to put 32768 values in the IN clause for a query of the following form: SELECT COUNT(*) FROM dbo.Q228695 WHERE ID IN (); This is easy to do in SQL Server 2017 with STRING_AGG:
DECLARE @nou VARCHAR(MAX);
SELECT @nou = 'SELECT COUNT(*) FROM dbo.Q228695 WHERE ID IN (' + STRING_AGG(CAST(RN AS VARCHAR(MAX)), ',') + ')'
FROM
(
SELECT TOP (32768) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
) q
OPTION (MAXDOP 1);
EXEC (@nou);
I get the following error:
Msg 8632, Level 17, State 2, Line 1
Internal error: An expression services limit has been reached. Please look for potentially complex expressions in your query, and try to simplify them.
If I remove one value from the IN clause the query succeeds after 24 seconds. For this incredibly simple query, 32767 is the maximum number of values that still allows the query to execute. Note that Microsoft documentation on error 8632 says the following:
This issue occurs because SQL Server limits the number of identifiers
and constants that can be contained in a single expression of a query.
This limit is 65,535.
32767 works and 32768 doesn't work. I don't think that it's a coincidence that 65535/2 = 32767.5. For whatever reason, the observed behavior is that each constant in the IN clause counts as two towards the limit.
I do think that this is the wrong question to ask. If I put those same values into a temp table then the query executes in 0 seconds:
DROP TABLE IF EXISTS #t;
CREATE TABLE #t (ID BIGINT);
INSERT INTO #t WITH (TABLOCK)
SELECT TOP (32768) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
OPTION (MAXDOP 1);
SELECT COUNT(*)
FROM dbo.Q228695
WHERE ID IN (
SELECT t.ID
FROM #t t
);
Even if your query doesn't throw an error you're going to pay a heavy performance price once you put too many values into an IN clause.
answered Feb 6 at 2:16
Joe ObbishJoe Obbish
21.9k43291
add a comment |
Strictly speaking you're guaranteed for the query to fail with 65536 values. With that said, I think it's fairly safe to consider 32768 as an upper bound in practice, but it's not the least upper bound. The least upper bound depends on what else is going on in the query and other local factors. You can see this with a simple example. First put 100k rows into a heap:
DROP TABLE IF EXISTS dbo.Q228695;
CREATE TABLE dbo.Q228695 (ID BIGINT);
INSERT INTO dbo.Q228695 WITH (TABLOCK)
SELECT TOP (100000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
OPTION (MAXDOP 1);
Suppose I want to put 32768 values in the IN clause for a query of the following form: SELECT COUNT(*) FROM dbo.Q228695 WHERE ID IN (); This is easy to do in SQL Server 2017 with STRING_AGG:
DECLARE @nou VARCHAR(MAX);
SELECT @nou = 'SELECT COUNT(*) FROM dbo.Q228695 WHERE ID IN (' + STRING_AGG(CAST(RN AS VARCHAR(MAX)), ',') + ')'
FROM
(
SELECT TOP (32768) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
) q
OPTION (MAXDOP 1);
EXEC (@nou);
I get the following error:
Msg 8632, Level 17, State 2, Line 1
Internal error: An expression services limit has been reached. Please look for potentially complex expressions in your query, and try to simplify them.
If I remove one value from the IN clause the query succeeds after 24 seconds. For this incredibly simple query, 32767 is the maximum number of values that still allows the query to execute. Note that Microsoft documentation on error 8632 says the following:
This issue occurs because SQL Server limits the number of identifiers
and constants that can be contained in a single expression of a query.
This limit is 65,535.
32767 works and 32768 doesn't work. I don't think that it's a coincidence that 65535/2 = 32767.5. For whatever reason, the observed behavior is that each constant in the IN clause counts as two towards the limit.
I do think that this is the wrong question to ask. If I put those same values into a temp table then the query executes in 0 seconds:
DROP TABLE IF EXISTS #t;
CREATE TABLE #t (ID BIGINT);
INSERT INTO #t WITH (TABLOCK)
SELECT TOP (32768) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
OPTION (MAXDOP 1);
SELECT COUNT(*)
FROM dbo.Q228695
WHERE ID IN (
SELECT t.ID
FROM #t t
);
Even if your query doesn't throw an error you're going to pay a heavy performance price once you put too many values into an IN clause.
answered Feb 6 at 2:16
Joe ObbishJoe Obbish
21.9k43291
Strictly speaking you're guaranteed for the query to fail with 65536 values. With that said, I think it's fairly safe to consider 32768 as an upper bound in practice, but it's not the least upper bound. The least upper bound depends on what else is going on in the query and other local factors. You can see this with a simple example. First put 100k rows into a heap:
DROP TABLE IF EXISTS dbo.Q228695;
CREATE TABLE dbo.Q228695 (ID BIGINT);
INSERT INTO dbo.Q228695 WITH (TABLOCK)
SELECT TOP (100000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
OPTION (MAXDOP 1);
Suppose I want to put 32768 values in the IN clause for a query of the following form: SELECT COUNT(*) FROM dbo.Q228695 WHERE ID IN (); This is easy to do in SQL Server 2017 with STRING_AGG:
DECLARE @nou VARCHAR(MAX);
SELECT @nou = 'SELECT COUNT(*) FROM dbo.Q228695 WHERE ID IN (' + STRING_AGG(CAST(RN AS VARCHAR(MAX)), ',') + ')'
FROM
(
SELECT TOP (32768) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
) q
OPTION (MAXDOP 1);
EXEC (@nou);
I get the following error:
Msg 8632, Level 17, State 2, Line 1
Internal error: An expression services limit has been reached. Please look for potentially complex expressions in your query, and try to simplify them.
If I remove one value from the IN clause the query succeeds after 24 seconds. For this incredibly simple query, 32767 is the maximum number of values that still allows the query to execute. Note that Microsoft documentation on error 8632 says the following:
This issue occurs because SQL Server limits the number of identifiers
and constants that can be contained in a single expression of a query.
This limit is 65,535.
32767 works and 32768 doesn't work. I don't think that it's a coincidence that 65535/2 = 32767.5. For whatever reason, the observed behavior is that each constant in the IN clause counts as two towards the limit.
I do think that this is the wrong question to ask. If I put those same values into a temp table then the query executes in 0 seconds:
DROP TABLE IF EXISTS #t;
CREATE TABLE #t (ID BIGINT);
INSERT INTO #t WITH (TABLOCK)
SELECT TOP (32768) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
OPTION (MAXDOP 1);
SELECT COUNT(*)
FROM dbo.Q228695
WHERE ID IN (
SELECT t.ID
FROM #t t
);
Even if your query doesn't throw an error you're going to pay a heavy performance price once you put too many values into an IN clause.
answered Feb 6 at 2:16
Joe ObbishJoe Obbish
21.9k43291
answered Feb 6 at 2:16
Joe ObbishJoe Obbish
21.9k43291
answered Feb 6 at 2:16
Joe ObbishJoe Obbish
21.9k43291
answered Feb 6 at 2:16
Joe ObbishJoe Obbish
21.9k43291
21.9k43291
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%2f228695%2fis-there-any-limit-for-in-results-in-sql-server%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
I had an issue last year where the in clause had more than 30,000 values, it was a query generated dinamically from the application. But seems to be an issue not with the values but something else, as later we hit the error with more than 20,000 values, we ended asking the developers to use the select subquery instead of using all the values.
– dbamex
Feb 1 at 15:46
I though the select query would be translated to the actual values separated by commas. but I think I´m wrong, probably it does some other "behind scenes" stuff to optimized that.
– Unnamed
Feb 1 at 15:49
1
Subquery will translate to a join typically, not the materialization of a set of comma-separated values.
– Aaron Bertrand♦
Feb 1 at 15:53