В этой статье говорится о потере разрешений при удалении объекта в SQL server.

• Tips’ N ' Tricks-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 в коде выше.

еще одна причина, чтобы рассмотреть возможность сделать это этот путь-терпимость к неудаче. Предположим, что ваше падение успешно, но ваше создание терпит неудачу - вы заканчиваете сломанной БД. Используя подход ALTER, вы получите более старую версию объекта.

год 2009 и SQL Server не имеет создать или изменить / заменить.

год 2016, и теперь он должен умереть (Падение, Если Существует) в 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

The соответствующее сообщение в блоге команды MSSQL Tiger объясняется

создать или изменить можно использовать в объектах программируемости, таких как:

• хранимые процедуры (включая скомпилированные в собственном коде)
• функции (Transact-SQL, включая скомпилированные в собственном коде)
• триггеры
• вид

но не может использоваться в:

• объекты которые требуют хранения (таблицы, индексы и индексированные представления)
• пользовательские функции CLR
• устаревшие объекты программируемости (правило и значение по умолчанию)
• объекты непрограммируемости (такие как CREATE ASSEMBLY, CREATE TABLE или CREATE - SCHEMA). На этих объектах синтаксис для CREATE и ALTER очень отличается с точки зрения синтаксиса и удобства использования.

каждый раз, когда разработчик пишет IF EXISTS(...) DROP тюлень щенок дубиной. Вы должны точно знать, что находится в базе данных, и ваш сценарий обновления должен выполнять CREATe или ALTER as appropiate, основываясь на текущей версии вашей схемы приложения: контроль версий и Ваша база данных.

мы столкнулись с ситуацией, когда нам нужно было обновить удаленный сайт, но у нас не было разрешения на удаление. До сих пор мы использовали сценарий "DROP and CREATE", встроенный в SSMS 2008 R2, но теперь нам нужно было изменить. Мы создали три шаблона, которые мы опускаем над соответствующими скриптами 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 не изменяет его, и если они добавляют или изменяют разрешения, они остаются. Таким образом, легко скопировать имя функции или хранимой процедуры и использовать замену параметров шаблона для автоматизации завершения этих скриплетов.

теперь я надеюсь, что хорошие люди в Microsoft либо добавят это в свои списки "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

Я хотел бы использовать 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 из-за того, как мы работаем с системой управления версиями и т. д.

Это в основном способ сделать это, да. Мне просто интересно, есть ли у вас особая причина использовать подход "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 " - на самом деле не строго или непосредственно таблицы.

Марк

я всегда alter мои объекты, потому что a drop это действительно плохая практика и может оставить вашу БД в плохом состоянии, если объект не удается создать (24/7 дБ!), а также то, что другие плакаты упоминали о разрешениях на ядерную бомбу.

редакторы, такие как Sublime, Atom и VS Code, позволят вам создавать фрагменты кода в качестве шаблонов для них, чтобы быстро генерировать ваш скелетный скрипт. 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

Procs:

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

похоже, что это некоторое время:текст ссылки

типичный сценарий для меня:

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

Я буду использовать либо в зависимости от контекста: мои сценарии начальной сборки или основного рефакторинга будут использовать check/drop/create, чистые сценарии обслуживания используют alter.

у меня есть шаблон, который позволяет выполнить скрипт несколько раз без ошибок.

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

вы не должны бросить объект. Удаление объекта страдает от двух проблем:

1) Если создать не удается, у вас больше нет объекта. (Вы можете использовать транзакции, чтобы избежать этого, за счет большого количества шаблонного кода)

2) вы теряете разрешения на объект, если вы явно не воссоздаете их.

---

Я предпочитаю создавать пустой объект в состоянии" если не существует", а затем использовать ALTER и писать вспомогательные процедуры для эта цель.

год 2017 и SQL Server имеет CREATE или ALTER

SQL Server 2016 SP1 и SQL Server vNext имеют новый оператор языка T-SQL -СОЗДАТЬ [ИЛИ ИЗМЕНИТЬ] ибо:

• STOREDPROCEDURES
• функции
• триггеры
• вид

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

предпочитаю CREATE-ALTER подход (не синтаксис) over 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 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:

---

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:

результат

С Drop-Create мы потеряли вынужденный план.

только к моему расширить предыдущий ответ.

еще одна причина, почему я предпочитаю CREATE-ALTER over DROP-CREATE подход. Это может привести к потере определенных свойств объекта. Например 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

и если вы не знаете об этом, или вы не проверяете, Вы в конечном итоге с процедурой, которая не начнется.