你怎么在SQL Server做创建或更改？(What do you do in SQL Server

2019-07-18 06:25发布

站内文章 / 后端开发

8 0

女 | 书童

私信

这一年是2009年和SQL Server没有创建或修改/替换。这是我做的，而不是。

IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES 
           WHERE ROUTINE_NAME = 'SynchronizeRemoteCatalog' 
             AND ROUTINE_SCHEMA = 'dbo' 
             AND ROUTINE_TYPE = 'PROCEDURE')
 EXEC ('DROP PROCEDURE dbo.SynchronizeRemoteCatalog')

CREATE PROCEDURE dbo.SynchronizeRemoteCatalog
AS BEGIN
    -- body
END

对于触发器，你必须依靠专有的系统视图。

这是在此期间，最接受的传统？

编辑：作为n8wrl建议，在官方消息显示，这个功能是不是一个高优先级。因此这个问题。

Answer 1:

本文就有关SQL服务器删除对象时丢失权限的好点。

温馨提示“N”窍门- T-SQL -在创建或更改存储过程一种优雅的方式一去，方便维修

因此，这里是它保留权限的方法：

IF OBJECT_ID('spCallSomething') IS NULL
    EXEC('CREATE PROCEDURE spCallSomething AS SET NOCOUNT ON;')
GO

ALTER PROCEDURE spCallSomething ... 
--instead of DROP/CREATE

也适用于功能，只需更换PROCEDURE与FUNCTION在上面的代码。

考虑做这种方式的另一个原因是宽容失败。假设你的DROP成功，但你的CREATE失败 - 你有一个破碎的DB结束。使用ALTER方法，你最终会与旧版本的对象。

Answer 2:

这一年是2009年和SQL Server没有创建或修改/替换。

这一年是2016和它现在有DIE（降是否存在），在SQL Server 2016 RTM以及CREATE OR ALTER （2016年SP1中引入）。

服用Drop If Exists首先围绕需要重新申请使用这种方法的权限仍然适用的注意事项。实施例的语法是

DROP PROCEDURE IF EXISTS dbo.SynchronizeRemoteCatalog

GO

CREATE PROCEDURE dbo.SynchronizeRemoteCatalog
AS
  BEGIN
      BODY:
  END 

GO

/*TODO: Reapply permissions*/

CREATE OR ALTER保留的权限。实施例的语法是

 CREATE OR ALTER PROCEDURE dbo.SynchronizeRemoteCatalog
 AS
 BEGIN
   BODY:
 END

在相应的MSSQL虎团队博客文章解释

创建或更改可以在可编程的对象，例如可以使用：
存储过程（包括本地编译）
函数（Transact-SQL，包括本地编译）
TRIGGERS
VIEWS
但不能用在：
需要存储（表，索引和索引视图）对象
CLR用户定义函数
弃用可编程对象（RULE和DEFAULT）
非可编程对象（如CREATE ASSEMBLY，CREATE TABLE或CREATE - SCHEMA）。在这些对象中，语法创建和修改是从语法和可用性的角度来看非常不同。

Answer 3:

每次开发人员编写IF EXISTS(...) DROP一个小海豹被杵。你应该知道到底什么是数据库和升级脚本应该做的创建或更改为appropiate，根据您的应用程序架构的当前版本：版本控制和数据库。

Answer 4:

我们遇到的情况是，我们需要更新远程站点，但我们没有DROP权限。到现在为止，我们一直在使用“删除并创建”建成SSMS 2008 R2脚本，但现在我们需要改变。我们创建了三个模板，我们适当的修改脚本，上面落的时候，我们需要更新一个存储过程或函数：

—- Stored Procedure
IF OBJECT_ID('[dbo].[<Name_Of_Routine, , >]') IS NULL
EXEC('CREATE PROCEDURE [dbo].[<Name_Of_Routine, , >] AS SET NOCOUNT ON;')
EXEC('GRANT EXECUTE ON [<Name_Of_Routine, , >] TO Public AS dbo;')
GO

—- Scalar Function
IF OBJECT_ID('[dbo].[<Name_Of_Routine, , >]') IS NULL
EXEC('CREATE FUNCTION [dbo].[<Name_Of_Routine, , >] (@i INT) RETURNS INT AS BEGIN RETURN 0 END;')
EXEC('GRANT EXECUTE ON [<Name_Of_Routine, , >] TO Public AS dbo;')
GO

—- Table-based Function
IF OBJECT_ID('[dbo].[<Name_Of_Routine, , >]') IS NULL
EXEC('CREATE FUNCTION [dbo].[<Name_Of_Routine, , >] (@i INT) RETURNS @O TABLE(i INT) AS BEGIN INSERT INTO @O SELECT 0 RETURN END;')
GO

任何特殊权限得到脚本在每个CREATE后（表函数不能分配权限）。在此之后，ALTER不改变它，如果他们添加或修改权限，它们依然存在。这样做，这样，它是一个容易的任务来复制函数或存储过程的名称，并使用模板参数更换，以自动化这些小脚本的完成。

现在，我希望在微软的好乡亲将在这他们加上“脚本___为”列表，或者给我们创造我们自己的，使得该脚本自带“烤入”的能力

您可能要抛出一些重量在SQL Server的反馈词条背后： https://connect.microsoft.com/SQLServer/feedback/details/344991/create-or-alter-statement 。这似乎是仍然可以访问公开的几个之一，他们指出，他们“已经开始为此进行可行性审查，以决定是否能在不久的将来出货这一点。” 将更多的声音，就越有可能会出现这种情况！

（更新：现在还使用触发器和视图下面的代码）

-- Triggers
IF OBJECT_ID('[dbo].[<Name_Of_Trigger, , >]') IS NULL -- Check if Trigger Exists
    EXEC('CREATE TRIGGER [dbo].[<Name_Of_Trigger, , >] ON [<Name_Of_Table, , >] AFTER UPDATE AS SET NOCOUNT ON;') -- Create dummy/empty SP
GO

-- Views
IF OBJECT_ID('[dbo].[<Name_Of_View, , >]') IS NULL -- Check if View Exists
    EXEC('CREATE VIEW [dbo].[<Name_Of_View, , >] AS SELECT 1;') -- Create dummy/empty View
GO

Answer 5:

我会使用OBJECT_ID(...) IS NOT NULL一个DROP前。

对象标识符必须是唯一的，因此它可以在不使用系统表：

CREATE TRIGGER dbo.ExistingTable ON dbo.AnotherTable FOR UPDATE
AS 
SET NOCOUNT ON
GO

给

Msg 2714, Level 16, State 2, Procedure MetaClass, Line 3
There is already an object named ExistingTable ' in the database.

我通常使用ALTER因为我们是如何与源代码控制等方面的工作

Answer 6:

这基本上做到这一点的方式，是的。我只是想知道，如果你有一个特别的理由使用“EXEC”的方法：

IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'SynchronizeRemoteCatalog' AND ROUTINE_SCHEMA = 'dbo' AND ROUTINE_TYPE = 'PROCEDURE')
    EXEC ('DROP PROCEDURE dbo.SynchronizeRemoteCatalog')

为什么不干脆：

IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'SynchronizeRemoteCatalog' AND ROUTINE_SCHEMA = 'dbo' AND ROUTINE_TYPE = 'PROCEDURE')
    DROP PROCEDURE dbo.SynchronizeRemoteCatalog

???

对于触发器，有sys.triggers 。这些都是在“SYS”模式系统目录视图 - 不严格或直接表，真的。

渣

Answer 7:

我总是alter我的对象，因为drop是非常糟糕的做法，可以让你的数据库处于不良状态。如果对象不能创建（24/7分贝！），还有什么其他海报提及的摧毁权限。

如Sublime，Atom和VS代码编辑器可以让你做的代码段为这些到模板快速GEN-你的骨骼脚本。 SQL 2016现在终于支持DROP IF EXISTS建造，但它仍然从错误的方向接近-这一切都是一个drop/create ，而不是一次create在遥远的过去，并alter从此。此外，我试图让我的头尽可能短，很可能会工作，所以我没有得到比任何票友create proc dbo.myproc as作为create存根。

浏览次数：

if objectproperty(object_id('dbo.myview'), 'IsView') is null begin
    exec('create view dbo.myview as select 1 c')
end
go
alter view dbo.myview as
    -- select *
    -- from table
go

特效：

if objectproperty(object_id('dbo.myproc'), 'IsProcedure') is null begin
    exec('create proc dbo.myproc as')
end
go
alter procedure dbo.myproc as
    set nocount on
    -- Add the stored proc contents here...
go

UDF（标量）：

if objectproperty(object_id('dbo.myudf'), 'IsScalarFunction') is null begin
    exec('create function dbo.myudf returns int as begin return null end')
end
go
alter function dbo.myudf(@s varchar(100)) returns int as
begin
    -- return len(@s)
end
go

UDF（表格）：

if objectproperty(object_id('dbo.myudf'), 'IsTableFunction') is null begin
    exec('create function dbo.myudf returns @t table(x int) as begin return end')
end
go
alter function dbo.myudf(@s varchar(100))
    returns @result table (
        -- Columns returned by the function
        id int identity(1, 1) primary key not null
        ,result varchar(100) null
    )
begin
    return
end
go

Answer 8:

看起来就像是一个，而关：链接文本

我典型的脚本：

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'ig_InsertDealer' AND type = 'P')
    DROP PROC dbo.ig_InsertDealer
GO 
CREATE PROCEDURE dbo.ig_InsertDealer
...
GO
GRANT EXECUTE ON dbo.ig_InsertDealer TO ...
GO

Answer 9:

我会用任何根据上下文：我最初的建造或重大重构脚本将使用支票/删除/创建，纯维护脚本使用alter。

Answer 10:

我更喜欢CREATE-ALTER过的方法（不是语法） DROP-CREATE ，原因有二：

权限（有DROP-CREATE你必须重新制作图片）
OBJECT_ID（改变对象不会改变它）

实施例DROP-CREATE ：

--Initial creation:
CREATE PROCEDURE dbo.my_proc
AS
SELECT *
FROM dbo.a
WHERE i < 10;
GO

SELECT OBJECT_ID('dbo.my_proc');
GO


-- Recreating
DROP PROCEDURE IF EXISTS dbo.my_proc;
GO

CREATE PROCEDURE dbo.my_proc
AS
-- some meaningless comment
SELECT *
FROM dbo.a
WHERE i < 10;
GO

SELECT OBJECT_ID('dbo.my_proc');
GO

DB小提琴

正如我们所看到的object_id发生了变化。

实施例2： CREATE-ALTER

-- Initial creation
CREATE PROCEDURE dbo.my_proc2
AS
SELECT *
FROM dbo.a
WHERE i < 10;
GO

SELECT OBJECT_ID('dbo.my_proc2');
GO

-- Altering
CREATE OR ALTER PROCEDURE dbo.my_proc2
AS
-- some meaningless comment
SELECT *
FROM dbo.a
WHERE i < 10;
GO

SELECT OBJECT_ID('dbo.my_proc2');
GO

DB小提琴

在这种情况下的object_id保持不变。

示例场景时，这可能会导致一些问题。让我们假设我们使用SQL Server 2016查询储存和强制使用存储过程的特定查询计划。

DROP-CREATE

USE T1;
GO
-- make sure that Query Store is READ_WRITE 
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[a]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[a](
    [i] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [g] [uniqueidentifier] NULL,
    [z] VARCHAR(10)
);
END
GO

-- populate table (15k records)
INSERT INTO dbo.a(g, z)
SELECT NEWID(), number
FROM (SELECT CAST([key] AS INT) AS number 
    FROM OPENJSON( '[1' + REPLICATE(',1',3000-1)+']')
    ) AS num
GO 5

-- initial creation
CREATE PROCEDURE dbo.my_proc
AS
SELECT *
FROM dbo.a
WHERE z LIKE '12%'
AND 1 = (SELECT 1);
GO

-- Clustered Index Scan
EXEC dbo.my_proc;

EXEC sp_query_store_flush_db; 

SELECT qsq.query_id,
    qsq.query_text_id,
    qsq.context_settings_id,
    qsq.[object_id],
    OBJECT_NAME(qsq.[object_id]) AS [object_name],
    qsp.is_forced_plan,
    qsqt.query_sql_text,
    qsrs.count_executions,
    CAST(qsp.query_plan AS XML) AS sql_query_plan
FROM sys.query_store_query qsq
JOIN sys.query_store_query_text qsqt
ON qsq.query_text_id = qsqt.query_text_id
JOIN sys.query_store_plan qsp
ON qsq.query_id= qsp.query_id
JOIN sys.query_store_runtime_stats qsrs
ON qsrs.plan_id = qsp.plan_id
WHERE query_sql_text LIKE '%dbo.a%'
AND qsq.[object_id] <> 0
ORDER BY qsq.query_id;
GO
--dc1

-- creating index
CREATE NONCLUSTERED INDEX IX_dbo_a_z
ON dbo.a([z] ASC) INCLUDE ([i], [g]);
GO

-- index seek
EXEC dbo.my_proc;

EXEC sp_query_store_flush_db; 

SELECT qsq.query_id,
    qsq.query_text_id,
    qsq.context_settings_id,
    qsq.[object_id],
    OBJECT_NAME(qsq.[object_id]) AS [object_name],
    qsp.is_forced_plan,
    qsqt.query_sql_text,
    qsrs.count_executions,
    CAST(qsp.query_plan AS XML) AS sql_query_plan
FROM sys.query_store_query qsq
JOIN sys.query_store_query_text qsqt
ON qsq.query_text_id = qsqt.query_text_id
JOIN sys.query_store_plan qsp
ON qsq.query_id= qsp.query_id
JOIN sys.query_store_runtime_stats qsrs
ON qsrs.plan_id = qsp.plan_id
WHERE query_sql_text LIKE '%dbo.a%'
AND qsq.[object_id] <> 0
ORDER BY qsq.query_id;

-- forcing plan GUI, clustered scan
-- dc3

EXEC sp_query_store_flush_db; 
SELECT qsq.query_id,
    qsq.query_text_id,
    qsq.context_settings_id,
    qsq.[object_id],
    OBJECT_NAME(qsq.[object_id]) AS [object_name],
    qsp.is_forced_plan,
    qsqt.query_sql_text,
    qsrs.count_executions,
    CAST(qsp.query_plan AS XML) AS sql_query_plan
FROM sys.query_store_query qsq
JOIN sys.query_store_query_text qsqt
ON qsq.query_text_id = qsqt.query_text_id
JOIN sys.query_store_plan qsp
ON qsq.query_id= qsp.query_id
JOIN sys.query_store_runtime_stats qsrs
ON qsrs.plan_id = qsp.plan_id
WHERE query_sql_text LIKE '%dbo.a%'
AND qsq.[object_id] <> 0
ORDER BY qsq.query_id;
-- dc4

-- Clustered Index Scan
EXEC dbo.my_proc;

EXEC sp_query_store_flush_db; 
SELECT qsq.query_id,
    qsq.query_text_id,
    qsq.context_settings_id,
    qsq.[object_id],
    OBJECT_NAME(qsq.[object_id]) AS [object_name],
    qsp.is_forced_plan,
    qsqt.query_sql_text,
    qsrs.count_executions,
    CAST(qsp.query_plan AS XML) AS sql_query_plan
FROM sys.query_store_query qsq
JOIN sys.query_store_query_text qsqt
ON qsq.query_text_id = qsqt.query_text_id
JOIN sys.query_store_plan qsp
ON qsq.query_id= qsp.query_id
JOIN sys.query_store_runtime_stats qsrs
ON qsrs.plan_id = qsp.plan_id
WHERE query_sql_text LIKE '%dbo.a%'
AND qsq.[object_id] <> 0
ORDER BY qsq.query_id;
-- dc5

/* MAIN PART  - DROP - RECREATE */
DROP PROCEDURE IF EXISTS dbo.my_proc;
GO

CREATE PROCEDURE dbo.my_proc
AS
-- some meaningless comment added by developer
SELECT *
FROM dbo.a
WHERE z LIKE '12%'
AND 1 = (SELECT 1);
GO

/* MAIN PART END */

-- Index Seek
EXEC dbo.my_proc;

EXEC sp_query_store_flush_db; 
SELECT qsq.query_id,
    qsq.query_text_id,
    qsq.context_settings_id,
    qsq.[object_id],
    OBJECT_NAME(qsq.[object_id]) AS [object_name],
    qsp.is_forced_plan,
    qsqt.query_sql_text,
    qsrs.count_executions,
    CAST(qsp.query_plan AS XML) AS sql_query_plan
FROM sys.query_store_query qsq
JOIN sys.query_store_query_text qsqt
ON qsq.query_text_id = qsqt.query_text_id
JOIN sys.query_store_plan qsp
ON qsq.query_id= qsp.query_id
JOIN sys.query_store_runtime_stats qsrs
ON qsrs.plan_id = qsp.plan_id
WHERE query_sql_text LIKE '%dbo.a%'
AND qsq.[object_id] <> 0
ORDER BY qsq.query_id;
-- object_id in query store is NULL
-- is_forced_plan flag is ignored !!!

第一次执行：

添加索引和执行：

强制计划：

另一种执行：

之后DROP-CREATE ：

CREATE - ALTER

USE T2;
GO
-- make sure that Query Store is READ_WRITE 
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[a]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[a](
    [i] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [g] [uniqueidentifier] NULL,
    [z] VARCHAR(10)
);
END
GO

-- populate table (15k records)
INSERT INTO dbo.a(g, z)
SELECT NEWID(), number
FROM (SELECT CAST([key] AS INT) AS number 
    FROM OPENJSON( '[1' + REPLICATE(',1',3000-1)+']')
    ) AS num
GO 5

-- initial creation
CREATE PROCEDURE dbo.my_proc
AS
SELECT *
FROM dbo.a
WHERE z LIKE '12%'
AND 1 = (SELECT 1);
GO

-- Clustered Index Scan
EXEC dbo.my_proc;

EXEC sp_query_store_flush_db; 
SELECT qsq.query_id,
    qsq.query_text_id,
    qsq.context_settings_id,
    qsq.[object_id],
    OBJECT_NAME(qsq.[object_id]) AS [object_name],
    qsp.is_forced_plan,
    qsqt.query_sql_text,
    qsrs.count_executions,
    CAST(qsp.query_plan AS XML) AS sql_query_plan
FROM sys.query_store_query qsq
JOIN sys.query_store_query_text qsqt
ON qsq.query_text_id = qsqt.query_text_id
JOIN sys.query_store_plan qsp
ON qsq.query_id= qsp.query_id
JOIN sys.query_store_runtime_stats qsrs
ON qsrs.plan_id = qsp.plan_id
WHERE query_sql_text LIKE '%dbo.a%'
AND qsq.[object_id] <> 0
ORDER BY qsq.query_id;
-- ca1
GO

-- creating index
CREATE NONCLUSTERED INDEX IX_dbo_a_z
ON dbo.a([z] ASC) INCLUDE ([i], [g]);
GO

-- index seek
EXEC dbo.my_proc;

EXEC sp_query_store_flush_db; 
SELECT qsq.query_id,
    qsq.query_text_id,
    qsq.context_settings_id,
    qsq.[object_id],
    OBJECT_NAME(qsq.[object_id]) AS [object_name],
    qsp.is_forced_plan,
    qsqt.query_sql_text,
    qsrs.count_executions,
    CAST(qsp.query_plan AS XML) AS sql_query_plan
FROM sys.query_store_query qsq
JOIN sys.query_store_query_text qsqt
ON qsq.query_text_id = qsqt.query_text_id
JOIN sys.query_store_plan qsp
ON qsq.query_id= qsp.query_id
JOIN sys.query_store_runtime_stats qsrs
ON qsrs.plan_id = qsp.plan_id
WHERE query_sql_text LIKE '%dbo.a%'
AND qsq.[object_id] <> 0
ORDER BY qsq.query_id;
--ca2

-- forcing plan GUI
--ca3

EXEC sp_query_store_flush_db; 
SELECT qsq.query_id,
    qsq.query_text_id,
    qsq.context_settings_id,
    qsq.[object_id],
    OBJECT_NAME(qsq.[object_id]) AS [object_name],
    qsp.is_forced_plan,
    qsqt.query_sql_text,
    qsrs.count_executions,
    CAST(qsp.query_plan AS XML) AS sql_query_plan
FROM sys.query_store_query qsq
JOIN sys.query_store_query_text qsqt
ON qsq.query_text_id = qsqt.query_text_id
JOIN sys.query_store_plan qsp
ON qsq.query_id= qsp.query_id
JOIN sys.query_store_runtime_stats qsrs
ON qsrs.plan_id = qsp.plan_id
WHERE query_sql_text LIKE '%dbo.a%'
AND qsq.[object_id] <> 0
ORDER BY qsq.query_id;
--ca4

-- Clustered Index Scan
EXEC dbo.my_proc;

EXEC sp_query_store_flush_db; 
SELECT qsq.query_id,
    qsq.query_text_id,
    qsq.context_settings_id,
    qsq.[object_id],
    OBJECT_NAME(qsq.[object_id]) AS [object_name],
    qsp.is_forced_plan,
    qsqt.query_sql_text,
    qsrs.count_executions,
    CAST(qsp.query_plan AS XML) AS sql_query_plan
FROM sys.query_store_query qsq
JOIN sys.query_store_query_text qsqt
ON qsq.query_text_id = qsqt.query_text_id
JOIN sys.query_store_plan qsp
ON qsq.query_id= qsp.query_id
JOIN sys.query_store_runtime_stats qsrs
ON qsrs.plan_id = qsp.plan_id
WHERE query_sql_text LIKE '%dbo.a%'
AND qsq.[object_id] <> 0
ORDER BY qsq.query_id;
--ca5
GO

/* MAIN PART  - CREATE-ALTER */
CREATE OR ALTER PROCEDURE dbo.my_proc
AS
-- some meaningless comment added by developer
SELECT *
FROM dbo.a
WHERE z LIKE '12%'
AND 1 = (SELECT 1);
GO

/* MAIN PART END */

-- Clustered Index Scan
EXEC dbo.my_proc;

EXEC sp_query_store_flush_db; 
SELECT qsq.query_id,
    qsq.query_text_id,
    qsq.context_settings_id,
    qsq.[object_id],
    OBJECT_NAME(qsq.[object_id]) AS [object_name],
    qsp.is_forced_plan,
    qsqt.query_sql_text,
    qsrs.count_executions,
    CAST(qsp.query_plan AS XbML) AS sql_query_plan
FROM sys.query_store_query qsq
JOIN sys.query_store_query_text qsqt
ON qsq.query_text_id = qsqt.query_text_id
JOIN sys.query_store_plan qsp
ON qsq.query_id= qsp.query_id
JOIN sys.query_store_runtime_stats qsrs
ON qsrs.plan_id = qsp.plan_id
WHERE query_sql_text LIKE '%dbo.a%'
AND qsq.[object_id] <> 0
ORDER BY qsq.query_id;

-- is_forced_plan is valid

第一次执行：

添加索引和执行：

强制计划：

另一种执行：

之后CREATE-ALTER ：

结果

随着落创建我们失去了强制计划。

Answer 11:

我有一个模板，它允许执行脚本几次都没有错误。

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[aaa_test]') AND type in (N'P', N'PC'))
    EXEC('CREATE PROCEDURE aaa_test AS')
    EXEC('GRANT EXECUTE ON aaa_test TO someone')
GO

ALTER PROCEDURE aaa_test 
     @PAR1 INT,
     @PAR2 INT=0
AS
BEGIN
    SELECT @PAR1 AS Par1, CASE @PAR2 WHEN 0 THEN 'Default' ELSE 'Other' END AS Par2
END
GO

执行：

EXEC aaa_test 1
EXEC aaa_test 1,5

Answer 12:

你不应该放弃的对象。删除的对象存在两个问题：

1）如果创建失败，则不再有一个对象。（您可以使用事务来避免这种情况，在大量的样板代码为代价）

2）你失去的对象的权限，如果你不明确重新创建。

我宁愿一个“如果不存在”条件中创建一个空对象，然后使用ALTER，并已书面助手程序，用于这一目的。

Answer 13:

这一年是2017年和SQL Server具有创建或更改

SQL服务器2016 SP1和SQL Server vNext有新的T-SQL语言的语句- CREATE [OR ALTER]为：

STOREDPROCEDURES
功能
TRIGGERS
VIEWS

https://blogs.msdn.microsoft.com/sqlserverstorageengine/2016/11/17/create-or-alter-another-great-language-enhancement-in-sql-server-2016-sp1/

Answer 14:

只是为了我的扩展以前的答案。

另一个原因，我更喜欢CREATE-ALTER过DROP-CREATE方法。这可能会导致失去有关对象specifc属性。例如ExecIsStartup ：

USE master
GO

CREATE TABLE dbo.silly_logging(id INT IDENTITY(1,1) PRIMARY KEY
                               ,created_date DATETIME DEFAULT GETDATE()
                               ,comment VARCHAR(100));
GO

CREATE PROCEDURE dbo.my_procedure 
AS
INSERT INTO dbo.silly_logging(comment)
VALUES ('SQL Server Startup');
GO

-- mark procedure to start at SQL Server instance startup
EXEC sp_procoption @ProcName = 'dbo.my_procedure'
    , @OptionName = 'startup'   
    , @OptionValue = 'on';


SELECT name, create_date, modify_date, is_auto_executed
FROM master.sys.procedures
WHERE is_auto_executed = 1;
--name  create_date modify_date is_auto_executed
--my_procedure  2017-07-28 06:36:21.743 2017-07-28 06:36:24.513 1

现在让我们假设有人想通过更新此过程DROP-CREATE ：

DROP PROCEDURE dbo.my_procedure;
GO

CREATE PROCEDURE dbo.my_procedure 
AS
-- adding meaningless comment
INSERT INTO dbo.silly_logging(comment)
VALUES ('SQL Server Startup');
GO

SELECT name, create_date, modify_date, is_auto_executed
FROM master.sys.procedures
WHERE is_auto_executed = 1;
-- empty

而如果你没有意识到这一点，或者你不检查你将结束与过程，将无法启动。

文章来源: What do you do in SQL Server to CREATE OR ALTER?