以前只是听说,并没有试过,今天算是个开头.
--创建一个唯一索引
CREATE UNIQUE INDEX ix_tUser_vUserName ON [tUser]
(
vUserName ASC
)
GO
--打开数据库全文索引的支持
execute sp_fulltext_database 'enable'
GO
--建立全文目录ft_UserName
execute sp_fulltext_catalog 'ft_UserName', 'create'
GO
--为titles表建立全文索引数据元，IX_tUser_vUserName是唯一索引，可由sp_help IX_tUser_vUserName得知
execute sp_fulltext_table 'tUser','create', 'ft_UserName', 'IX_tUser_vUserName'
GO
--设置全文索引列名
exec sp_fulltext_column 'tUser', 'vUserName', 'add'
GO
--建立全文索引
exec sp_fulltext_table 'tUser', 'activate'
GO
--填充全文索引目录
exec sp_fulltext_catalog 'ft_UserName', 'start_full'
GO
--使用contains和freetext
SELECT *
FROM [tUser]
WHERE CONTAINS([vUserName], '"wang*"')
SELECT *
FROM [tUser]
WHERE FREETEXT(*, 'wangzg admin')
--DROP FULLTEXT CATALOG ft_UserName
--DROP FULLTEXT INDEX ON tuser
================================================================================
后续：
没想到上面的存储过程的方式早就过时了，唉，真是误人子弟！
CREATE FULLTEXT CATALOG ftCatalog_elWeb AS DEFAULT;
GO
CREATE FULLTEXT INDEX ON tUser(vUserName) KEY INDEX pk_tUser;
GO
ALTER FULLTEXT INDEX ON [dbo].[tUser] add ([vNickName])
GO
ALTER FULLTEXT INDEX ON [dbo].[tUser] DROP ([vNickName])
GO
