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 Bristersr. DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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:
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.