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
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" <Letford@.discussions.microsoft.com> wrote in message
news:AAA3603B-F2E6-4FCB-838A-7FC07FF1CBEF@.microsoft.com...
> 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
2012年3月27日星期二
2012年3月26日星期一
full text search on views?
Can we buld full text search on views? If yes, can I build a search catalog on a view that allowed both varchar and text columns?
Hi Abhi,
I've asked help for Tony Ting which is full text expert and here are his comments:
Answer is yes, but you could only build fulltext on indexed view (materialized view) and column are limited to all available in normal case but not on text/ntext/image/xml column. The later constraint is came from the current limitation that index could not be created on a indexed view w/ text/ntext/image/xml, but fulltext column need a primary index to work on.
Hope this helps.
Thanks,
Ana Elisa - MSFT
full text search on views?
Can we buld full text search on views? If yes, can I build a search catalog on a view that allowed both varchar and text columns?
Hi Abhi,
I've asked help for Tony Ting which is full text expert and here are his comments:
Answer is yes, but you could only build fulltext on indexed view (materialized view) and column are limited to all available in normal case but not on text/ntext/image/xml column. The later constraint is came from the current limitation that index could not be created on a indexed view w/ text/ntext/image/xml, but fulltext column need a primary index to work on.
Hope this helps.
Thanks,
Ana Elisa - MSFT
full text search on views in express edition
Hi
Is full text indexing possible on views in SQL server express edition with
advanced services? If Yes then how to do it on a view where I want to do
full text search on five different columns?
Thanks
ontario, canada
I am fairly certain that you can only have one full-text index per table or
*indexed* view. So, if your view is not indexed, you will need to create
your FT index on the underlying table(s).
"db" <db@.discussions.microsoft.com> wrote in message
news:70DFE058-91C8-4D1D-96A7-277113460FB3@.microsoft.com...
> Hi
> Is full text indexing possible on views in SQL server express edition with
> advanced services? If Yes then how to do it on a view where I want to do
> full text search on five different columns?
> Thanks
> --
> ontario, canada
|||I created a full text index on the base table. Will use base table(L.B22) and
not view using syntex:
create fulltext index on L.B22
key index ui_fts22 on B221_fulltextcatalog
with change_tracking AUTO
Now I want to
1. View details about full text index in my database/table.
2. When I created index I created it on one column. I want to use full text
search on multiple columns. Can I add more columns and if yes how.
ontario, canada
"Aaron Bertrand [SQL Server MVP]" wrote:
> I am fairly certain that you can only have one full-text index per table or
> *indexed* view. So, if your view is not indexed, you will need to create
> your FT index on the underlying table(s).
>
>
> "db" <db@.discussions.microsoft.com> wrote in message
> news:70DFE058-91C8-4D1D-96A7-277113460FB3@.microsoft.com...
>
>
Is full text indexing possible on views in SQL server express edition with
advanced services? If Yes then how to do it on a view where I want to do
full text search on five different columns?
Thanks
ontario, canada
I am fairly certain that you can only have one full-text index per table or
*indexed* view. So, if your view is not indexed, you will need to create
your FT index on the underlying table(s).
"db" <db@.discussions.microsoft.com> wrote in message
news:70DFE058-91C8-4D1D-96A7-277113460FB3@.microsoft.com...
> Hi
> Is full text indexing possible on views in SQL server express edition with
> advanced services? If Yes then how to do it on a view where I want to do
> full text search on five different columns?
> Thanks
> --
> ontario, canada
|||I created a full text index on the base table. Will use base table(L.B22) and
not view using syntex:
create fulltext index on L.B22
key index ui_fts22 on B221_fulltextcatalog
with change_tracking AUTO
Now I want to
1. View details about full text index in my database/table.
2. When I created index I created it on one column. I want to use full text
search on multiple columns. Can I add more columns and if yes how.
ontario, canada
"Aaron Bertrand [SQL Server MVP]" wrote:
> I am fairly certain that you can only have one full-text index per table or
> *indexed* view. So, if your view is not indexed, you will need to create
> your FT index on the underlying table(s).
>
>
> "db" <db@.discussions.microsoft.com> wrote in message
> news:70DFE058-91C8-4D1D-96A7-277113460FB3@.microsoft.com...
>
>
full text search on views in express edition
Hi
Is full text indexing possible on views in SQL server express edition with
advanced services? If Yes then how to do it on a view where I want to do
full text search on five different columns?
Thanks
--
ontario, canadaI am fairly certain that you can only have one full-text index per table or
*indexed* view. So, if your view is not indexed, you will need to create
your FT index on the underlying table(s).
"db" <db@.discussions.microsoft.com> wrote in message
news:70DFE058-91C8-4D1D-96A7-277113460FB3@.microsoft.com...
> Hi
> Is full text indexing possible on views in SQL server express edition with
> advanced services? If Yes then how to do it on a view where I want to do
> full text search on five different columns?
> Thanks
> --
> ontario, canada|||I created a full text index on the base table. Will use base table(L.B22) and
not view using syntex:
create fulltext index on L.B22
key index ui_fts22 on B221_fulltextcatalog
with change_tracking AUTO
Now I want to
1. View details about full text index in my database/table.
2. When I created index I created it on one column. I want to use full text
search on multiple columns. Can I add more columns and if yes how.
ontario, canada
"Aaron Bertrand [SQL Server MVP]" wrote:
> I am fairly certain that you can only have one full-text index per table or
> *indexed* view. So, if your view is not indexed, you will need to create
> your FT index on the underlying table(s).
>
>
> "db" <db@.discussions.microsoft.com> wrote in message
> news:70DFE058-91C8-4D1D-96A7-277113460FB3@.microsoft.com...
> >
> > Hi
> >
> > Is full text indexing possible on views in SQL server express edition with
> > advanced services? If Yes then how to do it on a view where I want to do
> > full text search on five different columns?
> >
> > Thanks
> > --
> > ontario, canada
>
>
Is full text indexing possible on views in SQL server express edition with
advanced services? If Yes then how to do it on a view where I want to do
full text search on five different columns?
Thanks
--
ontario, canadaI am fairly certain that you can only have one full-text index per table or
*indexed* view. So, if your view is not indexed, you will need to create
your FT index on the underlying table(s).
"db" <db@.discussions.microsoft.com> wrote in message
news:70DFE058-91C8-4D1D-96A7-277113460FB3@.microsoft.com...
> Hi
> Is full text indexing possible on views in SQL server express edition with
> advanced services? If Yes then how to do it on a view where I want to do
> full text search on five different columns?
> Thanks
> --
> ontario, canada|||I created a full text index on the base table. Will use base table(L.B22) and
not view using syntex:
create fulltext index on L.B22
key index ui_fts22 on B221_fulltextcatalog
with change_tracking AUTO
Now I want to
1. View details about full text index in my database/table.
2. When I created index I created it on one column. I want to use full text
search on multiple columns. Can I add more columns and if yes how.
ontario, canada
"Aaron Bertrand [SQL Server MVP]" wrote:
> I am fairly certain that you can only have one full-text index per table or
> *indexed* view. So, if your view is not indexed, you will need to create
> your FT index on the underlying table(s).
>
>
> "db" <db@.discussions.microsoft.com> wrote in message
> news:70DFE058-91C8-4D1D-96A7-277113460FB3@.microsoft.com...
> >
> > Hi
> >
> > Is full text indexing possible on views in SQL server express edition with
> > advanced services? If Yes then how to do it on a view where I want to do
> > full text search on five different columns?
> >
> > Thanks
> > --
> > ontario, canada
>
>
2012年3月11日星期日
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.
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.
Full text index not tracking changes on an indexed view
We had a peculiar situation today where our lovely indexed view
obviously had textual data in it but some recently added rows were not
being returned in a CONTAINS query when they should have been.
Deleting and recreating the FT index fixed things immediately and I
can't seem to reproduce it but it worries me for our live deployment
(due imminently!).
Has anyone else seen this? Is it reproducible (and fixable?!)
Mark
Hi Mark,
Do you have a timestamp column in underlaying table(s) ?
Have you activated automatic change tracking for your index ?
Jean-Pierre Riehl
http://blog.djeepy1.net
http://www.bewise.fr
<mbedser@.gmail.com> wrote in message
news:1185555299.154393.224920@.d55g2000hsg.googlegr oups.com...
> We had a peculiar situation today where our lovely indexed view
> obviously had textual data in it but some recently added rows were not
> being returned in a CONTAINS query when they should have been.
> Deleting and recreating the FT index fixed things immediately and I
> can't seem to reproduce it but it worries me for our live deployment
> (due imminently!).
> Has anyone else seen this? Is it reproducible (and fixable?!)
> Mark
>
|||Change tracking does not require a timestamp column.
I think your problem will be solved if you do use change tracking (as the
previous poster points out).
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
"Jean-Pierre Riehl" <jean-pierre.riehl@.b-e-w-i-s-e.fr> wrote in message
news:1C0C02A3-0A2F-4294-97CA-59E453DE6780@.microsoft.com...
> Hi Mark,
> Do you have a timestamp column in underlaying table(s) ?
> Have you activated automatic change tracking for your index ?
> --
> Jean-Pierre Riehl
> http://blog.djeepy1.net
> http://www.bewise.fr
> <mbedser@.gmail.com> wrote in message
> news:1185555299.154393.224920@.d55g2000hsg.googlegr oups.com...
>
|||I apologize for my mistake. Incremental update requires a timestamp column
whereas change tracking doesn't.
Jean-Pierre Riehl
http://blog.djeepy1.net
http://www.bewise.fr
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23CQlfIT0HHA.1188@.TK2MSFTNGP04.phx.gbl...
> Change tracking does not require a timestamp column.
> I think your problem will be solved if you do use change tracking (as the
> previous poster points out).
> --
> 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
> "Jean-Pierre Riehl" <jean-pierre.riehl@.b-e-w-i-s-e.fr> wrote in message
> news:1C0C02A3-0A2F-4294-97CA-59E453DE6780@.microsoft.com...
>
obviously had textual data in it but some recently added rows were not
being returned in a CONTAINS query when they should have been.
Deleting and recreating the FT index fixed things immediately and I
can't seem to reproduce it but it worries me for our live deployment
(due imminently!).
Has anyone else seen this? Is it reproducible (and fixable?!)
Mark
Hi Mark,
Do you have a timestamp column in underlaying table(s) ?
Have you activated automatic change tracking for your index ?
Jean-Pierre Riehl
http://blog.djeepy1.net
http://www.bewise.fr
<mbedser@.gmail.com> wrote in message
news:1185555299.154393.224920@.d55g2000hsg.googlegr oups.com...
> We had a peculiar situation today where our lovely indexed view
> obviously had textual data in it but some recently added rows were not
> being returned in a CONTAINS query when they should have been.
> Deleting and recreating the FT index fixed things immediately and I
> can't seem to reproduce it but it worries me for our live deployment
> (due imminently!).
> Has anyone else seen this? Is it reproducible (and fixable?!)
> Mark
>
|||Change tracking does not require a timestamp column.
I think your problem will be solved if you do use change tracking (as the
previous poster points out).
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
"Jean-Pierre Riehl" <jean-pierre.riehl@.b-e-w-i-s-e.fr> wrote in message
news:1C0C02A3-0A2F-4294-97CA-59E453DE6780@.microsoft.com...
> Hi Mark,
> Do you have a timestamp column in underlaying table(s) ?
> Have you activated automatic change tracking for your index ?
> --
> Jean-Pierre Riehl
> http://blog.djeepy1.net
> http://www.bewise.fr
> <mbedser@.gmail.com> wrote in message
> news:1185555299.154393.224920@.d55g2000hsg.googlegr oups.com...
>
|||I apologize for my mistake. Incremental update requires a timestamp column
whereas change tracking doesn't.
Jean-Pierre Riehl
http://blog.djeepy1.net
http://www.bewise.fr
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23CQlfIT0HHA.1188@.TK2MSFTNGP04.phx.gbl...
> Change tracking does not require a timestamp column.
> I think your problem will be solved if you do use change tracking (as the
> previous poster points out).
> --
> 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
> "Jean-Pierre Riehl" <jean-pierre.riehl@.b-e-w-i-s-e.fr> wrote in message
> news:1C0C02A3-0A2F-4294-97CA-59E453DE6780@.microsoft.com...
>
2012年2月19日星期日
FTS ON View in Yukon ?
I am eargerly awaiting my MSDN Universal, until then I am very
interested to know if Yukon will allow a View to be FT Indexed ? I am
working on a scenario right as we speak that may be worth pushing off
for the moment if SQL 2005 Will support these
Anyone ?
Or do I just need to jump through the hoops as I would have before ?
Chris
My understanding is that only tables are indexable in Yukon.
"WertmanTheMad" <cwertman@.webchamps.com> wrote in message
news:13990782.0410130715.6790b181@.posting.google.c om...
>I am eargerly awaiting my MSDN Universal, until then I am very
> interested to know if Yukon will allow a View to be FT Indexed ? I am
> working on a scenario right as we speak that may be worth pushing off
> for the moment if SQL 2005 Will support these
> Anyone ?
> Or do I just need to jump through the hoops as I would have before ?
> Chris
|||Chris,
Yukon (SQL Server 2005) will support FT Indexing on Partition Views, i.e..
materialized views and not "normal" views.
See SQL 2000 BOL title "CREATE VIEW" for more information on Partition
Views. Is this type of view possible in your scenario?
Thanks,
John
"WertmanTheMad" <cwertman@.webchamps.com> wrote in message
news:13990782.0410130715.6790b181@.posting.google.c om...
> I am eargerly awaiting my MSDN Universal, until then I am very
> interested to know if Yukon will allow a View to be FT Indexed ? I am
> working on a scenario right as we speak that may be worth pushing off
> for the moment if SQL 2005 Will support these
> Anyone ?
> Or do I just need to jump through the hoops as I would have before ?
> Chris
|||Hmm, I dont think so, as my data cannot really be represented how the
Local Partitioned Views require but I will look into it further,
thanks
Chris
"John Kane" <jt-kane@.comcast.net> wrote in message news:<epl#$kUsEHA.2720@.TK2MSFTNGP12.phx.gbl>...[vbcol=seagreen]
> Chris,
> Yukon (SQL Server 2005) will support FT Indexing on Partition Views, i.e..
> materialized views and not "normal" views.
> See SQL 2000 BOL title "CREATE VIEW" for more information on Partition
> Views. Is this type of view possible in your scenario?
> Thanks,
> John
>
> "WertmanTheMad" <cwertman@.webchamps.com> wrote in message
> news:13990782.0410130715.6790b181@.posting.google.c om...
|||You're welcome, Chris,
While I need to confirm this with the SQL Server 2005 (Yukon) Community
Technology Previews (CTP) releases (post-Beta2 releases), it is possible
that some of the restrictions on Indexed (or Materialized) view might be
relaxed in Yukon, relative to SQL Server 2000 (Sphinx).
Regards,
John
"WertmanTheMad" <cwertman@.webchamps.com> wrote in message
news:13990782.0410150950.a082884@.posting.google.co m...
> Hmm, I dont think so, as my data cannot really be represented how the
> Local Partitioned Views require but I will look into it further,
> thanks
> Chris
>
> "John Kane" <jt-kane@.comcast.net> wrote in message
news:<epl#$kUsEHA.2720@.TK2MSFTNGP12.phx.gbl>...[vbcol=seagreen]
i.e..[vbcol=seagreen]
interested to know if Yukon will allow a View to be FT Indexed ? I am
working on a scenario right as we speak that may be worth pushing off
for the moment if SQL 2005 Will support these
Anyone ?
Or do I just need to jump through the hoops as I would have before ?
Chris
My understanding is that only tables are indexable in Yukon.
"WertmanTheMad" <cwertman@.webchamps.com> wrote in message
news:13990782.0410130715.6790b181@.posting.google.c om...
>I am eargerly awaiting my MSDN Universal, until then I am very
> interested to know if Yukon will allow a View to be FT Indexed ? I am
> working on a scenario right as we speak that may be worth pushing off
> for the moment if SQL 2005 Will support these
> Anyone ?
> Or do I just need to jump through the hoops as I would have before ?
> Chris
|||Chris,
Yukon (SQL Server 2005) will support FT Indexing on Partition Views, i.e..
materialized views and not "normal" views.
See SQL 2000 BOL title "CREATE VIEW" for more information on Partition
Views. Is this type of view possible in your scenario?
Thanks,
John
"WertmanTheMad" <cwertman@.webchamps.com> wrote in message
news:13990782.0410130715.6790b181@.posting.google.c om...
> I am eargerly awaiting my MSDN Universal, until then I am very
> interested to know if Yukon will allow a View to be FT Indexed ? I am
> working on a scenario right as we speak that may be worth pushing off
> for the moment if SQL 2005 Will support these
> Anyone ?
> Or do I just need to jump through the hoops as I would have before ?
> Chris
|||Hmm, I dont think so, as my data cannot really be represented how the
Local Partitioned Views require but I will look into it further,
thanks
Chris
"John Kane" <jt-kane@.comcast.net> wrote in message news:<epl#$kUsEHA.2720@.TK2MSFTNGP12.phx.gbl>...[vbcol=seagreen]
> Chris,
> Yukon (SQL Server 2005) will support FT Indexing on Partition Views, i.e..
> materialized views and not "normal" views.
> See SQL 2000 BOL title "CREATE VIEW" for more information on Partition
> Views. Is this type of view possible in your scenario?
> Thanks,
> John
>
> "WertmanTheMad" <cwertman@.webchamps.com> wrote in message
> news:13990782.0410130715.6790b181@.posting.google.c om...
|||You're welcome, Chris,
While I need to confirm this with the SQL Server 2005 (Yukon) Community
Technology Previews (CTP) releases (post-Beta2 releases), it is possible
that some of the restrictions on Indexed (or Materialized) view might be
relaxed in Yukon, relative to SQL Server 2000 (Sphinx).
Regards,
John
"WertmanTheMad" <cwertman@.webchamps.com> wrote in message
news:13990782.0410150950.a082884@.posting.google.co m...
> Hmm, I dont think so, as my data cannot really be represented how the
> Local Partitioned Views require but I will look into it further,
> thanks
> Chris
>
> "John Kane" <jt-kane@.comcast.net> wrote in message
news:<epl#$kUsEHA.2720@.TK2MSFTNGP12.phx.gbl>...[vbcol=seagreen]
i.e..[vbcol=seagreen]
订阅：
博文 (Atom)