在SQL Server 2005的视图上创建全文索引 [英] Creating a fulltext index on a view in SQL Server 2005

问题描述

我在SQL Server 2005的视图中创建全文索引时遇到了麻烦。查看文档我没有发现问题。我收到的错误消息是：'Id'不是强制执行全文搜索关键字的有效索引。全文搜索关键字必须是唯一的，不可为空的单列索引，它不是脱机的，是没有在非确定性或不精确的非计算列上定义，并且最大大小为900字节，请为全文密钥选择另一个索引。
我已经能够验证错误字符串中的每个需求，除了脱机需求，我不知道这意味着什么。我敢肯定它不是离线的。

I am having troubles creating a fulltext index on a view in SQL Server 2005. Reviewing the documentation I have not found the problem. The error message I receive is: "'Id' is not a valid index to enforce a full-text search key. A full-text search key must be a unique, non-nullable, single-column index which is not offline, is not defined on a non-deterministic or imprecise nonpersisted computed column, and has maximum size of 900 bytes. Choose another index for the full-text key." I have been able to verify every requirement in the errorstring except the "offline" requirement, where I don't really know what that means. I'm pretty darn sure its not offline though.

我有脚本在下面创建目标表，视图和索引。在下面的示例中，我并不真的需要一个视图，因为它试图将问题隔离开来。

I have the script to create the target table, view, and index below. I do not really need a view in the sample below, it is simplified as I try to isolate the issue.

DROP VIEW [dbo].[ProductSearchView]
DROP TABLE [dbo].[Product2]
GO


SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,
    QUOTED_IDENTIFIER, ANSI_NULLS ON;
GO

CREATE TABLE [dbo].[Product2](
    [Id] [bigint] NOT NULL,
    [Description] [nvarchar](max) NULL,
    CONSTRAINT [PK_Product2] PRIMARY KEY CLUSTERED 
    (
        [Id] ASC
    )WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE VIEW [dbo].[ProductSearchView] WITH SCHEMABINDING
AS
SELECT   P.Id AS Id,  
         P.Description AS Field
FROM [dbo].Product2 AS P
GO

-- this index may be overkill given the PK is set...
CREATE UNIQUE CLUSTERED INDEX PK_ProductSearchView ON [dbo].[ProductSearchView](Id)
GO

-- This is the command that fails
CREATE FULLTEXT INDEX ON [dbo].[ProductSearchView](Id, Field)
KEY INDEX Id
ON FullText WITH CHANGE_TRACKING AUTO;
GO

推荐答案

您需要指定索引的名称而不是列创建全文索引时的名称：

You need to specify the name of the index instead of the column name when creating the fulltext index:

CREATE FULLTEXT INDEX ON [dbo].[ProductSearchView](Id, Field)
KEY INDEX PK_ProductSearchView
ON FullText WITH CHANGE_TRACKING AUTO;
GO

这样可以解决您收到的错误，但会给您另一个错误因为你正试图在你的文本搜索中包含一个非字符的列。您可能需要选择另一个索引字符列来代替全文目录。

This will remedy the error you are getting, but it will give you another error because you are trying to include a non-character based column in your text search. You may want to choose another indexed character column to use in your full text catalog instead.

这篇关于在SQL Server 2005的视图上创建全文索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！