CODEWITHSUNDEEP
sqlsql-serversql-server-2012
yogi
yogi

Reputation: 19591

SQL Server 2012 paging and subquery

I have a of TSQL something like this

declare 
    @_RowsPerPage INT = 10,
    @_PageNumber INT = 1

select  *,
        (
            select  col
            from    TAB2
            where   TAB2.num = TAB1.num
        )
from    TAB1
order by TAB1.num
OFFSET (@_PageNumber - 1) * @_RowsPerPage ROWS
FETCH NEXT @_RowsPerPage ROWS ONLY

it's on SQL Server 2012 and it does paging of the records.

Suppose I have 50 records in TAB1 and at a time only 10 is going to be displayed because of paging. Now question I have is does the subquery that I have in there executes for 50 records (which table actually has) or for 10 records (which query returns)?

UPDATE (2014-12-29)

From answers its pretty clear that subquery is executing for 50 records here which is not what I or anyone else would want to have. So if I break down this query into 2 like below would it be better? would subquery execute for paged records only then?

declare
    @_RowsPerPage INT = 10,
    @_PageNumber INT = 1

declare @_tempTbl table (num int)

insert into @_tempTbl
select  num
from    TAB1
order by TAB1.num
OFFSET (@_PageNumber - 1) * @_RowsPerPage ROWS
FETCH NEXT @_RowsPerPage ROWS ONLY


select  TAB1.*,
        (
            select  col
            from    TAB2
            where   TAB2.num = TAB1.num
        )
from    @_tempTbl as TAB1

Upvotes: 1

Views: 397

Answers (2)

itzik Paz
itzik Paz

Reputation: 433

  1. This query is not a "LEFT OUTER JOIN", it is more "OUTER APPLY" .

  2. In this query, If the relation between the tables is not ONE to ONE, you will get an error message:

"`Msg 512, Level 16, State 1, Line 5 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. "

there for you need to add to you subquery the SELECT TOP(1) ... ORDER BY ASC/DESC.

FOR EXAMPLE :

USE NORTHWND
GO
declare 
@_RowsPerPage INT = 10,
@_PageNumber INT = 1

select  *,
    (
        select  TOP(1)
            TAB2.OrderID
        from    dbo.[Orders] TAB2
        where   TAB2.CustomerID = TAB1.CustomerID
        ORDER BY OrderID asc
    )
from    [dbo].[Customers]  TAB1
order by TAB1.PostalCode
OFFSET (@_PageNumber - 1) * @_RowsPerPage ROWS
FETCH NEXT @_RowsPerPage ROWS ONLY ;

go

2. The execution paln is "NESTED LOOP" (left outer join) . for each row from TAB1 it will access to table TAB2 . so if you are having 100 rows in TAB1 and 50 rows in TAB2 , than you will access to TAB2 100 times .(is it gone a be scan or seek on the tables ? It depends on the index tunning on the table)

Upvotes: 1

dodexahedron
dodexahedron

Reputation: 4657

For one thing, this is a left outer join query, so it should be written that way (though the query optimizer is likely taking care of that for you, behind the scenes).

To answer your question, this query has to scan the table first, and then return just the rows you wanted. Therefore, if you have 50 records in TAB1 that pass the join condition to TAB2, it's going to go over 50 records before it returns the 10 you asked for.

Re-write your query as this, to be clearer:

declare @_RowsPerPage INT = 10,
    @_PageNumber INT = 1

select  TAB1.*, TAB2.col
from    TAB1 LEFT OUTER JOIN TAB2 ON TAB1.num = TAB2.num
order by TAB1.num
OFFSET (@_PageNumber - 1) * @_RowsPerPage ROWS
FETCH NEXT @_RowsPerPage ROWS ONLY

Upvotes: 1

Related Questions