The Perils of Using Asterisks in SQL Views

Asterisks (*) are extremely useful and widely used in writing SQL.  However, in a SQL Server view, the asterisk can cause some unusual errors and/or missleading data.
The perils of asterisks comes when one of the underlying tables is altered to add a new column, the view does something quite unexpected.  The view will still return the same number (and names) of columns, but the new column's data will be returned in the column to its immediate right, and all other columns to the right will display data from the column to its left.  The very right most column will have the same name, but its data will not be returned anywhere.  Because columns are returning data for other columns, the data types are no longer correct.  An INT can display a CHAR() etc.

Here is an example of a view where the first 2 columns are coming from t1 (using *) and CustomerBalance, CustomerDOB and CustomerAddress are coming from t2.

Here is the same view after adding a VARCHAR column to t1.  Notice how CustomerBalance now has character data, CustomerDOB has balance, CustomerAddress has DOB and the customer's address appears nowhere!  This character data in CustomerBalance is from the new column.

Here is the same view after executing sp_refreshview.  Notice that the new column is now appearing and all columns contain the correct data.

My recommendation is to avoid using asterisks in views to return all columns from a table, instead specify the columns required.  This is mildly annoying, but much less annoying than getting address data in a balance column.

However another possible solution is to use sp_refreshview which will update the metadata for the view (and include the extra columns in the view). 

Note, I have actually used 'SELECT * FROM v1' in the demonstration below, but the same results would be returned even if I had used 'SELECT t1PK, CustomerName, CustomerBalance, CustomerDOB, CustomerAddress FROM v1'

Also note, sp_recompile does not fix the issue, nor does a server reboot.
To reproduce the issue execute the following script

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[t1]') AND type in (N'U'))
DROP TABLE [dbo].[t1];
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[t2]') AND type in (N'U'))
DROP TABLE [dbo].[t2];
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[v1]') AND type in (N'V'))
DROP VIEW [dbo].v1;
GO

create table t1 (t1PK int, CustomerName VARCHAR(50));
create table t2 (t2PK int, CustomerBalance DECIMAL(13,2), CustomerDOB DATE, CustomerAddress VARCHAR(100));
go
insert into t1 select 1, 'Richard Lees';
insert into t1 select 2, 'Richie McCaw';
insert into t2 select 1, -500, '20580221', 'Sydney, Australia';
insert into t2 select 2, +1000, '19801231', 'Christchurch, New Zealand';
go

create view v1 as
 select t1.*,t2.CustomerBalance,CustomerDOB,CustomerAddress from t1
 inner join t2 on t1PK=t2PK;
go
SELECT 'View is OK at this stage' _;
select * from v1;
go
alter table t1 add t1_comments varchar(20) not null default 'Da di da';
go
SELECT 'View is NOK.  The column t2PKint now contains char data' _;
select * from v1;
go
sp_refreshview v1;
go
SELECT 'View is OK again' _;
select * from v1;