SQL Paging optimization

1. First (not available, no difference with not using top) "Time 15~20s"

SELECT * FROM
(
SELECT TOP row_number () over (ORDER by Id) as Num, * from
(
SELECT * from Goods_1 WHERE Name like '% test% ' UNION all
SELECT * from goods_2 WHERE Name like '% test% ' UNION all
SELECT * from Goods_3 WHERE Name '% test% ' UNION all
SELECT * from Goods_4 WHERE Name like '% test% ' UNION all
SELECT * from Goods_5 WHERE Name like '% test% ' UNION all
SELECT * from Goods_6 WHERE Name like '% test% ' UNION all
SELECT * from goods_7 WHERE Name like '% test% ' UNION all
SELECT * from Goods_8 WHERE Name '% test% '
) as T
) as T
WHERE Num between ((2-1) * + 1) and 2 * 10;

2. Second (usable, time-consuming significantly reduced) "time-consuming 2~4s"

SELECT * FROM
(
SELECT row_number () over (ORDER by Id) as Num, * from
(
SELECT TOP * from goods_1 WHERE Name '% test% ' UNION all
SELECT TOP * from goods_2 WHERE Name '% test% ' UNION all
SELECT TOP * from Goods_3 WHERE Name '% test% ' UNION all
SELECT TOP * from Goods_4 WHERE Name '% test% ' UNION all
SELECT TOP * from goods_5 WHERE Name '% test% ' UNION all
SELECT TOP * from goods_6 WHERE Name '% test% ' UNION all
SELECT TOP * from goods_7 WHERE Name '% test% ' UNION all
SELECT TOP * from goods_8 WHERE Name '% test% '
) as T
) as T
WHERE Num between ((2-1) * + 1) and 2 * 10;

Here 2 refers to the page number of pagination, 20 refers to the paging size, such a way to reduce time-consuming can be imagined ~

SQL Paging optimization