Post reply

Does sub query in select, executes for only 5 recs if offset fetch next is used

  • March 16, 2017 at 9:47 am

    #336146

    This is a dummy query , i want know, the md col query(correlated sub query) will execute for only 5 records or for all records of sales.customer table.

      SELECT CustomerID, (SELECT TOP 1 Sales.SalesTerritory.ModifiedDateFROM Sales.SalesTerritory WHERE Sales.SalesTerritory.TerritoryID = Sales.Customer.TerritoryID) mdFROM Sales.Customer ORDER BY CustomerID OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY

    yours sincerely

  • March 16, 2017 at 9:52 am

    #1933528

    Try it and see if it returns what you expect.  I personally haven't used this new functionality as we are just now moving to SQL Server 2012 from SQL Server 2008.

  • March 16, 2017 at 10:00 am

    #1933536

    rajemessage 14195 - Thursday, March 16, 2017 9:47 AM

    This is a dummy query , i want know, the md col query(correlated sub query) will execute for only 5 records or for all records of sales.customer table.

      SELECT CustomerID, (SELECT TOP 1 Sales.SalesTerritory.ModifiedDateFROM Sales.SalesTerritory WHERE Sales.SalesTerritory.TerritoryID = Sales.Customer.TerritoryID) mdFROM Sales.Customer ORDER BY CustomerID OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY

    yours sincerely

    Here is the query again, for those without 75" monitors:
    SELECT CustomerID
    ,(
    SELECT TOP 1 Sales.SalesTerritory.ModifiedDateFROM Sales.SalesTerritory
    WHERE Sales.SalesTerritory.TerritoryID = Sales.Customer.TerritoryID
    ) md
    FROM Sales.Customer
    ORDER BY CustomerID OFFSET 10 ROWS
    FETCH NEXT 5 ROWS ONLY

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply