CODEWITHSUNDEEP
sql-serverpaginationunionoffset
GettingStarted
GettingStarted

Reputation: 7605

SQL Server 2019 unable to get Pagination to work correctly with UNION

    SELECT * FROM 
           (SELECT IdNumber from LegacyTable 
           WHERE IdNumber != 0 
           ORDER BY IdNumber 
           OFFSET 0 ROWS 
           FETCH NEXT 10 ROWS ONLY
       
           UNION 
           SELECT IdNumber FROM Customer 
           WHERE IdNumber != '0' 
           ORDER BY IdNumber 
           OFFSET 0 ROWS 
           FETCH NEXT 10 ROWS ONLY) as Customers 
    ORDER BY IdNumber 
    OFFSET 0 ROWS 
    FETCH NEXT 10 ROWS ONLY

I have two tables in my database. One Table contains legacy customer data and the other one is when a new customer signs up. I want to get all rows from BOTH tables but use offset/take for pagination.

If I don't paginate, the query works but it takes a VERY LONG TIME. I am new to SQL Server but I've read that Pagination should help resolve the problem.

The above code gives me 10 records. However if I change the value of the OFFSET to 10, it ends up skipping rows.

If there is a better way, please let me know.

LegacyTable

CREATE TABLE LegacyTable (
    IdNumber INT,
    FirstName VARCHAR(40),
    LastName VARCHAR(40)
)

CustomerTable

CREATE TABLE Customer (
    IdNumber INT,
    FirstName VARCHAR(40),
    LastName VARCHAR(40)
)

The Legacy Table has 40 additional fields which was used by the mainframe application. I didnt list it here.

Upvotes: 0

Views: 403

Answers (1)

Charlieface
Charlieface

Reputation: 71593

Your issue appears to be that you are using UNION instead of UNION ALL. This means that all the inner tables need to be fully evaluated for de-duplication before moving on to the outer query.

Putting an OFFSET on the inner tables also is going to mess up the results, for obvious reasons.

You could just put it on them, and get 10 rows for each table, but another option is to use UNION ALL, which can process rows without blocking.

I note that you are using != '0' on the second table, I would assume this is an int column, so you want to use a number here, not text:

  SELECT * FROM 
           (SELECT IdNumber from LegacyTable 
           WHERE IdNumber != 0 
       
           UNION ALL

           SELECT IdNumber FROM Customer 
           WHERE IdNumber != 0 
           
    ) as Customers 
    ORDER BY IdNumber 
    OFFSET 0 ROWS 
    FETCH NEXT 10 ROWS ONLY

Upvotes: 1

Related Questions