sql创建视图全文搜索[完整版]

sql创建视图全文搜索[完整版]

 

[sql] 
---每个表都要设有主键。。。。  
[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  
  
  
        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  
  
*/  
  
/*没有为当前数据库启用全文搜索。请使用 sp_fulltext_database 来启用数据库的全文搜索。*/  
--exec sp_fulltext_database 'enable';  
  
/*创建全文目录*/  
--create fulltext catalog ftproductlist as default  
/* 首先创建一个唯一索引，以便全文索引利用*/  
--create unique clustered  index index_view_productlist on view_productlist(innerid)  
  
/*index_view_productlist' 不是可强制使用全文搜索键的有效索引 <pre name="code" class="sql">---查看每个表都要设有主键</pre>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(*,'"内六角"  
 or "内六角沉头机螺钉" or "m5*28" or "gb818"')