执行全文索引的SQL脚本报错

[wxcnl]

declare @FTCPath3 varchar(200)
set @FTCPath3=N'C:\Full Text Catalog'

if (select DATABASEPROPERTY(DB_NAME(), N'IsFullTextEnabled')) <> 1
exec sp_fulltext_database N'enable'

if not exists (select * from dbo.sysfulltextcatalogs where name = N'FT_NewsLetter')
EXEC dbo.sp_fulltext_catalog @ftcat=N'FT_NewsLetter', @action=N'create', @path=@FTCPath3

IF (OBJECTPROPERTY(OBJECT_ID(N'[dbo].[NewsLetters]'), 'TableFullTextCatalogId') = 0)
EXEC dbo.sp_fulltext_table @tabname=N'[dbo].[NewsLetters]', @action=N'create', @keyname=N'PK_NewsLetters', @ftcat=N'FT_NewsLetter'

GO
declare @lcid int
select @lcid=lcid from master.dbo.syslanguages where alias=N'English'
EXEC dbo.sp_fulltext_column @tabname=N'[dbo].[NewsLetters]', @colname=N'Content', @action=N'add', @language=@lcid
GO
EXEC dbo.sp_fulltext_table @tabname=N'[dbo].[NewsLetters]', @action=N'start_change_tracking'
GO
EXEC dbo.sp_fulltext_table @tabname=N'[dbo].[NewsLetters]', @action=N'start_background_updateindex'
GO
执行第一便
Warning: Table '[dbo].[NewsLetters]' does not have the option 'text in row' enabled and has full-text indexed columns that are of type image, text, or ntext. Full-text change tracking cannot track WRITETEXT or UPDATETEXT operations performed on these columns.
什么意思？怎么解决？

执行第二编
Msg 15631, Level 16, State 1, Procedure sp_fulltext_table, Line 239
Full-text change tracking is currently enabled for table '[dbo].[NewsLetters]'.
Msg 15633, Level 16, State 1, Procedure sp_fulltext_table, Line 368
Full-text auto propagation is currently enabled for table '[dbo].[NewsLetters]'.
什么意思？怎么解决？

[fwacky]

不懂,期待高手！

[rouqu]

全文索引没有选中"enable change tracking"和"auto propagation"

[rouqu]

sorry 没看清sql语句 上面的回答错误

[rouqu]

Warning: Table '[dbo].[NewsLetters]' does not have the option 'text in row' enabled and has full-text indexed columns that are of type image, text, or ntext. Full-text change tracking cannot track WRITETEXT or UPDATETEXT operations performed on these columns.
---------
sp_tableoption默认text in row选项是OFF的 你对text/imaga列做FT索引 如果这一选项是ON FT的change tracking能够跟踪到对应的变化(做更新) 否则不能触发相应索引的更新

Msg 15631, Level 16, State 1, Procedure sp_fulltext_table, Line 239
Full-text change tracking is currently enabled for table '[dbo].[NewsLetters]'.
Msg 15633, Level 16, State 1, Procedure sp_fulltext_table, Line 368
Full-text auto propagation is currently enabled for table '[dbo].[NewsLetters]'.
------
之前已经给NewsLetters启用了选项开关 再次运行应该就会有这个提示

[rouqu]

我这边测了一下 如果你的change tracking & update index in background已经开启 再次运行
exec dbo.sp_fulltext_table @tabname=N'[dbo].[NewsLetters]', @action=N'start_change_tracking'
or
....@action=N'start_background_updateindex'
就会有这个错误

[WNASP]

declare @objid int
select @objid = object_id('NewsLetters', 'local')
select ObjectProperty(@objid, 'TableFulltextChangeTrackingOn'),
ObjectProperty(@objid, 'TableFulltextBackgroundUpdateIndexOn')

可以以此来判断在执行

[wxcnl]

第二个问题已经明白了，关键是第一个
执行第一便
Warning: Table '[dbo].[NewsLetters]' does not have the option 'text in row' enabled and has full-text indexed columns that are of type image, text, or ntext. Full-text change tracking cannot track WRITETEXT or UPDATETEXT operations performed on these columns.


查了一下帮助，说
启用 text in row 选项后，可以使用 TEXTPTR、READTEXT、UPDATETEXT 或 WRITETEXT 语句读取或修改表中存储的任何 text、ntext 或 image 值的部分
我可不可以理解为我使用了tracking，然后TEXTPTR、READTEXT、UPDATETEXT 就无法使用了，他这里只是一个警告而已，对我的全文检索没有影响？（我在实践中好像是没有什么影响的，就是担心会有，所以才问问的）

[wxcnl]

对了如何获取text in row的状态？
默认的是多少？

[rouqu]

默认text in row开关为OFF 即表中的BLOB数值不跟其他数据占用共同的data pages 如果打开之后根据可用空间(列空间分配)和sp_tableoption指定的数值进行计算 在范围之内的将和data pages共同占用data pages空间 一如char/varchar等 即不用寻找额外的Blob页面

[rouqu]

BOL
===
text in row supports the TEXTPTR, WRITETEXT, UPDATETEXT, and READTEXT functions.

[rouqu]

然后 对这个warning我的理解
Warning: Table '[dbo].[NewsLetters]' does not have the option 'text in row' enabled and has full-text indexed columns that are of type image, text, or ntext. Full-text change tracking cannot track WRITETEXT or UPDATETEXT operations performed on these columns.

BLOB列在'text in row'开关为ON的情形下能够track到update并作自动更新；若开关为OFF 即BLOB独立存储 相应的updatetext等操作FT不能自动跟踪到

[rouqu]

楼主 你可以这样去做个小测试

--create tables for comparing
create table tbA (
ID int identity(1,1),
coln ntext，
primary key (ID)
)

create table tbB (
ID int identity(1,1),
coln ntext，
primary key (ID)
)

--对A表充填测试5W行
declare @i int
set @i = 0
if @i < 50000
begin
insert into tbA (coln) select N'y.................................................' --可以长一些
set @i = @i + 1
end

--对B表充填测试5W行(略去)

--对表A和表B都添加全文索引 均设置trace change和update index in background为ON

--创建测试存储过程

create proc p1 as
begin tran
declare @p1 binary(16)
select @p1 = TEXTPTR(coln) from tbA
updatetext tbA.coln @p1 10 3 N'xxxxxxxxxxxxxxxxxxxxxxxxxx' --可以长一些
commit tran
go

create proc p2 as
begin tran
declare @p2 binary(16)
select @p2 = TEXTPTR(coln) from tbB
updatetext tbB.coln @p2 10 3 N'xxxxxxxxxxxxxxxxxxxxxxxxxx' --可以长一些 但保持相同
commit tran
go

exec p1
go
exec p2
go

--观察...\Program Files\Microsoft SQL Server\MSSQL$CRM\FTDATA\SQL______\Build\Indexer\NlFiles文件的变化情况