SQL Server - 将XML索引添加到View上的计算列 [英] SQL Server - Adding an XML Index to a calculated column on a View

问题描述

我有一个表在NVARCHAR（MAX）中存储逗号分隔值。

I have a table which stores comma separated values in an NVARCHAR(MAX).

我创建了一个视图，它使用字符串操作来转换这些以逗号分隔的值进入xml列表。然后，我可以使用此Xml列访问每个项目。

I have created a view, which uses string manipulation to convert these comma separated values into an xml list. I can then use this Xml column access each item.

如果我可以索引它，那么此列上的查询将受益匪浅。但是，在尝试创建主XML索引时，我收到消息查看XmlFoo foes没有集群主键。创建XML索引需要视图上的集群主键。

The queries on this column will benefit greatly if I could index it. However, on trying to create a Primary XML index I get the message "View XmlFoo foes not have a clustered primary key. Creation of an XML index requires a clustered primary key on the view."

是否有可能做我正在做的事情，如果是这样，我怎么能在它询问时向视图添加主键？我不认为这是可能的。

Is it possible to do what I'm after, and if so, how can I add a Primary Key to a View as it asks? I didn't think this was possible.

我的示例脚本如下。

CREATE TABLE [dbo].[Foo](
    [FooId] [int] IDENTITY(1,1) NOT NULL,
    [FooValues] NVARCHAR(MAX),
    CONSTRAINT [PK_Foo] PRIMARY KEY CLUSTERED 
    (
        [FooId] ASC
    )ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE VIEW [dbo].[XmlFoo] WITH SCHEMABINDING
AS
SELECT
    FooId,
    FooValues,
    CONVERT(xml, '<FooValues><FooValue>' + REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(FooValues, '&', '&amp;'), '>', '&gt;'), '<', '&lt;'), '"', '&quot;'), '''', '&apos;'), ',', '</FooValue><FooValue>') + '</FooValue></FooValues>') AS XmFoolValues
FROM
    [dbo].[Foo]
GO

INSERT INTO XmlFoo (FooValues) VALUES ('A,B,C')
INSERT INTO XmlFoo (FooValues) VALUES ('1,2')
INSERT INTO XmlFoo (FooValues) VALUES ('X,Y')
INSERT INTO XmlFoo (FooValues) VALUES ('I')
INSERT INTO XmlFoo (FooValues) VALUES ('9,8,7,6,5')
GO
SELECT * FROM XmlFoo

推荐答案

好的，我已经回答了我自己的问题。错误消息显然是一个红色的鲱鱼，而不是仅仅说你不能索引一个视图上的xml列，它提到了一个主键！

Ok, I've answered my own question. The error message is obviously a red herring, instead of just saying you can't index an xml column on a view it mentions a primary key!

你不能创建一个XML索引（主要或辅助），视图中的xml列，带有xml列的表值变量或xml类型变量。

You cannot create an XML index, either primary or secondary, on an xml column in a view, on a table-valued variable with xml columns, or xml type variables.

这是一篇关于此问题的Tech Net文章。

这篇关于SQL Server - 将XML索引添加到View上的计算列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！