Getting started with Microsoft SQL ServerOVER ClausePIVOT / UNPIVOTDatabase SnapshotsRetrieve information about the databaseThe STUFF FunctionFOR XML PATHCursorsJoinCommon Table ExpressionsMove and copy data around tablesDatesLimit Result SetRetrieve Information about your InstanceWith Ties Option VariablesJSON in Sql ServerWindow functionsPartitioningStored ProceduresGROUP BYGenerating a range of datesCOALESCESplit String function in Sql ServerINSERT INTOCREATE VIEWString FunctionsResource GovernorORDER BYWHILE loopSystem database - TempDbMigrationPrimary KeysMERGEFull-Text IndexingFOR JSONSELECT statementDBMAILIndexQueries with JSON dataStoring JSON in SQL tablesOPENJSONRanking FunctionsTriggerConverting data typesNULLsTransaction isolation levelsAdvanced optionsIF...ELSETRY/CATCHData TypesUser Defined Table TypesTable Valued ParametersIn-Memory OLTP (Hekaton)Temporal TablesInsertSequencesSCOPE_IDENTITY()ViewsUse of TEMP TableScheduled Task or JobIsolation levels and lockingSorting/ordering rowsPrivileges or PermissionsForeign KeysSQLCMDFile Groupcross applyBasic DDL Operations in MS SQL ServerComputed ColumnsUNIONSubqueriesLast Inserted IdentityCLUSTERED COLUMNSTOREParsenameInstalling SQL Server on WindowsAggregate FunctionsQuerying results by pageSchemasBackup and Restore DatabaseTransaction handlingNatively compiled modules (Hekaton)Database permissionsSpatial DataDynamic SQLPaginationQuery HintsModify JSON textRow-level securityDynamic data maskingExport data in txt file by using SQLCMDEncryptionManaging Azure SQL DatabaseCommon Language Runtime IntegrationDelimiting special characters and reserved wordsCASE StatementDBCCBULK ImportQuery StoreService brokerAnalyzing a QueryMicrosoft SQL Server Management Studio Shortcut KeysPermissions and SecurityPHANTOM readFilestreamDrop KeywordString Aggregate functions in SQL ServerSQL Server Evolution through different versions (2000 - 2016)SQL Server Management Studio (SSMS)Logical FunctionsDynamic SQL PivotAlias Names in Sql Serverbcp (bulk copy program) Utility

Full-Text Indexing

Other topics

A. Creating a unique index, a full-text catalog, and a full-text index

The following example creates a unique index on the JobCandidateID column of the HumanResources.JobCandidate table of the AdventureWorks2012 sample database. The example then creates a default full-text catalog, ft. Finally, the example creates a full-text index on the Resume column, using the ft catalog and the system stoplist.

USE AdventureWorks2012;  
GO  
CREATE UNIQUE INDEX ui_ukJobCand ON HumanResources.JobCandidate(JobCandidateID);  
CREATE FULLTEXT CATALOG ft AS DEFAULT;  
CREATE FULLTEXT INDEX ON HumanResources.JobCandidate(Resume)   
   KEY INDEX ui_ukJobCand   
   WITH STOPLIST = SYSTEM;  
GO 

https://www.simple-talk.com/sql/learn-sql-server/understanding-full-text-indexing-in-sql-server/

https://msdn.microsoft.com/en-us/library/cc879306.aspx

https://msdn.microsoft.com/en-us/library/ms142571.aspx

Creating a full-text index on several table columns

USE AdventureWorks2012;  
GO  
CREATE FULLTEXT CATALOG production_catalog;  
GO  
CREATE FULLTEXT INDEX ON Production.ProductReview  
 (   
  ReviewerName  
     Language 1033,  
  EmailAddress  
     Language 1033,  
  Comments   
     Language 1033       
 )   
  KEY INDEX PK_ProductReview_ProductReviewID   
      ON production_catalog;   
GO  

Creating a full-text index with a search property list without populating it

USE AdventureWorks2012;  
GO  
CREATE FULLTEXT INDEX ON Production.Document  
  (   
  Title  
      Language 1033,   
  DocumentSummary  
      Language 1033,   
  Document   
      TYPE COLUMN FileExtension  
      Language 1033   
  )  
  KEY INDEX PK_Document_DocumentID  
          WITH STOPLIST = SYSTEM, SEARCH PROPERTY LIST = DocumentPropertyList, CHANGE_TRACKING OFF, NO POPULATION;  
   GO  

And populating it later with

ALTER FULLTEXT INDEX ON Production.Document SET CHANGE_TRACKING AUTO;  
GO  

Full-Text Search

SELECT product_id   
FROM products   
WHERE CONTAINS(product_description, ”Snap Happy 100EZ” OR FORMSOF(THESAURUS,’Snap Happy’) OR ‘100EZ’)   
AND product_cost < 200 ;  


SELECT candidate_name,SSN   
FROM candidates   
WHERE CONTAINS(candidate_resume,”SQL Server”) AND candidate_division =DBA;  

For more and detailed info https://msdn.microsoft.com/en-us/library/ms142571.aspx

Contributors

Topic Id: 4557

Example Ids: 15988,15989,15990,15991

This site is not affiliated with any of the contributors.