Q: SQL - Better approach for total records count I have written a query where I am fetching the records as well as total records count. I have two approaches in mind but can't figure out which one is better and less expensive
First with multiple cte's
;with allRecords as 
(
   -- query fetching all the records with many joins
),
recordsCount as 
(
    select count(*) as RecordsCount from allRecords
)
select * from allRecords, recordsCount

Second approach, using cte and cross join
;with allRecords as 
(
   -- query fetching all the records with many joins
)
select *, c.totalRecords from allRecords 
cross join (select count(*) as totalRecords from allRecords) c

I personally felt very minor better performance in second approach.
So in first approach, does second cte is called everytime for all the records of first cte or it just runs once when first cte finishes? Then I think there is no problem using first approach.
Suggestions please!

A: A this approach would be to use COUNT with OVER:
SELECT *, COUNT(*) OVER()
FROM --- rest of the query here


A: Idea -1
sp_MSforeachtable 'sp_spaceused "?"'

Idea -2
sp_MSforeachtable 'select ''?'' Tablename, count(*) ''Rows'' from ?';

Idea -3
SELECT object_name(id), rows
FROM sysindexes
WHERE indid IN (0, 1)

