SQL code
/*
在库TEST上建立全文索引
*/
use test
create table poofly(id int not null, name varchar(10))
go
/* 首先创建一个唯一索引，以便全文索引利用*/
create unique clustered index un_ky1 on poofly(id)
/*创建全文目录*/
create FULLTEXT CATALOG FT1 AS DEFAULT
/*C创建全文索引*/
create FULLTEXT INDEX ON poofly(NAME) key index un_ky1 ON FT1
/*修改全文目录*/
alter FULLTEXT CATALOG FT1 REBUILD
/*删除全文目录FT(含有全文索引时候不能删除)*/
drop fulltext catalog ft
/*查看数据库所有的全文目录*/
select* from sys.fulltext_catalogs
/*
fulltext_catalog_id name
-- DROP TABLE PROD_PRODUCT
IF OBJECT_ID(\'PROD_PRODUCT\',\'U\') IS NULL
BEGIN
CREATE TABLE PROD_PRODUCT
(
ProductPKId Int identity(1000,1) NOT NULL ,
ProductName varchar(100) NOT NULL ,
CatagoryId Int NOT NULL ,
ItemNumber varchar(30) NOT NULL ,
DetailDescFile varchar(300) NOT NULL ,
BrandId Int NOT NULL ,
MetricUnit Varchar(20) NOT NULL ,
RetailPrice money NOT NULL ,
VipPrice money NOT NULL ,
Stock Int NOT NULL ,
ShowStock Int NOT NULL ,
Quota Int NOT NULL ,
BeginFlag INT NOT NULL,
DefaultImage Int NULL ,
ProductState int NOT NULL ,
BeginDateTime datetime NULL ,
FreeDateTime datetime NULL ,
CountDownDateTime datetime NULL ,
Discount Int NOT NULL ,
CreateDateTime datetime NOT NULL ,
CreatePerson int NOT NULL ,
UpdateDateTime datetime NOT NULL ,
UpdatePerson int NOT NULL ,
BrowseTimes int NOT NULL
)
CREATE CLUSTERED INDEX Idx_CreateDateTime ON PROD_PRODUCT(CreateDateTime )
--CREATE UNIQUE INDEX Idx_ProductPKId
ALTER TABLE PROD_PRODUCT
ADD CONSTRAINT PK_PROD_PRODUCT PRIMARY KEY(ProductPKId)
--创建全文索引目录
CREATE FULLTEXT CATALOG FT_PRODUCT_INFO AS DEFAULT
--创建全文索引
CREATE FULLTEXT INDEX ON PROD_PRODUCT(ProductName) KEY INDEX PK_PROD_PRODUCT ON FT_PRODUCT_INFO
CREATE INDEX Idx_CatagoryId ON PROD_PRODUCT(CatagoryId)
CREATE INDEX Idx_ProductState ON PROD_PRODUCT(ProductState)
END
select* from sys.fulltext_catalogs