sql server full text search: 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
[quoted text, click to view]

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?

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
[quoted text, click to view]

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
[quoted text, click to view]

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.