Adding a ROWID() for tables that are linked by UNION ALL

ROW_NUMBER() inserts a virtual ROWID for your query. But how can we create a ROWID for two tables continuously when they are linked using UNION ALL?

Here is How:

(SELECT PartsID AS ItemID, PartsDesc as ItemDesc, ROW_NUMBER()OVER (ORDER
BY PartsID)FROM dbo.Parts)

UNION ALL

SELECT AssemblyID as ItemID, AssemblyDesc,count(PartsID) + ROW_NUMBER() OVER (ORDER BY AssemblyID)FROM dbo.[Assembly],dbo.Parts group by
AssemblyID, AssemblyDesc

Here is how it works:

1. The first Select Statement inserts a ROW ID for the Parts Table
2. The Second Select Statement uses two tables (FROM dbo.[Assembly],dbo.Parts). JOINING two tables using From Table1, Table2 multiplies the number of records.
3. The Second Select Statement was then Grouped By Fields needed for Assembly. This will select the distinct records needed for the query.
4. count(PartsID) gives the last number of the first query then add it to the ROW_NUMBER() of the second select statement to continue the numbering.

Another way to optimize this is to create a view for the UNION ALL statement then treat it as a single table in your ROW_NUMBER statement.