创建视图全文搜索

--SET   NUMERIC_ROUNDABORT   OFF  
--GO  
--SET   ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL,ARITHABORT,QUOTED_IDENTIFIER,ANSI_NULLS   ON
--GO

Create view view_ProductList WITH SCHEMABINDING
as
SELECT     ba_ItemNumber.InnerID, dbo.ba_ItemNumber.STInnerID, dbo.ba_ItemNumber.Material, dbo.ba_ShellTrumpet.Code, 
                      dbo.ba_ShellTrumpet.Name, dbo.ba_ShellTrumpet.CDescription, dbo.ba_ShellTrumpet.Vulgo, dbo.ba_ShellTrumpet.EDescription, dbo.ba_ShellTrumpet.Specification, 
                      dbo.ba_ShellTrumpet.Od, dbo.ba_ShellTrumpet.StandardClass, dbo.ba_ShellTrumpet.Diameter, dbo.ba_ShellTrumpet.PremiumOld, 
                      dbo.ba_ShellTrumpet.PremiumNew, dbo.ba_ItemNumber.InvoiceName, dbo.ba_ItemNumber.OutsidePriceOld, dbo.ba_ItemNumber.OutsidePriceNew, 
                      dbo.ba_ItemNumber.MainUnit, dbo.ba_ItemNumber.ViceUnit, dbo.ba_ItemNumber.Singlet, dbo.ba_ItemNumber.Bag, dbo.ba_ItemNumber.Box, 
                      dbo.ba_ItemNumber.Cartons, dbo.ba_ItemNumber.BoxSize, dbo.ba_ItemNumber.BoxGW, dbo.ba_ItemNumber.CartonsSize, dbo.ba_ItemNumber.CartonsGW, 
                      dbo.ba_ItemNumber.DrawingsUrl, dbo.ba_ItemNumber.WhetherStanding, dbo.ba_ItemNumber.MinimumOrderQuantity, dbo.ba_ItemNumber.CountLimit, 
                      dbo.ba_ItemNumber.CountLower, dbo.ba_ItemNumber.MallDisplayNumber, dbo.ba_ItemNumber.StoreMaxNumber, dbo.ba_ItemNumber.GeneralDelivery, 
                      dbo.ba_ItemNumber.ExceedDelivery, dbo.ba_ItemNumber.SurfaceTreatment, dbo.ba_ItemNumber.Tage, dbo.ba_ItemNumber.HeatTreatment, 
                      dbo.ba_ItemNumber.StrengthGrade, dbo.ba_ItemNumber.TheCorrespondingMold, dbo.ba_ItemNumber.Remark, dbo.ba_ItemNumber.RemarkSpecial, 
                      dbo.ba_ItemNumber.RemarkSpecialInside, dbo.ba_ItemNumber.MiniPackageUnit, dbo.ba_ItemNumber.MiniPackageQuantity, dbo.ba_ItemNumber.Inventory, 
                      dbo.ba_ItemNumber.BulkInventory, dbo.ba_ItemNumber.DefectiveInventory, dbo.ba_ItemNumber.BarCode, dbo.ba_ShellTrumpet.ToothTypeBig, 
                      dbo.ba_ShellTrumpet.HeadType, dbo.ba_ShellTrumpet.Groove, dbo.ba_ShellTrumpet.SimilarStandard, dbo.ba_ShellTrumpet.IsDZP, 
                      dbo.ba_ItemNumber.StorageLocationFinished, dbo.ba_ItemNumber.StorageLocationBulk, dbo.ba_ItemNumber.pCXM, 
                      dbo.ba_ShellTrumpet.Remark AS ShellTrumpetRemark, dbo.ba_ShellTrumpet.RemarkSpecial AS ShellTrumpetRemarkSpecial
FROM         dbo.ba_ShellTrumpet INNER JOIN
                      dbo.ba_ItemNumber ON dbo.ba_ShellTrumpet.InnerID = dbo.ba_ItemNumber.STInnerID

---建立索引 材质，品名，规格，条码
/*
Create UNIQUE CLUSTERED INDEX index_view_ProductList ON view_ProductList (InnerID,STInnerID,Material,Specification,CDescription,BarCode)
--Create UNIQUE CLUSTERED INDEX index_ProductList ON view_ProductList (InnerID)

Set ANSI_NULLS ON
        SET ANSI_PADDING ON
        SET ANSI_WARNINGS ON
        SET ARITHABORT ON
        SET CONCAT_NULL_YIELDS_NULL ON
        SET QUOTED_IDENTIFIER ON
        SET NUMERIC_ROUNDABORT OFF

*/

select * from view_ProductList
where freetext(*,'GB818')


/* 首先创建一个唯一索引，以便全文索引利用*/
create unique clustered  index index_view_ProductList on view_ProductList(InnerID)
/*创建全文目录*/
create FULLTEXT CATALOG FTProductList AS DEFAULT
/*C创建全文索引
create FULLTEXT INDEX ON poofly(NAME) key index un_ky1 ON  FT1
/*修改全文目录*/
alter FULLTEXT CATALOG FTProductList  REBUILD
/*删除全文目录FT(含有全文索引时候不能删除)*/
drop fulltext catalog FTProductList
如果全文目录正处于填充状态，则等待30秒后再检测一次
WHILE FulltextCatalogProperty('FTProductList','PopulateStatus')<>0 
BEGIN 
--如果全文目录正处于填充状态，则等待30秒后再检测一次 
WAITFOR DELAY '0:0:5' 
END 
*/
create FULLTEXT INDEX ON view_ProductList(InnerID,[Name],STInnerID,Material,Specification,CDescription,BarCode) key index index_view_ProductList ON  FTProductList


select * from view_ProductList where CONTAINS(*,'内六角 | 内六角沉头机螺钉')
select * from view_ProductList where contains(*,'"内六角" or "内六角沉头机螺钉" or "M5*28" or "GB818"')

----分页控制

exec proc_DataPagingList
'view_ProductList',      ----要显示的表或多个表的连接
'*', ----要显示的字段列表
20 ,    ----每页显示的记录个数
1,                ----要显示那一页的记录
1,     ----output查询结果分页后的总页数
1 ,        ----output查询到的总记录数
'',----排序字段列表或条件
1,                 ----排序方法，0为升序，1为降序--程序传参如：' SortA Asc,SortB Desc,SortC ')
'freetext(*,''内六角平圆头机螺钉'')',----查询条件,不需WHERE
'InnerID',          ----主表的主键
0              ----是否添加查询字段的 DISTINCT 默认0不添加/1添加