limitation in number of UNIONS?

[Olaf Doschke]

Is there a limitation in the number of unions MSSQL 2012 supports?

I get no error executing a query with 90 tables in the form of

Select sum(cnt) as total, guid, min(Tablename) as table1, max(Tablename) as table2 FROM
(
SELECT guid, Count(*) as cnt, 'table1' as Tablename From table1 group by guid
Union All
...
Union All
SELECT guid, Count(*) as cnt, 'table90' as Tablename From table90 group by guid
) as temp
group by temp.guid
having sum(cnt)>1
order by 3

The semantic of this is not so important, but as you can guess I try to find out double guid values and where they are present. Mostly in the same table, but also in others.
I started this investigation with finding a single case for a certain guid value, but that value does not turn up in the full query. It does turn up, if I limit the query to perhaps 5 unions, though.

There might be a more elegant way in doing this overall, nevertheless I'm stumped by not getting any error still not the expected result, if there are too many unions. Seems like a bug to me.

Also perhaps good to know is I came from simpler single sub queries in the form of SELECT guid, 'tableN' as Tablename From tableN, only doing the group by on the outer level. I thought perhaps even Union All does filter out double records in some conditions and doing a pregrouping per table might help, but it doesn't.

Also: Real table names are not simply table1 to table90, it's all tables of a database as queried by information schema.

Bye, Olaf.

 

[Olaf Doschke]

I found the culprit. I was expecting 4 copies of a certain value in one table. It turns out there is a 5th record with that guid in another table, a version history table.
I was browsing the result with table1=table2 as a certain table name as this case was about repeated guid values in that same table. With min(Tablename) and max(Tablename) I also wanted to find keys being copied to other tables and didn't thought of the case guid values might have both been copied into the same and another table.

So a repeated value of guidX was in both tableN and tableM in this manner in the union:
guidX, tableN
guidX, tableN
guidX, tableN
guidX, tableN
guidX, tableM

Makes a total count of 5 instead of the expected 4 and differing min(Tablename) and max(Tablename) of course.

Bye, Olaf.