如何在线创建非聚集索引(如果可用) [英] How to create nonclustered index with online if available
问题描述
我正在按照Azure门户中的查询见解刀片服务器的建议将新索引添加到SQL Azure数据库,该刀片服务器使用 ONLINE = ON
标志.SQL看起来像这样:
I'm adding a new index to a SQL Azure database as recommended by the query insights blade in the Azure portal, which uses the ONLINE=ON
flag. The SQL looks something like this:
CREATE NONCLUSTERED INDEX [IX_MyIndex] ON
[Customers].[Activities] ([CustomerId])
INCLUDE ([AccessBitmask], [ActivityCode], [DetailsJson],
[OrderId], [OperationGuid], [PropertiesJson], [TimeStamp])
WITH (ONLINE = ON)"
但是,我们还需要将此相同的索引添加到本地开发数据库中,它们只是不支持 ONLINE = ON
选项的 localdb
实例,出现以下错误.
However, we also need to add this same index to our local development databases, which are just localdb
instances that don't support the ONLINE=ON
option, resulting in the following error.
Online index operations can only be performed in Enterprise edition of SQL Server.
我的问题是-有没有一种方法可以编写此SQL索引创建语句,该语句将使用 ONLINE = ON
(如果可用),但在不支持该操作的数据库上仍会成功支持吗?
My question is - is there a way to write this SQL index creation statement that will use ONLINE=ON
if available, but still succeed on databases that don't support it?
推荐答案
您可以使用以下内容:
DECLARE @Edition NVARCHAR(128);
DECLARE @SQL NVARCHAR(MAX);
SET @Edition = (SELECT SERVERPROPERTY ('Edition'));
SET @SQL = N'
CREATE NONCLUSTERED INDEX [IX_MyIndex] ON
[Customers].[Activities] ([CustomerId])
INCLUDE ([AccessBitmask], [ActivityCode], [DetailsJson],
[OrderId], [OperationGuid], [PropertiesJson], [TimeStamp])
'
IF @Edition LIKE 'Enterprise Edition%' OR @Edition LIKE 'SQL Azure%' BEGIN
SET @SQL = @SQL + N' WITH (ONLINE = ON)';
END;
EXEC sp_executesql @SQL;
这篇关于如何在线创建非聚集索引(如果可用)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!