full text search sql 2000 and indexed views

Hilary,

Can you give an example of this? I've tried to create an indexed view that
contains a text column and when I attempt to create the index I get the error:

Msg 1942, Level 16, State 1, Line 2
Cannot create index on view 'Test.dbo.FullTextView'. It contains text,
ntext, image or xml columns.

Here are my steps:


--Set the options to support indexed views.
SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,
QUOTED_IDENTIFIER, ANSI_NULLS ON;
GO
--Create view with schemabinding.
ALTER VIEW dbo.FullTextView
WITH SCHEMABINDING
AS
SELECT Table1.OrderID, TextField1, TextField2
FROM dbo.Table1 JOIN dbo.Table2
ON Table1.OrderID = Table2.OrderID
WHERE Table2.Status = 'Open'
GO
--Create an index on the view.
CREATE UNIQUE CLUSTERED INDEX idxFullTextView_TestIndex
ON dbo.FullTextView (OrderID);


When I run this, I get the error. I would love to be able to accomplish
what you are indicating in order to complete some performance testing since
currently the performance of full text searching frankly is horrible in the
situation I'm looking for.

Adam


"Hilary Cotter" wrote:

> You are correct, you can only full-text index indexed views in SQL 2005.
>
> --
> RelevantNoise.com - dedicated to mining blogs for business intelligence.
>
> 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
> "Letford" wrote in message
>
> > Hi,
> > Is it possible to do a full text catalogue on an indexed view in SQL 2000?
> >
> > It looks like you can only do tables and the functionality for indexed
> > views
> > is in SQL 2005, is this correct?
> >
> > Thanks
>
>
>
 >> Stay informed about: full text search sql 2000 and indexed views 

You can't full-text index views in SQL 2000.

"Adam P. Cassidy" wrote in
message
> Hilary,
>
> Can you give an example of this? I've tried to create an indexed view
> that
> contains a text column and when I attempt to create the index I get the
> error:
>
> Msg 1942, Level 16, State 1, Line 2
> Cannot create index on view 'Test.dbo.FullTextView'. It contains text,
> ntext, image or xml columns.
>
> Here are my steps:
>
>
> --Set the options to support indexed views.
> SET NUMERIC_ROUNDABORT OFF;
> SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,
> QUOTED_IDENTIFIER, ANSI_NULLS ON;
> GO
> --Create view with schemabinding.
> ALTER VIEW dbo.FullTextView
> WITH SCHEMABINDING
> AS
> SELECT Table1.OrderID, TextField1, TextField2
> FROM dbo.Table1 JOIN dbo.Table2
> ON Table1.OrderID = Table2.OrderID
> WHERE Table2.Status = 'Open'
> GO
> --Create an index on the view.
> CREATE UNIQUE CLUSTERED INDEX idxFullTextView_TestIndex
> ON dbo.FullTextView (OrderID);
>
>
> When I run this, I get the error. I would love to be able to accomplish
> what you are indicating in order to complete some performance testing
> since
> currently the performance of full text searching frankly is horrible in
> the
> situation I'm looking for.
>
> Adam
>
>
> "Hilary Cotter" wrote:
>
>> You are correct, you can only full-text index indexed views in SQL 2005.
>>
>> --
>> RelevantNoise.com - dedicated to mining blogs for business intelligence.
>>
>> 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
>> "Letford" wrote in message
>>
>> > Hi,
>> > Is it possible to do a full text catalogue on an indexed view in SQL
>> > 2000?
>> >
>> > It looks like you can only do tables and the functionality for indexed
>> > views
>> > is in SQL 2005, is this correct?
>> >
>> > Thanks
>>
>>
>>
 >> Stay informed about: full text search sql 2000 and indexed views 

This is a 2005 database. I assumed it wasn't possible on 2005 either, until
I saw this post. If it's possible with 2005, I'm trying to figure out where
in my example below I'm going wrong.

The caveat to this is that this WAS a 2000 database. As I mentioned I'm in
the testing process and so I took the backup of the database, restored it
onto a 2005 installation, set the compatibility level to 90 and attempted to
run this. Is that process not sufficient in order to accomplish this?

Adam

"Hilary Cotter" wrote:

> You can't full-text index views in SQL 2000.
>
> "Adam P. Cassidy" wrote in
> message
> > Hilary,
> >
> > Can you give an example of this? I've tried to create an indexed view
> > that
> > contains a text column and when I attempt to create the index I get the
> > error:
> >
> > Msg 1942, Level 16, State 1, Line 2
> > Cannot create index on view 'Test.dbo.FullTextView'. It contains text,
> > ntext, image or xml columns.
> >
> > Here are my steps:
> >
> >
> > --Set the options to support indexed views.
> > SET NUMERIC_ROUNDABORT OFF;
> > SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,
> > QUOTED_IDENTIFIER, ANSI_NULLS ON;
> > GO
> > --Create view with schemabinding.
> > ALTER VIEW dbo.FullTextView
> > WITH SCHEMABINDING
> > AS
> > SELECT Table1.OrderID, TextField1, TextField2
> > FROM dbo.Table1 JOIN dbo.Table2
> > ON Table1.OrderID = Table2.OrderID
> > WHERE Table2.Status = 'Open'
> > GO
> > --Create an index on the view.
> > CREATE UNIQUE CLUSTERED INDEX idxFullTextView_TestIndex
> > ON dbo.FullTextView (OrderID);
> >
> >
> > When I run this, I get the error. I would love to be able to accomplish
> > what you are indicating in order to complete some performance testing
> > since
> > currently the performance of full text searching frankly is horrible in
> > the
> > situation I'm looking for.
> >
> > Adam
> >
> >
> > "Hilary Cotter" wrote:
> >
> >> You are correct, you can only full-text index indexed views in SQL 2005.
> >>
> >> --
> >> RelevantNoise.com - dedicated to mining blogs for business intelligence.
> >>
> >> 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
> >> "Letford" wrote in message
> >>
> >> > Hi,
> >> > Is it possible to do a full text catalogue on an indexed view in SQL
> >> > 2000?
> >> >
> >> > It looks like you can only do tables and the functionality for indexed
> >> > views
> >> > is in SQL 2005, is this correct?
> >> >
> >> > Thanks
> >>
> >>
> >>
>
>
 >> Stay informed about: full text search sql 2000 and indexed views 

Try varchar(max)


create table table1 (OrderID int not null identity constraint Table1PK
primary key,
TextField1 varchar(max), TextField2 varchar(max))
GO
create table table2 (OrderID int not null identity constraint Table2PK
primary key,
Status varchar(20))
GO
--Create view with schemabinding.
create VIEW dbo.FullTextView
WITH SCHEMABINDING
AS
SELECT Table1.OrderID, TextField1, TextField2
FROM dbo.Table1 JOIN dbo.Table2
ON Table1.OrderID = Table2.OrderID
WHERE Table2.Status = 'Open'
GO
--Create an index on the view.
CREATE UNIQUE CLUSTERED INDEX idxFullTextView_TestIndex
ON dbo.FullTextView (OrderID);
GO


"Adam P. Cassidy" wrote in message

> This is a 2005 database. I assumed it wasn't possible on 2005 either,
> until
> I saw this post. If it's possible with 2005, I'm trying to figure out
> where
> in my example below I'm going wrong.
>
> The caveat to this is that this WAS a 2000 database. As I mentioned I'm
> in
> the testing process and so I took the backup of the database, restored it
> onto a 2005 installation, set the compatibility level to 90 and attempted
> to
> run this. Is that process not sufficient in order to accomplish this?
>
> Adam
>
> "Hilary Cotter" wrote:
>
>> You can't full-text index views in SQL 2000.
>>
>> "Adam P. Cassidy" wrote in
>> message
>> > Hilary,
>> >
>> > Can you give an example of this? I've tried to create an indexed view
>> > that
>> > contains a text column and when I attempt to create the index I get the
>> > error:
>> >
>> > Msg 1942, Level 16, State 1, Line 2
>> > Cannot create index on view 'Test.dbo.FullTextView'. It contains text,
>> > ntext, image or xml columns.
>> >
>> > Here are my steps:
>> >
>> >
>> > --Set the options to support indexed views.
>> > SET NUMERIC_ROUNDABORT OFF;
>> > SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,
>> > QUOTED_IDENTIFIER, ANSI_NULLS ON;
>> > GO
>> > --Create view with schemabinding.
>> > ALTER VIEW dbo.FullTextView
>> > WITH SCHEMABINDING
>> > AS
>> > SELECT Table1.OrderID, TextField1, TextField2
>> > FROM dbo.Table1 JOIN dbo.Table2
>> > ON Table1.OrderID = Table2.OrderID
>> > WHERE Table2.Status = 'Open'
>> > GO
>> > --Create an index on the view.
>> > CREATE UNIQUE CLUSTERED INDEX idxFullTextView_TestIndex
>> > ON dbo.FullTextView (OrderID);
>> >
>> >
>> > When I run this, I get the error. I would love to be able to
>> > accomplish
>> > what you are indicating in order to complete some performance testing
>> > since
>> > currently the performance of full text searching frankly is horrible in
>> > the
>> > situation I'm looking for.
>> >
>> > Adam
>> >
>> >
>> > "Hilary Cotter" wrote:
>> >
>> >> You are correct, you can only full-text index indexed views in SQL
>> >> 2005.
>> >>
>> >> --
>> >> RelevantNoise.com - dedicated to mining blogs for business
>> >> intelligence.
>> >>
>> >> 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
>> >> "Letford" wrote in message
>> >>
>> >> > Hi,
>> >> > Is it possible to do a full text catalogue on an indexed view in SQL
>> >> > 2000?
>> >> >
>> >> > It looks like you can only do tables and the functionality for
>> >> > indexed
>> >> > views
>> >> > is in SQL 2005, is this correct?
>> >> >
>> >> > Thanks
>> >>
>> >>
>> >>
>>
>>
 >> Stay informed about: full text search sql 2000 and indexed views