Also, it is possible to create non-clustered indexes on a view, providing more possibilities to enhance the queries calling the view.
Indexes can only be created on views which have the same owner as the referenced table or tables. This is also called an intact ownership-chain between the view and the table(s). Typically, when table and view reside within the same schema, the same schema-owner applies to all objects within the schema.
To create a full text index choose your table and right click on that table and select “Define Full-Text Index” option. Now select Unique Index. It is compulsory that for “Full Text Index” table must have at least one unique index. Select columns name and language types for columns.
Full-text indexes are created on text-based columns ( CHAR , VARCHAR , or TEXT columns) to speed up queries and DML operations on data contained within those columns. A full-text index is defined as part of a CREATE TABLE statement or added to an existing table using ALTER TABLE or CREATE INDEX .
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With