Most efficient way to return the totalcount along

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Most efficient way to return the totalcount along

Author

Topic

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2014-04-02 : 06:59:33

I have following query to return the page data

declare @pageSize INT = Null
,@pageNumber INT = Null
declare @totalcount int
set @pageSize =15
set @pageNumber = 1

select *
from (SELECT ROW_NUMBER()
OVER(
ORDER BY dbo.coordinator_event.CoordinatorId) AS RowNumberForPaging
,dbo.coordinator_event.EventId
,dbo.coordinator_event.NumberOfParticipantAllowed
,dbo.coordinator_event.RegistrationClosingDate
,dbo.coordinator_event.CoordinatorId
,dbo.royalevents_royalevent.title_en AS EventTitleEnglish
,dbo.royalevents_royalevent.StartDateTime
,Count(dbo.event_attendee_registration.FullName) ParticipantRegistration
, COUNT(*) OVER(PARTITION BY 1) as TotalRows
FROM dbo.coordinator_event
inner JOIN dbo.royalevents_royalevent
ON dbo.coordinator_event.EventId = dbo.royalevents_royalevent.base_id
left JOIN dbo.event_attendee_registration
ON dbo.coordinator_event.CoordinatorId = dbo.event_attendee_registration.CoordinatorId
and royalevents_royalevent.base_id = event_attendee_registration.EventId
where coordinator_event.CoordinatorId = 3
group by dbo.coordinator_event.EventId
,dbo.coordinator_event.NumberOfParticipantAllowed
,dbo.coordinator_event.RegistrationClosingDate
,dbo.coordinator_event.CoordinatorId
,dbo.royalevents_royalevent.title_en
,dbo.royalevents_royalevent.StartDateTime
) MyTable
WHERE RowNumberForPaging >= ( ( ( @pageSize * @pageNumber ) - @pageSize ) + 1 )
AND RowNumberForPaging <= ( @pageSize * @pageNumber )
ORDER BY CoordinatorId
I want's two thing with it. One thing is about how i can easily return @totalcount as separate parameter instead of part of the result set and secondly if there is a way i can optimize above row_number query to achieve paging

Kamran Shahid
Principle Engineer Development
(MCSD.Net,MCPD.net)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources