DROP... CREATE vs ALTER

Ответ 1

ALTER также заставит перекомпилировать всю процедуру. Повторная компиляция уровня заявки применяется к операторам внутри процедур, например. один SELECT, который перекомпилируется, потому что базовые таблицы изменяются, без каких-либо изменений в процедуре. Невозможно было бы выборочно перекомпилировать только определенные инструкции в процедуре ALTER, чтобы понять, что изменилось в тексте SQL после процедуры ALTER, которую сервер должен был бы скомпилировать.

Для всех объектов ALTER всегда лучше, потому что он сохраняет всю безопасность, все расширенные свойства, все зависимости и все ограничения.

Ответ 2

Вот как мы это делаем:

if object_id('YourSP') is null
    exec ('create procedure dbo.YourSP as select 1')
go
alter procedure dbo.YourSP
as
...

Код создает хранимую процедуру "заглушка", если она еще не существует, иначе она изменит. Таким образом, сохраняются любые существующие разрешения на процедуру, даже если вы повторно выполняете script.

Ответ 3

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

Ответ 4

Если у вас есть функция/хранимая процедура, которая вызывается очень часто с веб-сайта, например, это может вызвать проблемы.

Сохраненная процедура будет отброшена на несколько миллисекунд в секунду, и за это время все запросы будут терпеть неудачу.

Если вы делаете alter, у вас нет этой проблемы.

Шаблоны для вновь созданной хранимой процедуры обычно составляют эту форму:

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = '<name>')
    BEGIN
        DROP PROCEDURE <name>
    END
GO

CREATE PROCEDURE <name>
......

Однако, наоборот, лучше: imo:

Если хранимыйproc/function/etc не существует, создайте его с помощью фиктивного оператора select. Затем переменная всегда будет работать - она ​​никогда не будет удалена.

У нас есть хранимая процедура для этого, поэтому наши хранимые процедуры/функции обычно такие:

EXEC Utils.pAssureExistance 'Schema.pStoredProc'
GO

ALTER PROCECURE Schema.pStoredProc
...

и мы используем тот же хранимый proc для функций:

EXEC Utils.pAssureExistance 'Schema.fFunction'
GO

ALTER FUNCTION Schema.fFunction
...

В Utils.pAssureExistance мы выполняем IF и смотрим на первый символ после ".": если это "f", мы создаем фиктивную фонову, если это "p", мы создаем фиктивный сохраненный proc.

Будьте осторожны, если вы создадите фиктивную скалярную функцию и ваш ALTER находится на функции с табличной оценкой, функция ALTER FUNCTION потерпит неудачу, заявив, что она несовместима.

Опять же, Utils.pAssureExistance может быть удобно, с дополнительным необязательным параметром

EXEC Utils.pAssureExistance 'Schema.fFunction', 'TableValuedFunction'

создаст фиктивную табличную функцию,

Дополнительно, я могу ошибаться, но я думаю, что если вы выполните процедуру удаления, и в настоящее время запрос использует сохраненный процесс, он будет терпеть неудачу.

Однако процедура изменения будет ждать, пока все запросы перестанут использовать сохраненный процесс, а затем изменит его. Если запросы "блокируют" сохраненный процесс слишком долго (скажем, пару секунд), ALTER перестанет ждать блокировки и все равно изменит сохраненный процесс: запросы с использованием сохраненного proc, вероятно, сбой в этой точке.

Ответ 5

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

Я всегда предпочитал использовать drop/recreate. Мне также стало легче хранить их в исходном управлении. Вместо того, чтобы делать.... если существует, нужно изменить и если не существует, создайте.

Сказав это... если вы знаете, что делаете... Я не думаю, что это слишком важно.

Ответ 6

Вы задали вопрос, конкретно относящийся к объектам БД, которые не содержат никаких данных, и теоретически их не следует часто менять.

Вероятно, вам придется редактировать эти объекты, но не каждые 5 минут. Из-за этого я думаю, что вы уже ударили молотком по голове - разрешения.

Короткий ответ, на самом деле не проблема, если разрешения не являются проблемой

Ответ 7

DROP обычно теряет разрешения и любые расширенные свойства.

В некоторых UDF, ALTER также потеряет расширенные свойства (определенно в многозадачных табличных функциях SQL Server 2005).

Обычно я не DROP и CREATE, если я также не воссоздаю эти вещи (или знаю, что хочу их потерять).

Ответ 8

Раньше мы использовали alter во время разработки, создавая новые функции или изменяя функциональность. Когда мы закончили с нашим развитием и тестированием, мы тогда сделаем каплю и создадим. Это изменяет дату/время штампа на procs, чтобы вы могли сортировать их по дате/времени.

Это также позволило нам увидеть, что было привязано по дате для каждого отправленного нами отправления.

Ответ 9

Добавить с каплей, если существует лучше, потому что, если у вас есть несколько сред при перемещении script в QA или в test или prod, вы не знаете, существует ли script в этой среде. Добавив капли (если она уже существует), а затем добавьте, вы будете закрыты независимо от того, существует ли она или нет. Затем вам необходимо повторно назначить разрешения, но лучше, чем выслушать версию установки script.

Ответ 10

Начиная с SQL Server 2016 SP1, теперь у вас есть возможность использовать синтаксис CREATE OR ALTER для хранимых процедур, функций, триггеров и представлений. См. CREATE OR ALTER - еще одно отличное языковое усовершенствование в SQL Server 2016 SP1 в блоге Database Engine Database Engine. Например:

CREATE OR ALTER PROCEDURE dbo.MyProc
AS
BEGIN
    SELECT * FROM dbo.MyTable
END;

Ответ 11

С точки зрения удобства использования drop и create лучше, чем alter. Alter завершится неудачно в базе данных, которая не содержит этого объекта, но имеет IF EXISTS DROP, а затем CREATE будет работать в базе данных с уже существующим объектом или в базе данных, где объект не существует. В Oracle и PostgreSQL вы обычно создаете функции и процедуры с помощью инструкции CREATE OR REPLACE, которая делает то же самое, что и SQL SERVER IF EXISTS DROP, а затем CREATE. Было бы неплохо, если бы SQL Server взял этот небольшой, но очень удобный синтаксис.

Вот как я это сделаю. Поместите все это в один script для данного объекта.

IF EXISTS ( SELECT 1
            FROM information_schema.routines
            WHERE routine_schema = 'dbo'
              AND routine_name   = '<PROCNAME'
              AND routine_type   = 'PROCEDURE' )
BEGIN
    DROP PROCEDURE <PROCNAME>
END
GO


CREATE PROCEDURE <PROCNAME>
AS
BEGIN
END
GO

GRANT EXECUTE ON <PROCNAME> TO <ROLE>
GO