Crafting Equivalent Functionality to Oracle's CREATE OR REPLACE VIEW in SQL Server

Before modifying the view, you need to verify if it already exists. You can use the IF EXISTS statement to check for the view in the sys.views system table. This table stores information about all views in the database.

IF EXISTS (SELECT * FROM sys.views WHERE name = 'MyView')
BEGIN
  -- The view already exists, so drop it before recreating
  DROP VIEW MyView;
END;

Creating or Altering the View:

Once you've confirmed the existence (or non-existence) of the view, you can proceed with creating or modifying it.

For SQL Server 2016 (SP1) and later versions:

• You can use the CREATE OR ALTER VIEW syntax, which combines both actions into a single statement. This simplifies the process:

CREATE OR ALTER VIEW MyView AS
SELECT *
FROM MyTable
WHERE column1 > 10;

For earlier versions of SQL Server:

• If you're using an older version, you'll need to use separate DROP and CREATE statements:

DROP VIEW MyView;  -- Only if the view already exists

CREATE VIEW MyView AS
SELECT *
FROM MyTable
WHERE column1 > 10;

Example:

Here's a complete example demonstrating both approaches:

-- Check if the view exists (applicable to all versions)
IF EXISTS (SELECT * FROM sys.views WHERE name = 'MyView')
BEGIN
  -- Drop the view if it exists (applicable to all versions)
  DROP VIEW MyView;
END;

-- SQL Server 2016 (SP1) and later versions:
CREATE OR ALTER VIEW MyView AS
SELECT *
FROM MyTable
WHERE column1 > 10;

-- Earlier versions of SQL Server:
-- CREATE VIEW MyView AS -- Uncomment if using an older version
-- SELECT *
-- FROM MyTable
-- WHERE column1 > 10;

Remember:

• Replace MyView with the actual name of your view.
• Modify the SELECT statement to define the desired view logic.