Avoid view creation with “SELECT *”

A view is very useful in giving out only the required/restricted information to the users. However see what happens when you create a view with “SELECT * from table” definition.

Let us create this simple table

create table emp_master
(
emp_id int,
emp_name varchar(100),
joined_date date
)

Create a view based on this table using “SELECT *”

Create view emp_master_view as
select * from emp_master;

Now see the column information of this view using sp_columns system procedure

EXEC sp_columns 'emp_master_view'

The result is shown in the below image

Now let us add a new column in the emp_master table

alter table emp_master add address varchar(200)

Now see the column information of view using sp_columns system procedure

EXEC sp_columns 'emp_master_view'

The result is shown in the below image. As you see the address column is not yet updated in the view.

So what is the proper way to update the column information of the view?

Use sp_refreshview system rocedure

EXEC sp_refreshview emp_master_view

After executing the sp_refreshview procedure, let us see the column information

EXEC sp_columns 'emp_master_view'

As you see in the following result, new column is added in the view

Points to consider when creating a view

1 Do not use *. Explicitly type out the column names in the view definition. When new columns are required, alter the view to add them
2 Use schemabinding option when creating view which will prevent the usuage of *

Advertisements