What Do You Do in SQL Server to Create or Alter

DROP...CREATE vs ALTER

ALTER will also force a recompile of the entire procedure. Statement level recompile applies to statements inside procedures, eg. a single SELECT, that are recompiled because the underlying tables changes, w/o any change to the procedure. It wouldn't even be possible to selectively recompile just certain statements on ALTER procedure, in order to understand what changed in the SQL text after an ALTER procedure the server would have to ... compile it.

For all objects ALTER is always better because it preserves all security, all extended properties, all dependencies and all constraints.

ALTER vs DROP & CREATE in SQL Server

Create will fail if table exists. Alter will fail if table does not exist.

If you ask why to use Alter if you can drop and create, a few reasons:

• certain permissions already assigned to the object, you would need to regrant permissions if you drop it
• object is probably used by other objects with schema binding

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

Edit: Although this question has been marked as a duplicate, it has still been getting attention. The answer provided by @JaKXz is correct and should be the accepted answer.

---

You'll need to check for the existence of the view. Then do a CREATE VIEW or ALTER VIEW depending on the result.

IF OBJECT_ID('dbo.data_VVVV') IS NULL
BEGIN
    CREATE VIEW dbo.data_VVVV
    AS
    SELECT VCV.xxxx, VCV.yyyy AS yyyy, VCV.zzzz AS zzzz FROM TABLE_A VCV
END
ELSE
    ALTER VIEW dbo.data_VVVV
    AS
    SELECT VCV.xxxx, VCV.yyyy AS yyyy, VCV.zzzz AS zzzz FROM TABLE_A VCV
BEGIN
END

how to have dynamically create & alter in the sql script?

To clarify my comments above, SQL Server 2016 SP1 released a CREATE OR ALTER statement that will either create an object that doesn't already exists or modify the object if it does. This is only allowed on certain objects such as stored procedures, triggers, functions, and views. Tables, indexes, and other objects that are allocated storage cannot be used in by the CREATE OR ALTER statement. Also note that since they're persisted on disk, indexes views are not permitted to be used by this. A basic example of the syntax is below.

CREATE OR ALTER PROCEDURE SP_TestStoredProcedure 
AS 
BEGIN
SELECT
    Column1,
    Column2,
    Column3
FROM YourTable
END

---

Related Topics