Skip to main content
78 votes
Accepted

SQL Server Error 8632 due to over 100,000 entries in WHERE clause

To search for more than 100,000 values, put them in a temp table instead, one row per value that you're searching for. Then, join your query to that temp table for filtering. Something with more than ...
Brent Ozar's user avatar
58 votes
Accepted

How to limit maximum number of rows in a table to just 1

These two constraints would do: CREATE TABLE dbo.Configuration ( ConfigurationID TINYINT NOT NULL DEFAULT 1, -- the rest of the columns CONSTRAINT Configuration_PK PRIMARY KEY (...
ypercubeᵀᴹ's user avatar
25 votes

How to limit maximum number of rows in a table to just 1

You could define the ID as a computed column evaluating to a constant value, and declare that column to be unique: CREATE TABLE dbo.Configuration ( ID AS CAST(1 AS tinyint), -- or: AS bit ... --...
Andriy M's user avatar
  • 23k
17 votes
Accepted

Why *not* ERROR: index row size xxxx exceeds maximum 2712 for index "foo"?

Short answer: compression. The data type text allows (lossless!) compression and storage out of line by default: SELECT typstorage FROM pg_type WHERE typname = 'text'; -- 'x' The manual about ...
Erwin Brandstetter's user avatar
13 votes

SQL Server Error 8632 due to over 100,000 entries in WHERE clause

If you're going to change the app anyway, consider either (a) using a TVP for the entire set of values - you would create a DataTable in C# and pass it into a stored procedure using StructuredType, ...
Aaron Bertrand's user avatar
12 votes
Accepted

MongoDB terminates when it runs out of memory

As per MongoDB BOL Here Changed in version 3.4: Values can range from 256MB to 10TB and can be a float. In addition, the default value has also changed. Starting in 3.4, the WiredTiger internal cache,...
Md Haidar Ali Khan's user avatar
8 votes
Accepted

Limits of applock in MS SQL Server

I didn't see official documentation answering your questions, but some of these things you can yourself (on a testing/dev server first!). Here's what I found: Unless you are hoping to use a very ...
Geoff Patterson's user avatar
8 votes
Accepted

In MySQL, what's the most efficient way to query an entire table in chunks?

OFFSET is very bad. Instead of processing the data in Order(N) time, it will take Order(N*N). This is because of the effort to skip past the "offset" rows. Instead "remember where you left off". I ...
Rick James's user avatar
  • 78.1k
8 votes
Accepted

Limit left join for multiple rows

What version of MySQL are you using? MySQL 8 supports LATERAL which you need to be able to refer to i from the derived table: SELECT i.*, si.child, si.parent_id FROM items as i LEFT JOIN LATERAL (...
Lennart - Slava Ukraini's user avatar
7 votes

UPDATE Only one row (using update and join)

LIMIT is not allowed when the UPDATE has joins. You can however move the join and the LIMIT inside a subquery and then join back to the table to be updated using the primary or a unique key. This is ...
ypercubeᵀᴹ's user avatar
7 votes

SQL Server Error 8632 due to over 100,000 entries in WHERE clause

No it is not configurable and you cannot increase this to a higher one. There are workaround suggested in the MSDN article you mentioned and other articles. I mentioned two here but you can search ...
SqlWorldWide's user avatar
  • 13.2k
7 votes

MongoDB terminates when it runs out of memory

Actually, if you look closely, it's not mongod that dies for "out-of-memory", it's the kernel OOM (out of memory) manager that kills mongod, because it has the biggest memory usage. Yes, you can try ...
JJussi's user avatar
  • 5,703
6 votes

SQL Server Error 8632 due to over 100,000 entries in WHERE clause

Just my 2¢ regarding making the query condition shorter:- If you're able to determine all the possible values of entry in advance, would it feasible if you take the complement of your query? Before ...
Zephyr's user avatar
  • 203
6 votes
Accepted

How to use ORDER BY and LIMIT inside JSON_AGG

The syntax of the ORDER BY inside JSON_AGG does not accept a LIMIT option. I am not aware of a way to limit the scope of any aggregate function in this way. As an alternative, you can try the ...
Andriy M's user avatar
  • 23k
5 votes

Express edition core limits ambiguity

SQL Server 2005 Express supports a single socket with up to four cores. Each core is mapped to a SQLOS scheduler. There is one VISIBLE ONLINE scheduler per core in the sys.dm_os_schedulers dynamic ...
Paul White's user avatar
  • 84.3k
4 votes
Accepted

Limit number of simultaneous queries by user

This is not possible now. You have two classes of configuration options Connections and Authentication, includes things like max_connections Session/Client Configuration, includes things like ...
Evan Carroll's user avatar
  • 63.2k
4 votes

MySQL LIMIT within JOIN

For a correlated subquery you should use a CROSS APPLY, unfortunately MySql doesn't allow it, but you can simulate it on this way: SELECT `symbols`.`id`, `symbols`.`symbol`, ...
McNets's user avatar
  • 23.8k
4 votes

How to limit the amount of values within a postgres table which are updated

Using common table expressions or CTE might be a good solution, more dynamically (easily changed if the conditions of the updated pattern are modified ) , in terms of performance it should be tested. ...
Ergest Basha's user avatar
  • 3,935
3 votes
Accepted

mySQL Query with a LIMIT on a row value

For a reasonable new version of MySQL you can use a window function: SELECT * FROM ( SELECT y.*, ROW_NUMBER() OVER (PARTITION BY x ORDER BY ts) as rn FROM y WHERE x IN (a,b,c,d) ) AS t ...
Lennart - Slava Ukraini's user avatar
2 votes
Accepted

Row size too large (> 8126)

5.5.14 and 5.6.3 and 5.7 have innodb_file_format=Barracuda as an option. Prior to 5.7.7, do these: SET GLOBAL innodb_file_format=Barracuda; SET GLOBAL innodb_file_per_table=1; SET GLOBAL ...
Rick James's user avatar
  • 78.1k
2 votes
Accepted

Is there a server option which limits the amount of rows returned by a remote query?

You can use OFFSET and FETCH to effectively page through results e.g. SELECT * FROM MyTable ORDER BY MyColumn OFFSET 100 FETCH NEXT 100 ROWS ONLY Helpful article here... Article About OFFSET FETCH ...
Ollie's user avatar
  • 738
2 votes
Accepted

Is it possible to limit concurrent aggregations in Mongo?

Answer is simple, NO. Not in mongodb. But you can always do it in your own application. Just create "lock" collection where applications mark they will to do aggregation. Ticket system for queuing.
JJussi's user avatar
  • 5,703
2 votes
Accepted

MySQL LIMIT within JOIN

Try this: SELECT sym.id, sym.symbol, s.marketCapitalization FROM symbols AS sym INNER JOIN company_key_statistics AS s ON sym.id = s.companyId INNER JOIN (SELECT companyId, MAX(...
Dario's user avatar
  • 748
2 votes

PostgreSQL Size Quota on Table or Schema

You can create a trigger that checks the number of records in your destination table when an INSERT is used. The trigger function would look like this: CREATE OR REPLACE FUNCTION check_number_of_row(...
Ashiq Ahamed's user avatar
2 votes
Accepted

MySQL 5.5 vs 5.6,5.7 above same database same query but different output, limit have duplicated ID

Added ORDER BY up_posts.post_date DESC, up_posts.ID DESC no more duplicate result. Seem like we need to manually add this to avoid an unnecessary problem. FYI. It applied to MySQL 5.6 and 5.7. See ...
Shiro's user avatar
  • 153
2 votes

Is it bad form to use "LIMIT 1" on queries that should only return one row anyway?

For a primary column it doesn't give any difference. But what if, in the future for some reasons, you changed the primary column and you forgot to change the query too? Now your WHERE column contains ...
tatskie's user avatar
  • 113
2 votes
Accepted

Is it bad form to use "LIMIT 1" on queries that should only return one row anyway?

For SELECT, I think one of the main considerations has to do with performance. When using LIMIT 1 statistics on row correlations could play a part in determining slower/faster query execution. ...
Andy's user avatar
  • 593
2 votes

MySQL failed connection attempts hitting max_connections

I don't think the failed connections are causing the max connections issue, but can you set log_warnings=2 and after a short time, view the error log to see if there is more information there? The ...
Valerie Parham-Thompson's user avatar
2 votes
Accepted

Express edition core limits ambiguity

As per MSDN Bol SQL Server 2005 Express has support like Limited to one CPU, where CPU means sockets, not limited to cores and hyperthreading. Up to 2008 this was the policy and there isn't any ...
Md Haidar Ali Khan's user avatar

Only top scored, non community-wiki answers of a minimum length are eligible