Building IN Lists

Large IN (?,?,?,...) lists with many parameters can cause slow, unstable query plans, especially compared to hard‑coded literals. Using a temporary table instead avoids the long IN list: you insert all IDs into a temp table and then join your main table to that temp table, which SQL Server can optimize more effectively for larger lists.

Illustrative code in Java:

void queryWithTempTableJoin(Connection con, List<Integer> ids) throws SQLException {
    if (ids == null || ids.isEmpty()) {
        return; // or handle "no IDs" case explicitly
    }

    boolean oldAutoCommit = con.getAutoCommit();
    con.setAutoCommit(false); // keep all steps on the same physical connection

    try {
        // 1) Create temp table
        String createSql =
            "CREATE TABLE #Ids (" +
            "    Id INT NOT NULL PRIMARY KEY" +
            ")";
        try (PreparedStatement ps = con.prepareStatement(createSql)) {
            ps.executeUpdate();
        }

        // 2) Batch-insert IDs into #Ids
        String insertSql = "INSERT INTO #Ids (Id) VALUES (?)";
        try (PreparedStatement ps = con.prepareStatement(insertSql)) {
            for (Integer id : ids) {
                ps.setInt(1, id);
                ps.addBatch();
            }
            ps.executeBatch();
        }

        // 3) Use temp table in a join instead of IN (...)
        String querySql =
            "SELECT c.* " +
            "FROM   Customer c " +
            "JOIN   #Ids i ON i.Id = c.Id";

        try (PreparedStatement ps = con.prepareStatement(querySql);
             ResultSet rs = ps.executeQuery()) {

            while (rs.next()) {
                // map row from ResultSet as needed
            }
        }

        con.commit();
    } catch (SQLException e) {
        con.rollback();
        throw e;
    } finally {
        con.setAutoCommit(oldAutoCommit);
    }
}