[RESOLVED] My View Has Changed

[Shaggy Hiker]

I have a table in one SQL Server database, and a view of that table in a second database. There are many columns in the table, but the view asks for only four of them. So, the view looks like this (with names simplified, of course):

SELECT A, B, C, D FROM OtherDatabase.dbo.SomeTable

About a month back, I made some changes to SomeTable. The result of this change was that a new column was added between B and C. I'll call that column Z. So, the table still had columns A, B, C, and D, but the order is now A, B, Z, C, and D.

The view then failed when somebody tried to use it. After studying the problem at some length, I discovered that the view was no longer returning columns A, B, C, and D. In fact, it had changed to this:

SELECT A, B, Z As C, C As D FROM OtherDatabase.dbo.SomeTable

In other words, it was originally taking the first four columns, but taking them by name. The fact that they were the first four columns in the table shouldn't have mattered. By some magic, after the underlying table was changed, the view changed to where it was still taking the first four columns from the table, but since those four columns didn't match the column names, columns 3 and 4 got aliased to what they should have been, which means I still had four columns with the names A, B, C, and D, but column C wasn't even the right datatype (and wasn't column C), and column D wasn't the right data. What a mess.

I tried to reproduce this by using a view of a different table from OtherDatabase, then adding columns to the underlying table, but the view always did what it should do: As long as I still had columns A, B, C, and D in the underlying table, then those were the four columns I got back, regardless of whether new columns were added and regardless of whether those four were moved around.

Can anybody explain why the view got changed the way it did?

[FunkyDexter]

I know I'm teaching you to suck eggs here but:-

Are you DEFINITELY referencing the columns by name in the select clause?

If you script the view, what does it say?

[FunkyDexter]

And is the view definitely selecting from a table? it's not, for example, selecting from another view, table valued function or similar that might itself have been changed?

[Shaggy Hiker]

Nope, it is as I showed. The SQL behind the view is what I wrote with the exception that rather than A, B, C, D, there were the actual column names (and the names for the table and the database were better than what I showed).

One thing I will say is that the view was created probably over a decade back, so I have little memory of how it was created. However, I have no doubt that it was a matter of creating a new view in SSMS, adding the table, and checking the boxes beside the fields I wanted in the view, since that's essentially the only way I've ever created a view.

What I can't say for certain is whether or not the alias names were there prior to the change. It would have been absurd, because that would mean I would have written this:

SELECT A, B, C As C, D As D

since there were aliases only on those two columns. Furthermore, since Z was a column added within the last month, it certainly couldn't have been there originally, so there was never any "somethingElse As C" in there. It would have had to be C As C. That really doesn't seem like something I'd do for no reason, and I can't think of any contortion that could make that reasonable.

The original view was created in SQL Server 2008 R2, while it is now in SQL Server 2012, so there was a migration step. If that had any impact I can't say.

[szlamany]

This might explain some of what you are seeing here

https://www.mssqltips.com/sqlservert...l-server-view/

From that link:

If you're curious about how your view metadata is being stored, you can take a peek at the INFORMATION_SCHEMA.COLUMNS view (view column information is kept there as well as table column information).

[kfcSmitty]

Random guess here, but was the "C" column modified to be "Z" or was "Z" just inserted between the two? If it was a modify statement, then it is possible the DBMS tried to help you out and did some funkiness.

[digitalShaman]

Sounds like a issue with a migrated 2008 view and meta data.
quite scary in fact
could have caused huge issues if the datatype would have matched. brrrr...

[Shaggy Hiker]

Random guess here, but was the "C" column modified to be "Z" or was "Z" just inserted between the two? If it was a modify statement, then it is possible the DBMS tried to help you out and did some funkiness.

Z was just inserted, but I think the second half of your reply was spot on. The DBMS certainly DID try to help out, because it added aliases.

[Shaggy Hiker]

Sounds like a issue with a migrated 2008 view and meta data.
quite scary in fact
could have caused huge issues if the datatype would have matched. brrrr...

That's true. This would have been hell to debug if the added column had been integers. As it was, it crashed the program right away because it was a text field with text in it, and it was being converted to an integer on the assumption that since the column names were the same (thanks to the alias), then the data was what I expected. Had it been an integer, but just a wrong integer, the bug might have lingered for a year or two before it was discovered (it was located such that it might well have slipped through for that long), at which point it would have been an utter nightmare to figure out and correct.

[Shaggy Hiker]

This might explain some of what you are seeing here

https://www.mssqltips.com/sqlservert...l-server-view/

From that link:

While this doesn't explicitly cover the situation, I believe it is the answer. The metadata didn't change when the table changed, and the DBMS was just clever enough to add the aliases to the columns.

[FunkyDexter]

and did some funkiness.

I swear I never touched it.

[Shaggy Hiker]

You get the blame for everything.