How to add columns to a view in SQL Server 2005

Question

I have no experience with SQL Server 2005. I've been assigned a task to modify views for adding 4 columns to the view. Is it possible to do this without the column change reflected in the table the view is referring. If I have the columns in the Table, then should I just drop the view and create a new one or is there a way to alter it.

Solution 1

You can use ALTER VIEW to achieve the result you are looking for.

This will just act as dropping the existing view and adding new columns from your new select statement. However, this is better than dropping your existing view and creating a new view because the Alter view will retain the permissions granted to users.

Solution 2

If these 4 columns are calculated based on existing data then you just need to run ALTER VIEW... and add them into the query definition used by the view

ALTER VIEW dbo.foo 
AS
SELECT originalcolumnlist, A+B AS col1, C+D as col2, E+F as col3, G+H as col4
FROM yourtable

You can right click the View definition in Management Studio and "Script View as -> Alter" to see the existing definition.

Solution 3

alter view TheViewName
as
select oldCol_A, oldCol_B, NEWCol_C
from someTable

go

Comments

[Volker]

Fun fact: Your indexes on the view will be dropped too and not be recreated.