Different number of records returned from SSMS and C# ExecuteReader

Question

Different number of records returned from SSMS and C# ExecuteReader

Old Hand

Points: 384

February 26, 2025 at 5:42 pm

I am using the EXACT same query in SQL Server Management Studio (2014) as I am in my C# program, but I get different results. How is this possible?

SELECT        tblWTHistory.Date, tblWTHistory.EstCompDate, tblOperator.BadgeID, tblOperator.OperatorName, tblWTHistory.WONum, tblWTHistory.WOType, tblWTHistory.PartNum, tblWTHistory.CellReceivedQty, tblWTHistory.OpNum, tblWorkCenter.WorkCenterName, tblWorkType.WorkTypeName, tblWTHistory.WorkQty, tblWTStatus.StatusName, tblWTHistory.ReasonID, tblWTReasons.ReasonName, tblWTHistory.Rework, tblWTHistory.OpComment
FROM             tblWTHistory INNER JOIN
                 tblWTReasons ON tblWTHistory.ReasonID = tblWTReasons.ReasonID INNER JOIN
                 tblWTStatus ON tblWTHistory.StatusID = tblWTStatus.StatusID INNER JOIN
                 tblWorkType ON tblWTHistory.WorkTypeID = tblWorkType.WorkTypeID INNER JOIN
                 tblWorkCenter ON tblWTHistory.WorkCenterID = tblWorkCenter.WorkCenterID INNER JOIN
                         tblOperator ON tblWTHistory.OperatorID = tblOperator.OperatorID
WHERE        (tblWTHistory.CellReceivedQty > 0) AND (FORMAT(tblWTHistory.EstCompDate, 'yyyy-MM-dd') >= '2025-02-10') AND (FORMAT(tblWTHistory.EstCompDate, 'yyyy-MM-dd') <= '2025-02-13') AND (tblWTHistory.WOType = 'WO') AND (tblWTHistory.WONum = '1858974')
ORDER BY tblWTHistory.PartNum, tblWTHistory.WONum, tblWTHistory.OpNum, tblWTHistory.StatusID, tblWTHistory.OperatorID, tblWTHistory.Date

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

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

Phil Parkin SSC Guru Points: 247125 More actions · Answer 1

This is not directly related, but still worth mentioning.

We try to avoid using FORMAT() here because of its poor performance. Assuming tblWTHistory.EstCompDate is a date, please try changing those two lines of code as follows:

     AND tblWTHistory.EstCompDate >= '20250210'
     AND tblWTHistory.EstCompDate <= '20250213'

Makes the code easier to read and should run faster too.

Phil Parkin SSC Guru Points: 247125 More actions · Answer 2

Regarding my 'easier to read' comment, please also consider using table aliases and removing superfluous brackets.

SELECT h.Date
      ,h.EstCompDate
      ,o.BadgeID
      ,o.OperatorName
      ,h.WONum
      ,h.WOType
      ,h.PartNum
      ,h.CellReceivedQty
      ,h.OpNum
      ,wc.WorkCenterName
      ,wt.WorkTypeName
      ,h.WorkQty
      ,s.StatusName
      ,h.ReasonID
      ,r.ReasonName
      ,h.Rework
      ,h.OpComment
FROM tblWTHistory h
    JOIN tblWTReasons r
        ON h.ReasonID = r.ReasonID
    JOIN tblWTStatus s
        ON h.StatusID = s.StatusID
    JOIN tblWorkType wt
        ON h.WorkTypeID = wt.WorkTypeID
    JOIN tblWorkCenter wc
        ON h.WorkCenterID = wc.WorkCenterID
    JOIN tblOperator o
        ON h.OperatorID = o.OperatorID
WHERE h.CellReceivedQty > 0
      AND h.EstCompDate >= '20250210'
      AND h.EstCompDate <= '20250213'
      AND h.WOType = 'WO'
      AND h.WONum = '1858974'
ORDER BY h.PartNum
        ,h.WONum
        ,h.OpNum
        ,h.StatusID
        ,h.OperatorID
        ,h.Date;

EdVassie SSC Guru Points: 60505 More actions · Answer 3

There is a limit on the number of rows that can be displayed in a SSMS pane. This is because SSMS is not intended as a reporting tool.

You can check that the query is returning the same number of rows in SSMS by wrapping your query in SELECT COUNT(*) FROM (your query)

If you want to change the maximum number of rows displayable in a SSMS window there is an option under Settings that controls this. Be aware that allowing mega numbers of rows also requires mega amounts of memory on the machine running SSMS, and mega amount of network bandwidth to populate it.

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

Different number of records returned from SSMS and C# ExecuteReader

Cookies on SQLServerCentral