sqlServer创建全文检索，解决内容like ‘%value%’ 查询慢的问题，等索引，和数据量大的情况查询总行数

use  kspolice;
--检查数据库是否支持全文检索
if(select databaseproperty('kspolice','isfulltextenabled'))=0  
  execute sp_fulltext_database 'enable' 
 --创建全文检索目录
 execute sp_fulltext_catalog 'FT_kspolice','create'
 --创建检索数据源
 execute sp_fulltext_table 'content','create','FT_kspolice','PK_Content' 
 --设置全文检索列名
 execute sp_fulltext_column 'content','content','add' 
 --建立全文索引，activate，是激活表的全文检索能力，也就是在全文目录中注册该表 
execute sp_fulltext_table 'content','activate' 
--填充全文索引目录 
execute sp_fulltext_catalog 'FT_kspolice','start_full' 
go 


--检查全文目录填充情况 
While fulltextcatalogproperty('FT_kspolice','populateStatus') <>0 
begin 
--如果全文目录正处于填充状态，则等待30秒后再检测一次 
waitfor delay '0:0:30' 
end 


--查询返回总行数
SELECT object_name(id) as TableName,indid,rows,rowcnt    
FROM sys.sysindexes WHERE id = object_id('content')   
and indid in (0,1); 


--创建唯一索引
create unique index ix_uni_Employee_ID on content(ID);
go


--创建覆盖索引
create index ix_cov_content_ID_webid on content (ID) include(webid);
go
create index ix_cov_content_ID_itemids on content (ID) include(itemids);
go
create index ix_cov_content_ID_IsDraft on content (ID) include(IsDraft);
go 
create index ix_cov_content_ID_Title on content (ID) include(Title);
go