פבר25
Written by:
ronen ariely
25/02/2023 14:18 
Introduction
Following the inquiry in the forums, it seems that there is a bug when we are using sp_refreshsqlmodule on a stored procedure which call HIDDEN system-versioned temporal tables with hidden columns. In this post I will reproduce the issue and present a simple explanation. For solution we will need Microsoft team to fix the bug.
The short explanation is that this issue related to the HIDDEN columns in the system-versioned temporal tables.
Credit to find the but and ask about it in the forum: Aswin
The issue was reported at Microsoft Feedback system here.
Reproduce the issue and a short discussion
The short explanation is that this issue related to the HIDDEN columns in the system-versioned temporal tables.
For the discussion and for monitoring, we can **reproduce the issue**:
CREATE TABLE dbo.RonenA
(
C1 int NOT NULL PRIMARY KEY CLUSTERED
, C2 nvarchar(100) NOT NULL
, [ValidFrom] datetime2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL
, [ValidTo] datetime2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL
, PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.RonenAHistory));
GO
INSERT RonenA (C1,C2) values (1,'Ronen')
GO
INSERT RonenA (C1,C2) values (2,'Ariely')
GO
UPDATE RonenA SET C1= 3 where c2 = 'Ronen'
GO
Since the columns `[ValidFrom]` and `[ValidTo]` are HIDDEN, using `SELECT *` will not returns these columns in the original table `RonenA`, but the same query from the history table returns all columns.
Therefore the following query will return error:
SELECT * FROM RonenA
UNION
SELECT * FROM RonenAHistory
GO
The solution is to explicitly add the hidden columns in the select
SELECT * , [ValidFrom],[ValidTo] FROM RonenA
UNION
SELECT * FROM RonenAHistory
GO -- OK
Now, we can create SP from this query:
CREATE PROCEDURE RonenA_SP as
SELECT * , [ValidFrom],[ValidTo] FROM RonenA
UNION
SELECT * FROM RonenAHistory
GO
EXEC RonenA_SP
GO
All the above bahaves normal, but the issue is when we want to set the `sp_refreshsqlmodule` on the SP
exec sp_refreshsqlmodule N'RonenA_SP'
GO
-- ERROR: All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.
It seems like `sp_refreshsqlmodule` checks all the columns when we use "`SELECT *`" including the HIDDEN columns.
Therefore by using the query "`SELECT * , [ValidFrom],[ValidTo] FROM VerHIDDEN`" it actually "think" that we have 6 columns in the result set (four from the star and two from the explicit column names.
This lead to the error since the second query from the history table includes only four columns.
Bypass the issue
Do not use HIDDEN columns or simply use explicit columns names.
Conclusions
**Is this a bug?** YES!
in my opinion it is a bug, since the behaviour is not consistency with the idea of using the `sp_refreshsqlmodule` for cases we use implicit list of columns (using start *) and as such it does not support HIDDEN columns in the system-versioned temporal tables
Cleaning
ALTER TABLE [dbo].RonenA SET ( SYSTEM_VERSIONING = OFF)
GO
DROP TABLE [dbo].RonenA
GO
DROP TABLE [dbo].RonenAHistory
GO