How to make CREATE OR REPLACE VIEW work in SQL Server? [duplicate]

CREATE OR REPLACE VIEW doesn't seem to work in SQL Server. So how do I port CREATE OR REPLACE VIEW to work on SQL Server?

This is what I'm trying to do:

CREATE OR REPLACE VIEW data_VVVV AS 
SELECT 
    VCV.xxxx,
        VCV.yyyy AS yyyy,
        VCV.zzzz AS zzzz
FROM 
TABLE_A
;

Any ideas?

Solution 1:

Borrowing from @Khan's answer, I would do:

IF OBJECT_ID('dbo.test_abc_def', 'V') IS NOT NULL
    DROP VIEW dbo.test_abc_def
GO

CREATE VIEW dbo.test_abc_def AS
SELECT 
    VCV.xxxx
    ,VCV.yyyy AS yyyy
    ,VCV.zzzz AS zzzz
FROM TABLE_A

MSDN Reference

Solution 2:

Here is another method, where you don't have to duplicate the contents of the view:

IF (NOT EXISTS (SELECT 1 FROM sys.views WHERE name = 'data_VVV'))
BEGIN
    EXECUTE('CREATE VIEW data_VVVV as SELECT 1 as t');
END;

GO

ALTER VIEW data_VVVV AS 
    SELECT VCV.xxxx, VCV.yyyy AS yyyy, VCV.zzzz AS zzzz FROM TABLE_A ;

The first checks for the existence of the view (there are other ways to do this). If it doesn't exist, then create it with something simple and dumb. If it does, then just move on to the alter view statement.