删除 'OPTIMIZE_FOR_SEQUENTIAL_KEY' 会解决我的 script.sql 失败还是更复杂(SQL Server Express DB -> SQL Server)? [英] Would removing 'OPTIMIZE_FOR_SEQUENTIAL_KEY' solve the failure of my script.sql or is more involved (SQL Server Express DB -> SQL Server)?

问题描述

我正在尝试将我的托管站点 (GoDaddy/Plesk) 上的 SQL Server Express 数据库设置为成熟的 SQL Server 数据库，但在运行 script.sql 文件时遇到了一系列错误消息.

我正在按照步骤

...但是我在运行脚本文件时收到了这些错误消息:

这是因为我的数据库是 SQL Server Express，它正在尝试创建一个 SQL Server(标准/常规)数据库，而OPTIMIZE_FOR_SEQUENTIAL_KEY"是常规"数据库吗?SQL Server/TSQL?

如果是这样(或在任何情况下)，我需要做什么才能运行 sql 脚本?

更多细节，所有的抱怨都是'OPTIMIZE_FOR_SEQUENTIAL_KEY'不是公认的CREATE TABLE选项.";除了一个，它是:

关键字with"附近的语法不正确.如果此语句是公用表表达式、xmlnamespaces 子句或更改跟踪上下文子句，则前一条语句必须以分号结尾.

至于OPTIMIZE_FOR_SEQUENTIAL_KEY"问题，这里是第一个抱怨的地方:

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY],

...至于关键字'with'附近的语法不正确"，这里是它发生的地方:

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]

换句话说，它是完全相同的 TSQL，但被应用于不同的列，并导致不同的 err msgs - 第一个抱怨OPTIMIZE"；第二个只是含糊地抱怨WITH"附近的东西

这里是上下文:

[Id] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY],约束 [CK_ACTORS_Column] 唯一非聚集([演员 ID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]) 在 [主要]

我很想简单地删除所有OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF"；声明并重试；这样做安全吗?

解决方案

您的目标 SQL Server 只是一个旧版本.确切地说，它是 SQL Server 2014 SP3 CU4，而 OPTIMIZE_FOR_SEQUENTIAL_KEY 是 SQL Server 2019 中的一项新功能.SSMS 脚本总是会引用所有选项，即使设置为默认值以确保完全保真脚本，这有时也很痛苦.

您可以简单地删除该选项，该脚本应该与 SQL 2014 兼容.事实上，如果您没有将任何选项设置为非默认值，您可以删除所有这些选项.

或者查看您的托管服务商是否有可用的 SQL Server 2019.或升级您的主机:)

I am trying to set up my SQL Server Express database on my hosting site (GoDaddy/Plesk) as a full-fledged SQL Server DB, but have run into a series of error messages running the script.sql file.

I am following the steps here to set up my DB, first by running my script.sql file inside MSSMS while connected to the remote server.

I had been having trouble connecting, but was finally able to by leaving out the port number, as recommended by seanb here:

...but I got these err msgs on running the script file:

Is this because my DB is SQL Server Express, and it's trying to create a SQL Server (standard/regular) DB, and 'OPTIMIZE_FOR_SEQUENTIAL_KEY' is foreing to "regular" SQL Server/TSQL?

If so (or in any case), what do I need to do to get the sql script to run?

For a little more detail, all of the complaints are "'OPTIMIZE_FOR_SEQUENTIAL_KEY' is not a recognized CREATE TABLE option." except for one, which is:

Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

As for the "OPTIMIZE_FOR_SEQUENTIAL_KEY" problem, here is the first place where that is complained about:

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY],

...and as for "Incorrect syntax near the keyword 'with'", here is where it occurs:

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]

In other words, it's the same exact TSQL, but being applied to different columns, and causing different err msgs - the first one complaining about "OPTIMIZE" and the second one simply vaguely complaining about something near "WITH"

Here they are in context:

[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY],
 CONSTRAINT [CK_ACTORS_Column] UNIQUE NONCLUSTERED 
(
    [ActorId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

I'm tempted to simply remove all of the "OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF" statements and try it again; would it be safe to do that?

解决方案

Your target SQL Server is simply an older version. It's SQL Server 2014 SP3 CU4 to be exact, and OPTIMIZE_FOR_SEQUENTIAL_KEY is a new feature in SQL Server 2019. SSMS scripting always recites all the options, even when set to the default value to ensure full fidelity scripting, which sometimes a pain.

You can simply remove that option and the script should be compatible with SQL 2014. In fact you can remove all those options if you haven't set any to a non-default value.

Or see if your hoster has SQL Server 2019 available. Or upgrade your hoster :)

这篇关于删除 'OPTIMIZE_FOR_SEQUENTIAL_KEY' 会解决我的 script.sql 失败还是更复杂(SQL Server Express DB -> SQL Server)?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！