Full Text index on a view.

Am I missing something here? SQL Server 2005 BOL states that one the
requirements for the CREATE INDEX on a view are:
"The view cannot include text, ntext, or image columns, even if they
are not referenced in the CREATE INDEX statement".
But to create a Full Text index on the text column referenced in the
view I need a unique clustered index that I can't create because the
view references a text column.
Vicious circles.
It must be on an indexed view. Here is an example of how to create one.
CREATE DATABASE CODE_SAMPLE4
GO
USE CODE_SAMPLE4
GO
CREATE TABLE CODE_SAMPLE4
(PK INT NOT NULL IDENTITY CONSTRAINT PRIMARYKEY PRIMARY KEY,
CHARCOL CHAR(20),
INTCOL INT DEFAULT 1)
GO
DECLARE @.INT INT
SET @.INT=1
WHILE @.INT<=100
BEGIN
INSERT INTO CODE_SAMPLE4 (CHARCOL) VALUES('TEST')
SELECT @.INT=@.INT+1
END
SET @.INT=0
WHILE @.INT<=100
BEGIN
INSERT INTO CODE_SAMPLE4 (CHARCOL,INTCOL) VALUES('TEST',@.INT+1)
SELECT @.INT=@.INT+1
END
GO
CREATE FULLTEXT CATALOG TEST AS DEFAULT
GO
CREATE FULLTEXT INDEX ON CODE_SAMPLE4 (CHARCOL) KEY INDEX PRIMARYKEY
GO
--below query erroneously does not return any rows
SELECT * FROM CODE_SAMPLE4 AS CS4 JOIN
CONTAINSTABLE (CODE_SAMPLE4,CHARCOL,'TEST',100)
AS FT ON FT.[KEY]=CS4.PK
where intcol>10
ORDER BY RANK DESC
GO
CREATE VIEW CODE_SAMPLE4_VIEW WITH SCHEMABINDING
AS
SELECT PK, CHARCOL, INTCOL FROM DBO.CODE_SAMPLE4 WHERE INTCOL>10
GO
CREATE UNIQUE CLUSTERED INDEX CODE_SAMPLE4_VIEW_INDEX ON
CODE_SAMPLE4_VIEW(PK)
GO
CREATE FULLTEXT INDEX ON CODE_SAMPLE4_VIEW (CHARCOL) KEY INDEX
CODE_SAMPLE4_VIEW_INDEX
GO
--below query returns all rows
SELECT * FROM CODE_SAMPLE4_VIEW AS CS4 JOIN CONTAINSTABLE
(CODE_SAMPLE4_VIEW,CHARCOL,'TEST',100)
AS FT ON FT.[KEY]=CS4.PK
where intcol>10
ORDER BY RANK DESC
GO
SELECT * FROM CODE_SAMPLE4_VIEW WHERE CONTAINS(*,'TEST')
GO
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Lee" <sqldba@.comcast.net> wrote in message
news:1182543834.843179.270800@.x35g2000prf.googlegr oups.com...
> Am I missing something here? SQL Server 2005 BOL states that one the
> requirements for the CREATE INDEX on a view are:
> "The view cannot include text, ntext, or image columns, even if they
> are not referenced in the CREATE INDEX statement".
> But to create a Full Text index on the text column referenced in the
> view I need a unique clustered index that I can't create because the
> view references a text column.
> Vicious circles.
>
|||Hilary,
That makes perfect sence but change the CHARCOL CHAR(20) to a data
type of Text and it doesn't workr. All is fine untill you CREATE
UNIQUE CLUSTERED INDEX CODE_SAMPLE4_VIEW_INDEX ON
CODE_SAMPLE4_VIEW(PK)
then it errors with:
Server: Msg 1942, Level 16, State 1, Line 1
Index cannot be created on view 'CODE_SAMPLE4_VIEW' because the view
contains text, ntext or image columns.
Lee.
|||Text is deprecated in SQL 2005. Change it to varchar(max) and it works.
CREATE DATABASE CODE_SAMPLE4
GO
USE CODE_SAMPLE4
GO
CREATE TABLE CODE_SAMPLE4
(PK INT NOT NULL IDENTITY CONSTRAINT PRIMARYKEY PRIMARY KEY,
CHARCOL varCHAR(max),
INTCOL INT DEFAULT 1)
GO
DECLARE @.INT INT
SET @.INT=1
WHILE @.INT<=100
BEGIN
INSERT INTO CODE_SAMPLE4 (CHARCOL) VALUES('TEST')
SELECT @.INT=@.INT+1
END
SET @.INT=0
WHILE @.INT<=100
BEGIN
INSERT INTO CODE_SAMPLE4 (CHARCOL,INTCOL) VALUES('TEST',@.INT+1)
SELECT @.INT=@.INT+1
END
GO
CREATE FULLTEXT CATALOG TEST AS DEFAULT
GO
CREATE FULLTEXT INDEX ON CODE_SAMPLE4 (CHARCOL) KEY INDEX PRIMARYKEY
GO
--below query erroneously does not return any rows
SELECT * FROM CODE_SAMPLE4 AS CS4 JOIN
CONTAINSTABLE (CODE_SAMPLE4,CHARCOL,'TEST',100)
AS FT ON FT.[KEY]=CS4.PK
where intcol>10
ORDER BY RANK DESC
GO
CREATE VIEW CODE_SAMPLE4_VIEW WITH SCHEMABINDING
AS
SELECT PK, CHARCOL, INTCOL FROM DBO.CODE_SAMPLE4 WHERE INTCOL>10
GO
CREATE UNIQUE CLUSTERED INDEX CODE_SAMPLE4_VIEW_INDEX ON
CODE_SAMPLE4_VIEW(PK)
GO
CREATE FULLTEXT INDEX ON CODE_SAMPLE4_VIEW (CHARCOL) KEY INDEX
CODE_SAMPLE4_VIEW_INDEX
GO
--below query returns all rows
SELECT * FROM CODE_SAMPLE4_VIEW AS CS4 JOIN CONTAINSTABLE
(CODE_SAMPLE4_VIEW,CHARCOL,'TEST',100)
AS FT ON FT.[KEY]=CS4.PK
where intcol>10
ORDER BY RANK DESC
GO
SELECT * FROM CODE_SAMPLE4_VIEW WHERE CONTAINS(*,'TEST')
GO
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Lee" <sqldba@.comcast.net> wrote in message
news:1182777066.943787.176260@.n60g2000hse.googlegr oups.com...
> Hilary,
> That makes perfect sence but change the CHARCOL CHAR(20) to a data
> type of Text and it doesn't workr. All is fine untill you CREATE
> UNIQUE CLUSTERED INDEX CODE_SAMPLE4_VIEW_INDEX ON
> CODE_SAMPLE4_VIEW(PK)
> then it errors with:
> Server: Msg 1942, Level 16, State 1, Line 1
> Index cannot be created on view 'CODE_SAMPLE4_VIEW' because the view
> contains text, ntext or image columns.
> Lee.
>
|||How would you handle the same situation in SQL 2000 with a text column
that would need to remain a Text column?
|||I thought we were talking SQL 2005. In SQL 2000 you can't full-text index a
view.
Am I missing something here?
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Lee" <sqldba@.comcast.net> wrote in message
news:1182785285.821430.47320@.u2g2000hsc.googlegrou ps.com...
> How would you handle the same situation in SQL 2000 with a text column
> that would need to remain a Text column?
>
|||No, your okay. I had ask about SQL 2005 and then SQL 2000. As for not
being able to ful-text a view. Guess I should have cought that.