在SQL Server中，你需要执行CREATE OR ALTER操作来做什么？

Question

现在是2009年，SQL Server没有CREATE OR ALTER/REPLACE这个功能。我使用的方法如下。

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建议的那样，官方消息表明，该功能不是高优先级。 因此会有这个问题。

- harpo

---

看起来，仅过了七年多的时间，SQL Server 现在有了 CREATE OR ALTER 语句。喔耶！ - ruffin

@ruffin，没错，这就是我现在所做的。对于早期版本（其半衰期很长），这里的方法仍然是最好的选择。 - harpo

支持 CREATE OR ALTER，但这仅适用于 SQL Server 2016。 - Icegras

Answer 1

这篇文章提到了在SQL Server中删除对象时会失去权限的问题，作者提出了一个很好的观点。

• 技巧 - T-SQL - 一种优雅的方法，在SQL Server中一次性创建或更改存储过程，以便进行简单的维护

下面介绍保留权限的方法：

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失败了 - 您最终将获得一个损坏的数据库。使用ALTER方法，您将最终获得对象的旧版本。

- Victor Zakharov

---

是的，这正是我一直以来的做法（除了 AS SET NOCOUNT ON，这是一个有趣的变化）。想想看，我应该为这个模式设置一个 yasnippet，因为我经常要输入它。 - harpo

我已经编写了模板来处理缺失的存储过程、标量函数和表函数的创建，并取得了良好的成功。 - Daniel Bragg

然而，如果你“只是用FUNCTION替换PROCEDURE”，语法是不正确的。我已经在下面发布了我们的示例。 - Daniel Bragg

为什么在临时占位符存储过程体中使用"SET NOCOUNT ON"是一个好选择？在这里提问：https://dev59.com/s5zha4cB1Zd3GeqPL-Mu - Jon Schneider

我认为使用这种方法的另一个优点是避免关于已设置的潜在权限的错误，例如除DBA之外的其他人进行更改，使用drop/create可能会导致您不知道的撤销授权。 - LeBaptiste

Answer 2

现在是2009年，SQL Server没有CREATE OR ALTER/REPLACE。

现在是2016年，SQL Server 2016 RTM中已经有了DIE (Drop If Exists)，并且在2016 SP1中引入了CREATE OR ALTER。

首先，在使用这种方法时需要重新应用权限的注意事项仍然适用。下面是示例语法：

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 Tiger Team 博客文章 解释了：

CREATE OR ALTER 可用于可编程对象，例如：

• 存储过程（包括本地编译）
• 函数（Transact-SQL，包括本地编译）
• 触发器
• 视图

但不能用于：

• 需要存储的对象（表、索引和索引视图）
• CLR 用户定义函数
• 弃用的可编程对象（规则和默认值）
• 非可编程对象（如 CREATE ASSEMBLY、CREATE TABLE 或 CREATE SCHEMA）。对于这些对象，从语法和可用性角度来看，CREATE 和 ALTER 的语法有很大的区别。

- Martin Smith

Answer 3

我们遇到了一个情况，需要更新远程站点，但我们没有DROP权限。到目前为止，我们一直在使用内置于SSMS 2008 R2中的“DROP and CREATE”脚本，但现在我们需要进行更改。 我们创建了三个模板，在需要更新存储过程或函数时，我们将其放置在适当的ALTER脚本之上：

—- 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

每次创建后都会自动脚本化任何特殊权限（表函数无法分配权限）。之后，ALTER 语句不会更改它，如果添加或修改权限，则会保留。通过这种方式，可以轻松复制函数或存储过程名称，并使用模板参数替换来自动完成这些脚本程序。希望微软公司能将其添加到“Script ___ as”列表中，或者赋予我们创建自己列表的能力，使得这种脚本化成为基本功能。

你可能想要支持 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

- Daniel Bragg

Answer 4

每当开发人员编写 IF EXISTS(...) DROP 时，就会有一只海豹幼崽被殴打。您应该确切知道数据库中的内容，根据当前应用程序模式的版本，您的升级脚本应该进行CREATE或ALTER操作： 版本控制和您的数据库。

- Remus Rusanu

---

为什么？小心谨慎总比后悔莫及好！你从未遇到过脚本失败，然后你要删除的存储过程仍然在数据库中挂着吗？我总是会先进行IF EXISTS()检查——只是为了更加安全！你从未遇到过一些愚蠢的临时DBA多次运行你的脚本而不是只运行一次吗？ - marc_s

我认为这是一种合理的方法，可以让我的部署脚本安全地运行多次。此外，如果我能够在不感冒的情况下捕杀海豹... - n8wrl

我想我并没有生活在理想的世界里。如果脚本在客户机器上出现错误，即使版本控制和测试过了，我该怎么说呢？"太糟糕了，它应该可以工作，所以我也帮不了你。" 如果这样的话，我的生意就完了，Remus。 - harpo

@marc：是的，那些傻瓜数据库管理员...我只是想在这里播种，让人们更多地将数据库视为一个受版本控制的资源，而不是“让我们打开SSMS并修改表格！”。我知道在实践中不依赖if exists()是不可能的（或者像gbn一样，我实际上更喜欢使用object_id不为空）。我想海豹宝宝注定要失败了...北极恶心的害虫。 - Remus Rusanu

实现这个线程的复活可能有些困难，但我不确定你能用“在SQL Server中如何创建或更改？”来回答“不要在SQL Server中创建或更改”。;^) 好吧，我的意思是，我猜你确实这样做了，但我不确定你应该这样做...“CREATE OR ALTER Club的第一条规则是：‘不要创建或更改CREATE OR ALTER Club！’” - ruffin

Answer 5

在执行DROP操作之前，我会使用OBJECT_ID(...) IS NOT NULL。

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

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，因为我们使用源代码控制等方面的原因。

- gbn

---

我终于明白你在这里说什么了...虽然OBJECT_ID也是专有的，但这是一种更紧凑的执行检查的方式。 - harpo

我不知道OBJECT_ID是专有的...不过它可能也在Sybase中。 - gbn

你会如何编写查询语句？如果对象ID不为空，那么怎么办？删除对象吗？T-SQL中有这样的语句吗？ - Victor Zakharov

Answer 6

我总是修改我的对象，因为删除是非常糟糕的做法，如果一个对象创建失败，它可能会使你的数据库处于不良状态（24/7数据库！），除此之外，其他帖子也提到了权限问题。像Sublime、Atom和VS Code这样的编辑器将允许您创建代码片段作为模板，以便快速生成骨架脚本。 SQL 2016现在终于支持DROP IF EXISTS结构，但它仍然从错误的方向接近——一切都是drop/create而不是远古时代的一次create，从那时起就alter。此外，我试图让我的标题尽可能短，所以我不会比create proc dbo.myproc as更花哨的东西了。

视图：

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

- mattmc3

---

谢谢，说得好。在对可用性要求较高的情况下，我已经开始在事务内执行DROP/CREATE操作，这与ALTER实际上是相同的。但是，ALTER更加精细。 - harpo

Answer 7

我更喜欢使用CREATE-ALTER方法（而不是语法），有两个原因：

• 权限问题（使用DROP-CREATE需要重新创建权限）
• 对象ID问题（修改对象不会改变其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 Fiddle

我们可以看到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 Fiddle

在这种情况下，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之后：

---

创建-更改

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 后：

结果

使用 Drop-Create 我们失去了强制执行计划。

- Lukasz Szozda

Answer 8

那基本上就是这样做的，是的。我只是想知道你使用“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”模式下的系统目录视图——并非严格意义上的表。

Marc

- marc_s

---

我开始使用EXEC，因为有时当我将脚本发送给对象不存在的客户端时，SQL Server会抱怨。也许这是迷信，但它并没有造成任何伤害。 - harpo

Answer 9

2017年，SQL Server增加了CREATE OR ALTER命令。SQL Server 2016 SP1和SQL Server vNext支持在STORED PROCEDURES、FUNCTIONS、TRIGGERS和VIEWS中使用CREATE [OR ALTER]语句。详情请参考此处。

- OzBob

---

对于那些拥有 SQL 2016 SP1 及以上版本的幸运儿来说，这现在是最好的选择 :) - vlabatut

Answer 10

看起来还有一段时间：链接文本

对我来说是典型的脚本：

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

- n8wrl

---

建议使用a)“sys”模式以及 b)更具有针对性的系统视图，例如sys.tables用于表，sys.triggers用于触发器等，而不仅仅是通用的“sysobjects”（这将很快被弃用）。 - marc_s