你在 SQL Server 中做什么来创建或更改？

Question

提问by harpo

The year is 2009 and SQL Server does not have CREATE OR ALTER/REPLACE. This is what I do instead.

年份是 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

For triggers, you have to lean on the proprietary system views.

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

Is this the most accepted convention in the meantime?

这是目前最被接受的约定吗？

EDIT:As n8wrl suggested, the official wordsuggests that this feature is not a high priority. Hence the question.

编辑：正如 n8wrl 所建议的，官方说法表明此功能不是高优先级。因此这个问题。

Answer 1

回答by Neolisk

This article makes a good point about losing permissions when dropping an object in SQL server.

这篇文章很好地说明了在 SQL Server 中删除对象时丢失权限的问题。

Tips ‘N' Tricks – T-SQL – An Elegant way to CREATE or ALTER Stored Procedures in One-Go for Easy Maintenance

Tips 'N' Tricks – T-SQL – 一种一次性创建或更改存储过程的优雅方式，便于维护

So here is the approach which retains permissions:

所以这是保留权限的方法：

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

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

Also works for functions, just replace PROCEDUREwith FUNCTIONin the above code.

也适用于函数，只需替换上面代码中的PROCEDUREwith 即可FUNCTION。

Another reason to consider doing it this way is tolerance to failure. Suppose your DROP succeeds, but your CREATE fails - you end with a broken DB. Using ALTER approach, you will end up with an older version of the object.

考虑这样做的另一个原因是对失败的容忍度。假设您的 DROP 成功，但您的 CREATE 失败 - 您以损坏的数据库结束。使用 ALTER 方法，您最终会得到对象的旧版本。

Answer 2

回答by Martin Smith

The year is 2009 and SQL Server does not have CREATE OR ALTER/REPLACE.

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

The year is 2016 and it does now have DIE (Drop If Exists) in SQL Server 2016 RTM and CREATE OR ALTER(introduced in 2016 SP1).

这一年是 2016 年，它现在在 SQL Server 2016 RTM 和（在 2016 SP1 中引入）中有 DIE（Drop If ExistsCREATE OR ALTER）。

Taking Drop If Existsfirst the caveats around needing to re-apply permissions with this approach still apply. Example syntax is

Drop If Exists首先考虑需要使用这种方法重新应用权限的警告仍然适用。示例语法是

DROP PROCEDURE IF EXISTS dbo.SynchronizeRemoteCatalog

GO

CREATE PROCEDURE dbo.SynchronizeRemoteCatalog
AS
  BEGIN
      BODY:
  END 

GO

/*TODO: Reapply permissions*/

CREATE OR ALTERretains the permissions. Example syntax is

CREATE OR ALTER保留权限。示例语法是

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

The corresponding MSSQL Tiger Team blog postexplains

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

CREATE OR ALTER can be used in programmability objects such as:
STORED PROCEDURES (including natively compiled)
FUNCTIONS (Transact-SQL, including natively compiled)
TRIGGERS
VIEWS
But cannot be used in:
Objects that require storage (tables, indexes and indexed views)
CLR user-defined functions
Deprecated programmability objects (RULE and DEFAULT)
Non-programmability objects (such as CREATE ASSEMBLY, CREATE TABLE or CREATE - SCHEMA). On these objects, the syntax for CREATE and ALTER is very different from a syntax and usability perspective.

CREATE OR ALTER 可用于可编程对象，例如：
存储过程（包括本地编译）
函数（Transact-SQL，包括本机编译）
触发器
视图
但不能用于：
需要存储的对象（表、索引和索引视图）
CLR 用户定义函数
不推荐使用的可编程对象（规则和默认值）
非可编程对象（例如 CREATE ASSEMBLY、CREATE TABLE 或 CREATE - SCHEMA）。在这些对象上，CREATE 和 ALTER 的语法从语法和可用性的角度来看是非常不同的。

Answer 3

回答by Remus Rusanu

Every time a developer writes IF EXISTS(...) DROPa seal pup is clubbed. You should know exactly what's in the database and your upgrade script should do the CREATe or ALTER as appropiate, based on the current version of your application schema: Version Control and your Database.

每次开发人员写IF EXISTS(...) DROP一只海豹幼崽都会被打成棍棒。您应该确切地知道数据库中的内容，并且您的升级脚本应该根据应用程序架构的当前版本执行 CREATe 或 ALTER 操作：Version Control 和您的 Database。

Answer 4

回答by Dan

We encountered a situation where we needed to update a remote site, but we didn't have DROP permissions. Until now, we have been using the ‘DROP and CREATE' script built into SSMS 2008 R2, but now we needed to change. We created three templates, which we drop above the appropriate ALTER scripts when we need to update a stored procedure or function:

我们遇到了需要更新远程站点的情况，但我们没有 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

Any special permissions get scripted after each CREATE (Table functions cannot be assigned permissions). After that, the ALTER doesn't change it, and if they add or modify the permissions, they remain. Doing it this way, it's an easy task to copy the name of the function or stored procedure, and use the Template Parameter replacement to automating the completion of these scriptlets.

任何特殊权限都会在每次 CREATE 之后编写脚本（不能为表函数分配权限）。之后，ALTER 不会更改它，如果他们添加或修改权限，它们仍然存在。通过这种方式，复制函数或存储过程的名称并使用模板参数替换来自动完成这些 scriptlet 是一项简单的任务。

Now, I'm hoping that the good folks at Microsoft will either add this to their “Script ___ as” lists, or give us the ability to create our own such that this scripting comes ‘baked-in'

现在，我希望微软的好人要么将其添加到他们的“Script ___ as”列表中，要么让我们能够创建自己的脚本，以便“烘焙”

You may want to throw some weight behind the SQL Server feedback entry at: https://connect.microsoft.com/SQLServer/feedback/details/344991/create-or-alter-statement. It seems to be one of the few that are still accessible publicly, and they state that they "have started a feasibility review for this to decide if we can ship this in the near future." The more voices, the more likely this will happen!

您可能希望在 SQL Server 反馈条目后面添加一些权重：https: //connect.microsoft.com/SQLServer/feedback/details/344991/create-or-alter-statement。它似乎是仍然可以公开访问的少数几个之一，他们表示他们“已经开始对此进行可行性，以决定我们是否可以在不久的将来发货。” 声音越多，这种情况发生的可能性就越大！

(Update: now also using the following code for Triggers and Views)

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

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

回答by gbn

I'd use OBJECT_ID(...) IS NOT NULLbefore a DROP.

我会OBJECT_ID(...) IS NOT NULL在 DROP 之前使用。

Object identifiers have to be unique, so it works without using system tables:

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

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

gives

给

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

I normally use ALTER because of how we work with source control, etc.

我通常使用 ALTER，因为我们如何使用源代码控制等。

Answer 6

回答by mattmc3

I always altermy objects because a dropis really bad practice and can leave your DB in an bad state if an object fails to create (24/7 db!), as well as what the other posters have mentioned about nuking permissions.

我总是alter我的对象，因为 adrop是非常糟糕的做法，如果对象无法创建（24/7 db！），以及其他发布者提到的有关 nuking 权限的内容，可能会使您的数据库处于不良状态。

Editors like Sublime, Atom, and VS Code will let you make code snippets as templates for these to quickly gen-up your skeleton script. SQL 2016 now finally supports DROP IF EXISTSconstruct, but it still approaches from the wrong direction - that everything is a drop/createinstead of a one time createin the distant past and alterfrom then on. Also, I have tried to make my headers as short as will possibly work, so I don't get any fancier than create proc dbo.myproc asas the createstub.

Sublime、Atom 和 VS Code 等编辑器可让您将代码片段作为模板，以快速生成您的骨架脚本。SQL 2016现在终于支持DROP IF EXISTS结构，但它仍然从错误的方向接近-这一切都是一个drop/create的一个时间，而不是create在遥远的过去，并alter从那时起。此外，我试图让我的头尽可能短，将可能的工作，所以我没有得到任何票友比create proc dbo.myproc as为create存根。

Views:

意见：

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 (scalar):

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 (tabular):

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 7

回答by marc_s

That's basically the way to do it, yes. I just wonder if you have a particular reason to use the "EXEC" approach:

这基本上就是这样做的方法，是的。我只是想知道您是否有特殊原因使用“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')

Why not just:

为什么不只是：

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

???

？？？

For triggers, there's sys.triggers. Those are system catalog views in the "sys" schema - not strictly or directly tables, really.

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

Marc

马克

Answer 8

回答by overslacked

I'll use either depending on context: my initial-build or major refactoring scripts will use check/drop/create, pure maintenance scripts use alter.

我将根据上下文使用：我的初始构建或主要重构脚本将使用检查/删除/创建，纯维护脚本使用更改。

Answer 9

回答by n8wrl

Looks like it's a while off: link text

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

typical script for me:

我的典型脚本：

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 10

回答by OzBob

The year is 2017 and SQL Server has CREATE OR ALTER

年份是 2017 年，SQL Server 有 CREATE OR ALTER

SQL Server 2016 SP1 and SQL Server vNext have new T-SQL language statement – CREATE [OR ALTER]for:

SQL Server 2016 SP1 和 SQL Server vNext 具有新的 T-SQL 语言语句 - CREATE [OR ALTER]用于：

STOREDPROCEDURES
FUNCTIONS
TRIGGERS
VIEWS

存储过程
职能
触发器
视图

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

你在 SQL Server 中做什么来创建或更改？

提问by harpo

回答by Neolisk

回答by Martin Smith

回答by Remus Rusanu

回答by Dan

回答by gbn

回答by mattmc3

回答by marc_s

回答by overslacked

回答by n8wrl

回答by OzBob

相关推荐

最近更新

标签

你在 SQL Server 中做什么来创建或更改？

提问by harpo

回答by Neolisk

回答by Martin Smith

回答by Remus Rusanu

回答by Dan

回答by gbn

回答by mattmc3

回答by marc_s

回答by overslacked

回答by n8wrl

回答by OzBob

相关推荐

SQL SELECT 从多个表

SQL：级联 UNION 和 JOIN

如何在 Oracle Sql developer 中查看 Explain Plan？

SQL - UPDATE 中每个 SET 命令的 WHERE 子句？

相关推荐

最近更新

标签