Hi Naomi,

Just tried with View, it's like almost the same. For the approach that you proposed, doing a sub Row_Number() before UNION seems workable.

However, if the records are not in the range of Sub Row_Number(), but fall in the Final Row_Number(), those records will be missing from the selection.

Example
User select page 2, which will list Row 11th to 20th of the UNION records.
Page Index = 1, MaxRow = 10

Table 1 contains ID from 1 to 100, return ID 11 to 20 (Row 11th to 20th)
Table 2 contains ID from 11 to 200, return ID 20 to 30 (Row 11th to 20th)
Table 3 contains ID from 5 to 150, return ID 50 to 100 (Row 11th to 20th)

Thus, records with ID 11 to 20 from Table 2 and Table 3 will not be selected as the Sub Row_Number will filter them out.

I have set the SET STATISTICS PROFILE ON, but the log is too big to post all, perhaps I can post the specified portion that you needed.


Thanks.