Today there was an interesting issue in our MS SQL Server database view.
There are several base tables. One is sys_user an the others join in some additional data.
The (beginning of) SQL view definition looks like this:
ALTER VIEW [dbo].[V_Usage] AS
SELECT u.*,
case when assexcl.sys_id IS NULL THEN asslookup.home_office ELSE assexcl.home_office END [Home Office],
case when assexcl.sys_id IS NULL THEN asslookup.assigned_group ELSE assexcl.assigned_group END [Assigned Group],
case when assexcl.sys_id IS NULL THEN asslookup.drs_hub ELSE assexcl.drs_hub END [DRS Hub],
x.request_sys_id, x.request_pa_name, x.u_allocation request_allocation, x.u_negotiated_deadline, x.u_research_time, x.u_qa_time_drs, x.u_assignment_group, x.u_utilization_category
FROM sys_user u
LEFT outer join (...
The select u.* brings in all fields of the sys_user table and some other fields are also shown per user which is queried in the lower part.
After adding a field in the sys_user table we had the issues that all content in the last fields was shiftet one column to the right.
[Home Office] did contain the last field of the sys_user table, [Assigned Group] did contain the content of [Home Office] etc.
First I would like to understand the issue. I think it is due to internal compilation and referencing.
And second I would like to have a solution to make that not happen again. I would prefer not reference all fields directly in the view since it was intentional that all sys_user fields should be in the view.
Is there a kind of flag or db hint to keep those view correct even when base tables get fields added?
Any information appreciated.
Having spent lots and lots of time (once upon a time) debugging problems cause by such queries, I strongly advise you to never use * in views.
That said, you can prevent changes to the underlying tables using SCHEMABINDING.
Binds the view to the schema of the underlying table or tables. When SCHEMABINDING is specified, the base table or tables cannot be modified in a way that would affect the view definition.
As the documentation says, this prevents changes to the other tables unless the view is recreated. It does not automatically change the view, but it does prevent damaging changes from occurring.
You might also investigate WHY table structures are changing in -- what is presumably -- a production database. That seems like a bad design.