Myujth

My team and I are using Microsoft SQL Server 2014 - Standard Edition (64-bit)

We have created some views as we usually do, they work(ed) normally while we were coding and testing.

Then suddenly, one of our QA noticed that the data in the application was mixed up, for example, description data was in the name field, name was where sex was supposed to be, etc.

We cheched data in the Tables and it was correct, then we checked the view, querying the view like this SELECT * FROM VIEW and realized that the view had the data mixed up.. The next logic step was to check the view queries, for our surprise all the queries were correct. so what was happening?

Well, that is the question, why the data in a view is corrupt or mixed up if the queries within the view are correct and they were working well for long time?

We just ALTERED the view, not modifying anything and that fixed the issue.

But, we need to know the cause of the data corruption, because we don't want to monitor and alter views all the time.

VIEW CODE AS REQUESTED

ALTER VIEW [dbo].[pvvClient] AS 
SELECT *
FROM Table
INNER JOIN Table 2 ON.....

The first thing that came to my mind was that the Table(s) has changed and that raised this behavior, do you think SCHEMABINDING can help to avoid this kind of issues

When you put * in the column list of a view and the underlying tables change your view will not automatically update to include the changed columns. In fact, if you delete a column you can get the data mixed up across columns. This has been discussed and documented many times. Aaron Bertrand has a great article covering this topic.

• Bad habits to kick : using SELECT * / omitting the column list

Moral of the story, avoid using select * unless the select is inside an EXISTS.

It doesn't make sense that simply altering the view without changing any of the code would fix it, but an important point is this part of your view...

select pvtConsumidorFinanciero.*

If this table definition changes... that is, if more columns are added or some are removed, the columns in this view would also change. That is why it is good practice to never select * in a view, especially when querying another view.

Additionally, this table could have the same column names as other tables.

What also could have happened is in your application, you are select * from view. Again, if a DBA changed the view, this could mess up your application, so i would avoid it an explicitly list the columns you want returned in the order you want them returned.

I think this is is also part of the answer:

When you create views it is a good practice to use SCHEMABINDING, this way when you alter the table under the view, you are forced to review your view as well.