How to ROWCOUNT_BIG() value with union all

Question

I have the following query in SQL Server. How do I get the number of rows of previous select query as following format?

Sample Query

select ID, Name FROM Branch
UNION ALL
SELECT ROWCOUNT_BIG(), ''

Sample Output

Normally you would add the row count as part of your display application, not as part of the query. — Dale K, Feb 03 '21 at 19:09

Dale K · Accepted Answer · 2021-02-03T08:23:23.627

2

If you use a CTE you can count the rows and union all together:

with cte as (
    select ID, [Name]
    from dbo.Branch
)
select ID, [Name]
from cte
union all
select count(*) + 1, ''
from cte;

edited Feb 03 '21 at 08:23

answered Feb 03 '21 at 06:55

Dale K

25,246
15
42
71

My select query is in cte is very complex and with many columns. There is lot of data also. So will it be slower? Is there any other alternative solutions. – Mahedee Feb 03 '21 at 07:57
@Mahedee The thing with SQL is it never pays to optimise too early. Give it a try and see how it performs. I *think* any other way is going to be more complex. – Dale K Feb 03 '21 at 08:04
@Mahedee have you tested the performance? There is no reason to think this will be any slower. SQL Server knows how many records there are, so its not like it repeats the query twice. – Dale K Feb 03 '21 at 09:23
there is another issue in my query that is multiple column with same name. CTE doesn't allow it. So, I cannot test my real query. – Mahedee Feb 03 '21 at 10:15
@Mahedee well just alias one of the columns? That should be trivial to fix. – Dale K Feb 03 '21 at 19:09

Venkataraman R · Answer 2 · 2021-02-03T09:01:12.960

1

I think you want to see total count of the select statement. you can do this way.

CREATE TABLE #test (id int)
insert into #test(id)
SELECT 1 

SELECT id from #test
union all
SELECT rowcount_big()

Note: Here, the ID will be implicitly converted to BIGINT datatype, based on the datatype precedence. Read more

edited Feb 03 '21 at 09:01

answered Feb 03 '21 at 08:49

Venkataraman R

12,181
2
31
58

Which essentially (from a performance perspective) is the same as my answer :) – Dale K Feb 03 '21 at 09:17
@DaleK, agree with you. It is cumbersome approach – Venkataraman R Feb 03 '21 at 10:47

score 1 · Answer 3 · answered Feb 03 '21 at 13:29

Presumably, you are running this in some sort of application. So why not use @@ROWCOUNT?

select id, name
from . . .;

select @@rowcount_big;  -- big if you want a bigint

I don't see value to including the value in the same query. However, if the underlying query is an aggregation query, there might be a way to do this using GROUPING SETS.

score 0 · Answer 4 · answered Feb 03 '21 at 14:06

Here are two ways. It's better to use a CTE to define the row set so further table inserts don't interfere with the count. Since you're using ROWCOUNT_BIG() these queries use COUNT_BIG() (which also returns bigint) to count the inserted rows. In order to make sure the total always appears as the last row an 'order_num' column was added to the SELECT list and ORDER BY clause.

drop table if exists #tTest;
go
create table #tTest(
  ID        int not null,
  [Name]    varchar(10) not null);

insert into #tTest values
(115, 'Joe'),
(116, 'Jon'),
(117, 'Ron');

/* better to use a CTE to define the row set */
with t_cte as (
    select * 
    from #tTest)
select 1 as order_num, ID, [Name] 
from t_cte
union all
select 2 as order_num, count_big(*), '' 
from t_cte
order by order_num, ID;

/* 2 separate queries could give inconsistent result if table is inserted into */
select 1 as order_num, ID, [Name] 
from #tTest
union all
select 2 as order_num, count_big(*), '' 
from #tTest
order by order_num, ID;

Both return

order_num   ID  Name
1           115 Joe
1           116 Jon
1           117 Ron
2           3

How to ROWCOUNT_BIG() value with union all

4 Answers4