• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 53
  • Last Modified:

MS SQL that is more efficient than mine?

Is there a more efficient way to get the total rows on each returned line than what I am doing in my select?


DECLARE @SalesSiteID INT = 56399;
WITH cte
AS ( SELECT   n.NotificationID ,
              n.NotificationText ,
     FROM     ewNotifications n
              INNER JOIN ewNotificationsToSalesSites nss ON nss.NotificationID = n.NotificationID
     WHERE    n.IsGlobal = 1
              AND n.DateExpired > GETDATE()
              AND nss.SalesSiteID = @SalesSiteID
              AND nss.IsRead = 0
     GROUP BY n.NotificationID ,
              n.NotificationText ,
              n.DateAdded )
SELECT A.BadgeCount ,
       cte.NotificationID ,
       cte.NotificationText ,
FROM   cte
       OUTER APPLY (   SELECT COUNT(*) BadgeCount
                       FROM   cte ) A;

Open in new window

Returns this

I am aware of the COUNT(*) OVER() as Total option but that seems to be excluded from the login when a select is done with a SELECT TOP (n)

The following SQL returns 100 rows of data
But SHOWS 1,754,123  as the total.

SELECT TOP 100 i.IndividualID ,
       COUNT(*) OVER () AS [Total_Rows]
FROM   Individuals i
       JOIN dbo.ewSalesSitesToIndividuals s ON i.IndividualID = s.IndividualID
       JOIN SalesSites ss ON s.SalesSiteID = ss.SalesSiteID
WHERE  ss.BusinessID = 2;

Open in new window

Larry Brister
Larry Brister
  • 2
1 Solution
ste5anSenior DeveloperCommented:
COUNT(*) OVER () should work.. check your second sample.. e.g.

        ID INT ,
        Payload INT

INSERT INTO @Sample ( ID ,
                      Payload )
VALUES ( 1, 1 ) ,
       ( 2, 1 ) ,
       ( 3, 2 ) ,
       ( 4, 2 ) ,
       ( 5, 3 );

WITH Grouped
AS ( SELECT   S.Payload
     FROM     @Sample S
     GROUP BY S.Payload )
SELECT A.BadgeCount ,
FROM   Grouped G
       OUTER APPLY (   SELECT COUNT(*) AS BadgeCount
                       FROM   Grouped ) A;
SELECT   S.Payload ,
         COUNT(*) OVER ()
FROM     @Sample S
GROUP BY S.Payload;

Open in new window

Larry Bristersr. DeveloperAuthor Commented:
Hey Stefan...
I see that.

I am just noting that when you select TOP 2 in the second select
It returns two records but displays 3 total records on each row

I can see where that would be helpful as in
Add the row_number() and you could actually display something like the following when using TOP 100

Row 3 of 10,000 records.  There are 100 Records in this table
Larry Bristersr. DeveloperAuthor Commented:
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now