create view

Syntax

Changes are in bold.

create [or replace] view [owner.]view_name           
    [(column_name[, column_name]...)]            
    as            
    select [distinct] select_statement           
    [with check option]    

Parameter Changes for create or replace view

• create – creates a view if one does not already exist.
• or replace – replaces an existing view definition without changing any of a view's security attributes.
• view_name – is the name of the view being replaced. Only an existing view can be replaced. The object name and ID remain the same.
• column_name – specifies names to be used as headings as columns in the view. With the or replace clause, you can change column names for the view as follows:
  • If the previous definition of the view contained headings for columns names, then the new definition of the view can omit the headings, or have different headings for column names.
  • If the previous definition of the view did not contain headings for column names, the new definition can contain headings for the view column names.
  • You can change the number of column headings according to the column names in the select_statement.
• distinct – allows duplicate rows; and if the original definition of the view did not specify distinct clause, you can change this parameter so the new view cannot contain duplicate rows.
• select_statement – specify different tables and views in the definition of the view. The columns specified in the target list of the select_statement of the replaced view can be changed to drop or add columns.
• with check option – a view created with the with check option clause can be replaced without this clause, and vice versa.

Examples

Example 1

This example is based on the view Current_Product_List which lists all active products from the table Products. The view is defined as:

create view Current_Product_List as
select ProductID, ProductName   
from Products  
where Discontinued = “No” 

select object_id("Current_Product_List")
-----------       
889051172   

This next command adds the Category column to Current_Product_list using the or replace clause. The object ID of the view remains the same:

create or replace view Current_Product_List as
select ProductID, ProductName, Category   
from Products  
where Discontinued = “No” 

select object_id("Current_Product_List")
-----------       
889051172  

Example 2

In this example, V1—a view that has dependent objects—is replaced:

create table T1(C1 int, C2 int)
create table T2(C1 int, C2 int) 
      
create view V1 as select * from T1   
create view V2 as select * from V1
       
create function foo1   
returns int   
as   
begin     
    declare @number int     
    select @number = C1 from V2   
end   
return @number       
select object_id("V1")       
-----------       
985051514 

create or replace V1 as select * from T2

select * from V2       

select dbo.foo1()       

select object_id("V1")       
-----------       
985051514 

The replaced version of V1 references T2 instead of T1. Both V2 and foo1 will be recompiled. select * from v2 recompiles V2, but not foo1, which is recompiled when the UDF is invoked.

Objects Dependent on Replaced Views

Restrictions

If a view has permissions granted at the column level, then it cannot be replaced and error 2014 is raised. To replace the view, you must first revoke the permissions, or drop and re-create the view.

Permission Changes for create or replace view

Any user who impersonates the view owner through an alias or setuser cannot replace the view.

Changes for replacing a view are in bold.

Auditing Changes for create or replace view

Changes are in bold.