建立視圖全文檢索搜尋

--SET   NUMERIC_ROUNDABORT   OFF  --GO  --SET   ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL,ARITHABORT,QUOTED_IDENTIFIER,ANSI_NULLS   ON--GOCreate view view_ProductList WITH SCHEMABINDINGasSELECT     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 ShellTrumpetRemarkSpecialFROM         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_ProductListwhere 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  FTProductListselect * 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添加