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 100,000 values isn't a parameter - it's a table. Rather than thinking about raising the limit, c...
although you're completely right, let me explain you the situation: the SQL query I'm talking about, is generated automatically by my software. Modifying this query hence means modifying the software, which includes a software update on a particular version, ..., hence my request for a quick (and dirty, I admit) solution.
@Dominique: make it a stored procedure and have your application call it. Inline code in your app requires a deployment. If your app just calls a proc, you can modify it and not really have to re-deploy the app.
@MguerraTorres You would still have to deploy the proc change. Either way you're deploying something. Procs aren't magic :). Not much difference in deploying what should be a DLL vs a proc to a production system. Both would need to go through the same level of testing and rigger. I'd even argue replacing a DLL is easier.
@user441521 that's just not true. If you work your configurations right, you will simply have to deploy your stored proc when you change it, and any objects that are invalidated because of it. I have it set up this way in 8 or so apps. Most of the time I only have to reset IIS or whatever the app's service is. Unless I make a signature/spec change which is obviously more intrusive.
"if you're approaching 10% of a̶ ̶S̶Q̶L̶ ̶S̶e̶r̶v̶e̶r any technology's limit, you're probably going to have a bad time."
@AaronBertrand If your app is distributed/load balanced/anything that means multiple installs, you should have all that mostly automated/scripted. Besides, what if your DB is distributed or there are otherwise multiple copies, too?
@MguerraTorres - Excellent advice at the planning stage. If your already-deployed app doesn't currently use a stored procedure, then you'd have to change the app to start use the stored procedure, requiring the update/deployment process this time. However, if there are future changes, they would gain the benefit you mention.
@jpmc26 An app can be distributed outside of your control, too. Think about desktop applications, mobile apps, etc. I wasn't talking about deploying an app to 5 of your own web servers, which is no different from deploying a stored procedure change to 5 replicated databases.
@AaronBertrand How in the world is deploying a database change to an end user's desktop or mobile device easier than deploying a code update? The point doesn't even really hold up if you're talking about deploying to client servers you don't own, either, as they'd have to be able to deploy the app and DB in the first place; that's an even stronger reason to have your installation/update process smooth and automated.
@AaronBertrand Yes, and your server is typically hosting custom code that implements an API in HTTP, while the DB lives behind a firewall. I've never heard of out of network applications connecting directly to the DB. (I'm sure someone does it, but surely you're not recommending it.) If the change can be accomplished by a simple server side change to a stored proc, it can also be accomplished by just updating the API code without changing the API itself. So what use case do you have in mind that an app is "distributed outside of your control" where a stored proc is easier to deploy?
@AaronBertrand No, I'm just frustrated because either I have no clue what you're talking about and need clarification or you're not making sense and don't want to admit it. I really honestly don't know which it is, but so far, I can't see any clearly identified use cases in your comments where it makes sense for a database upgrade to be significantly easier than a code deploy. Your last response only mentioned a use case completely covered by my comments that preceded it. Additionally, I don't think it's befitting of a mod to act dismissively.
@AaronBertrand is trying to enlighten you based on his experience (his rep speaks for itself and he's very well respected in the SQL Server community) and you're sitting there using phrases like "You're kidding, right?" Deploying a DB change is much less intrusive than a database-related application change when you've configured everything correctly on both ends. It just is.
@MguerraTorres Aaron is the one who said, "You're kidding, right?" Not me. All I've done is elaborate on my understanding. In my experience, a database change is very intrusive. I wouldn't dare try to update database schema objects with active connections, so updating the DB means taking the entire site down. A code deployment of a single instance .NET site means a Web Deploy sync that happens in seconds and makes a maint page redundant. Restore is also simpler: a simple Web Deploy sync with a backup vs. take the site down and drop/import. Saying, "It just is," doesn't change any of that.
@MguerraTorres Also, meta.stackexchange.com/a/12427/216712 "Reputation was never designed (and never could be) to indicate skill-level. It's simple a general indicater of how much useful input they have had around the site." Reputation is not a determiner of whether someone is right or wrong in any particular instance.
The correct answer is, it depends. I also wonder how some people update their website or software. On my end, I would be out of a job if I had to take down the website for every update, including database changes. Sometimes database changes are unavoidable, or if you try to avoid them it will stilt your potential design. Likewise for trying to avoid software updates. I would agree, if appropriate, an API is the way to go. You can change the API without breaking the software or changing the compiled software. I've been able to avoid software updates by simple API changes (while still maintai…
By the way, I am actually almost impressed someone managed to exceed 100,000 WHERE entries. Most definitely some refactoring is needed.
Discussion on answer by Brent Ozar: S…
Discussion on answer by Brent Ozar: S…