if OBJECT_ID('full_text_demo','U') is null
create table full_text_demo(
aid int identity(1,1),
aname varchar(255),
aaddress varchar(255),
adate datetime,
amemo text);
go
IF NOT EXISTS (SELECT * FROM sysfulltextcatalogs ftc WHERE ftc.name = N'全文索引目录')
CREATE FULLTEXT CATALOG [全文索引目录]WITH ACCENT_SENSITIVITY = ON
go
IF not EXISTS (SELECT * FROM sys.fulltext_indexes fti WHERE fti.object_id = OBJECT_ID(N'[full_text_demo]'))
CREATE FULLTEXT INDEX ON [full_text_demo](
[aaddress] LANGUAGE [Simplified Chinese],
[amemo] LANGUAGE [Simplified Chinese],
[aname] LANGUAGE [Simplified Chinese])
KEY INDEX [PK_full_text_demo]ON ([全文索引目录], FILEGROUP [PRIMARY])
WITH (CHANGE_TRACKING = AUTO, STOPLIST = SYSTEM)
go
create view _rand as select rand() as _rand
go
create function get_rand_ch(@alen int)
returns varchar(8000)
as begin
declare @loop int = @alen
declare @ret varchar(8000) = ''
declare @abc float
while @loop > 0 begin
select @abc = _rand from _rand
set @ret += nchar(1 * 0x4E00 + @abc * (1 * 0x9FA5 - 1 * 0x4E00))
set @loop -= 1
end
return @ret
end
go
create procedure init_table @count int
as begin
declare @name varchar(255), @address varchar(255), @memo varchar(8000)
begin tran
while @count > 0
begin
set @name = dbo.get_rand_ch(rand() * 10)
set @address = dbo.get_rand_ch(rand() * 250)
set @memo = dbo.get_rand_ch(rand()* 1000)
insert into full_text_demo values(@name, @address, GETDATE(), @memo)
set @count -= 1
end
commit tran
end
exec init_table 100
select * from full_text_demo where contains(*, '白')