Skip to main content

SQL SERVER – 2008 – Creating Full Text Catalog and Full Text Search

Dear All;
Ref URL: 3ART Technologies Support Services URL


Full Text Index helps to perform complex queries against character data.  These queries can include word or phrase searching. We can create a full-text index on a table or indexed view in a database. Only one full-text index is allowed per table or indexed view. The index can contain up to 1024 columns. Software developer Monica who helped with screenshots also informed that this feature works with RTM (Ready to Manufacture) version of SQL Server 2008 and does not work on CTP (Community Technology Preview) versions.
To create an Index, follow the steps:
  1. Create a Full-Text Catalog
  2. Create a Full-Text Index
  3. Populate the Index
1) Create a Full-Text Catalog







Full – Text can also be created while creating a Full-Text Index in its Wizard.
2) Create a Full-Text Index











3) Populate the Index

As the Index Is created and populated, you can write the query and use in searching records on that table which provides better performance.
For Example,
We will find the Employee Records who has “Marking “in their Job Title.
FREETEXT( ) Is predicate used to search columns containing character-based data types. It will not match the exact word, but the meaning of the words in the search condition. When FREETEXT is used, the full-text query engine internally performs the following actions on the freetext_string, assigns each term a weight, and then finds the matches.
  • Separates the string into individual words based on word boundaries (word-breaking).
  • Generates inflectional forms of the words (stemming).
  • Identifies a list of expansions or replacements for the terms based on matches in the thesaurus.
CONTAINS( ) is similar to the Freetext but with the difference that it takes one keyword to match with the records, and if we want to combine other words as well in the search then we need to provide the “and” or “or” in search else it will throw an error.
USE AdventureWorks2008GO
SELECT BusinessEntityIDJobTitleFROM HumanResources.EmployeeWHERE FREETEXT(*, 'Marketing Assistant');
SELECT BusinessEntityID,JobTitleFROM HumanResources.EmployeeWHERE CONTAINS(JobTitle'Marketing OR Assistant');
SELECT BusinessEntityID,JobTitleFROM HumanResources.EmployeeWHERE CONTAINS(JobTitle'Marketing AND Assistant');GO
Conclusion
Full text indexing is a great feature that solves a database problem, the searching of textual data columns for specific words and phrases in SQL Server databases. Full Text Index can be used to search words, phrases and multiple forms of word or phrase using FREETEXT() and CANTAINS() with “and” or “or” operators.

Regards 

Comments

Popular posts from this blog

OLEDB jet 4.0 driver In Vista 64bit / he 'Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine

Well i think you must be thankful for me specailly for this research i am really getting the solution after 6 month research that is how to enable the oledb jet 4.0 driver in vista, i read arround 100s of articles and maximum said that is not possible to enable it and ala bla well at the end i got the answer that is so easy Acctually that is correct that oledb jet 4.0 driver is not avaialble for 64 bit but if you run your IIS on 32 bit instead of 64 then Oledb jet will working fine Here are the steps Click on the Start > Program > Administrative Tool > IIS Management panel Select the Computer name Right click on the application pool and select properties Select "TRUE" in Enable 32 Bit Application by default it is false Then this problem will resolve if you need any assitance then feel free to email me rashidbilgrami@hotmail.com Regards Rashid Imran Bilgrami CEO Best visualization www.bestvisualization.com

How to create a search engine for your DotNetNuke site

Dear Readers today i found very interesting item now you can create your own search engine like google in just few steps by using dotnetnuke technology Original URL  http://www.wrensoft.com/zoom/support/tutorial_dnn.html thanks for zoom Zoom Search Engine is a software package that allows you to create a powerful custom search engine for your website. It is unlike other search solutions in that it gives you full control over its indexing capabilities, and there is no advertising or annual fees, while being one of the easiest to use and most feature packed solutions on the market. More on the advantages of using Zoom here . The following tutorial explains how to use Zoom with your DotNetNuke website. Since many DotNetNuke (DNN) users find the built-in search functionalities of DNN Search to be lacking, they have found that Zoom is far more capable in providing a much more effective (both in relevance and performance) search function to their DNN website. We provide

How to convert and crack windows server 2012 from Evaluation to Full

Dear All This is a way how you Convert Evalution to Full Step1: Open CMD and run following command DISM /online /Get-CurrentEdition <edition ID> is like ServerStandard with out Eval Step 2: DISM /online /Set-Edition:<edition ID> /ProductKey:XXXXX-XXXXX-XXXXX-XXXXX-XXXXX /AcceptEula WINDOWS SERVER 2012 Serial Key Windows Server 2012 DataCenter: 48HP8-DN98B-MYWDG-T2DCC-8W83P Datacenter: Y4TGP-NPTV9-HTC2H-7MGQ3-DV4TW Standard: XC9B7-NBPP2-83J2H-RHMBY-92BT4 Standard R2: DBGBW-NPF86-BJVTX-K3WKJ-MTB6V Server Essentials: K2XGM-NMBT3-2R6Q8-WF2FK-P36R2 For Standard R2 here is a command For R2 its like that DISM /online /Set-Edition:ServerStandard /ProductKey:DBGBW-NPF86-BJVTX-K3WKJ-MTB6V /AcceptEula Regards