Search code examples
sqlt-sqlsql-server-2000

SQL Limit on "WHERE X IN (...)"

I've got some data I'd like to pull off our SQL server.

This old database does not have any primary keys associated with it, so pulling data is like querying an Excel spreadsheet (what it actually originated as years ago).

I need to run reports on this data, though.

Currently, I get a list of distinct serial numbers for a given time period, then pull all of the records for a given serial number. For a 1-month time frame, this can be 1500 to 3000 serial numbers. The serial number field is formatted as char(20), even though the serial numbers are only 15 characters long.

BEGIN UPDATE

  • There are typically 5 to 15 entries in this table per Serial_Number.
  • There are at most 10 machines writing data to this table, so identical Date_Time values are possible

END UPDATE

This process takes a while, but between different serial numbers in the list, I am able to update the Windows Form with a Progress Bar so management knows something is happening and about how much longer to expect.

I am always trying to make this query run faster.

Now, I am thinking about pulling the data I need using a WHERE clause such as:

SELECT Col1, Col2, Col3
FROM Table1
WHERE Serial_Number IN (
  SELECT DISTINCT Serial_Number
  FROM Table1
  WHERE Date_Time Between @startDate AND @endDate
)

My question is: Are there any issues I could run into with this, particularly because we have so many distinct serial numbers during a given time frame.

And, of course, you know someone in Management is going to try running a year's worth of data when they are bored! Then, they are going to try running data since Jesus was born, just because they've got nothing better to do.

Restate Question: Is there a limit to the WHERE clause's IN method that limits the number of items I can pass in?

Solution

  • Apparently, there is no way to tell what the maximum length of the WHERE X IN (...) can be.

    For now, this is the answer.

    If, at some later point in time, someone comes along and finds something to the contrary, please post that answer and I will mark it as such.

    Thanks, Joe