创建视图全文搜索(一)

[sql] 
--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 c