Esecuzione della query con ricerca Full-Text

Per definire ricerche full-text, le query full-text in SQL Server utilizzano i predicati full-text (CONTAINS e FREETEXT) e le funzioni full-text (CONTAINSTABLE e FREETEXTTABLE). Tali predicati e funzioni supportano la sintassi Transact-SQL avanzata che a sua volta supporta vari formati di termini di query. Per scrivere query full-text, è necessario sapere come e quando utilizzare questi predicati e queste funzioni.

Contenuto dell'argomento

• Cenni preliminari sui predicati full-text CONTAINS e FREETEXT

• Cenni preliminari sulle funzioni full-text CONTAINSTABLE e FREETEXTTABLE

• Utilizzo degli operatori booleani AND, OR e NOT in CONTAINS e CONTAINSTABLE

• Considerazioni aggiuntive per le query full-text

• Esecuzione di query su colonne di tipo varbinary(max) e xml

• Forme supportate di termini della query

• Visualizzazione del risultato della suddivisione in token di una combinazione di word breaker, thesaurus ed elenco di parole non significative

Cenni preliminari sui predicati full-text CONTAINS e FREETEXT

I predicati CONTAINS e FREETEXT restituiscono un valore TRUE o FALSE. Possono essere utilizzati solo per specificare i criteri di selezione per determinare se una determinata riga corrisponde alla query full-text. Le righe corrispondenti vengono restituite nel set di risultati. CONTAINS e FREETEXT vengono specificati nella clausola WHERE o HAVING di un'istruzione SELECT. Possono essere combinati con qualsiasi altro predicato Transact-SQL, ad esempio LIKE e BETWEEN.

[!NOTA]

Per informazioni sulla sintassi e sugli argomenti di questi predicati, vedere CONTAINS (Transact-SQL) e FREETEXT (Transact-SQL).

Quando si utilizza CONTAINS o FREETEXT, è possibile specificare una sola colonna, un elenco di colonne o tutte le colonne della tabella in cui eseguire la ricerca. Facoltativamente, è possibile specificare la lingua le cui risorse verranno utilizzate dalla query full-text specificata per il word breaking e lo stemming, le ricerche nel thesaurus e la rimozione di parole non significative.

CONTAINS e FREETEXT sono utili per tipi diversi di corrispondenze, come illustrato di seguito:

• Utilizzare CONTAINS (o CONTAINSTABLE) per corrispondenze precise o fuzzy (meno precise) a singole parole e frasi, la prossimità di parole entro una certa distanza una dall'altra o corrispondenze ponderate. Quando si utilizza CONTAINS, è necessario specificare almeno una condizione di ricerca che specifichi il testo che si desidera cercare e le condizioni che determinano le corrispondenze.

  È possibile utilizzare l'operazione logica tra condizioni di ricerca. Per ulteriori informazioni, vedere Utilizzo di operatori booleani AND, OR, AND NOT (in CONTAINS e CONTAINSTABLE), più avanti in questo argomento.

• Utilizzare FREETEXT (o FREETEXTTABLE) per trovare la corrispondenza tra il significato, ma non l'esatta formulazione, di parole e frasi specificate (stringa free-text). Vengono generate corrispondenze se nell'indice full-text di una colonna specificata viene trovato un qualsiasi termine o formato di un qualsiasi termine.

È possibile utilizzare un nome in quattro parti nel predicato CONTAINS o FREETEXT per eseguire query di colonne con indicizzazione full-text delle tabelle di destinazione in un server collegato. Per preparare un server remoto alla ricezione di query full-text, creare un indice full-text delle tabelle di destinazione e delle colonne nel server remoto, quindi aggiungere il server remoto come server collegato.

[!NOTA]

I predicati full-text non sono consentiti nella clausola OUTPUT quando il livello di compatibilità del database è impostato su 100.

[TORNA ALL'INIZIO]

Esempi

A.Utilizzo di CONTAINS con <simple_term>

Nell'esempio seguente vengono trovati tutti i prodotti il cui prezzo è $80.99 e contenenti la parola "Mountain".

USE AdventureWorks2012
GO

SELECT Name, ListPrice
FROM Production.Product
WHERE ListPrice = 80.99
   AND CONTAINS(Name, 'Mountain')
GO

B.Utilizzo di FREETEXT per la ricerca di parole contenenti valori di carattere specificati

Nell'esempio seguente viene eseguita la ricerca di tutti i documenti contenenti le parole associate a "vital", "safety" e "components".

USE AdventureWorks2012
GO

SELECT Title
FROM Production.Document
WHERE FREETEXT (Document, 'vital safety components')
GO

[TORNA ALL'INIZIO]

Cenni preliminari sulle funzioni full-text CONTAINSTABLE e FREETEXTTABLE

È possibile fare riferimento alle funzioni CONTAINSTABLE e FREETEXTTABLE come un normale nome di tabella nella clausola FROM di un'istruzione SELECT. Restituiscono una tabella di zero, una o più righe corrispondenti alla query full-text. La tabella restituita contiene solo righe della tabella di base che corrispondono ai criteri di selezione specificati nella condizione della ricerca full-text della funzione.

[!NOTA]

Per informazioni sulla sintassi e sugli argomenti di queste funzioni, vedere CONTAINSTABLE (Transact-SQL) e FREETEXTTABLE (Transact-SQL).

Le query che utilizzano una di queste funzioni restituiscono un valore di classificazione per pertinenza (RANK) e una chiave full-text (KEY) per ogni riga, come illustrato di seguito:

• Colonna KEY

  La colonna KEY restituisce valori univoci delle righe restituite. Può essere utilizzata per specificare i criteri di selezione.

• Colonna RANK

  La colonna RANK restituisce un valore di pertinenza per ogni riga che indica il livello di corrispondenza tra la riga e i criteri di selezione. Maggiore è il valore di pertinenza del testo o del documento in una riga, maggiore sarà la pertinenza della riga per una determinata query full-text. Si noti che è possibile classificare in modo identico righe diverse. È possibile limitare il numero di corrispondenze da restituire specificando il parametro top_n_by_rank facoltativo. Per ulteriori informazioni, vedere Limitazione dei risultati della ricerca mediante RANK.

Quando si utilizza una di queste funzioni, è necessario specificare la tabella di base in cui eseguire la ricerca full-text. Come con i predicati, è possibile specificare una singola colonna, un elenco di colonne o tutte le colonne della tabella in cui eseguire la ricerca e, facoltativamente, la lingua le cui risorse verranno utilizzate dalla query full-text specificata.

CONTAINSTABLE è utile per gli stessi tipi di corrispondenze di CONTAINS, mentre FREETEXTTABLE è utile per gli stessi tipi di corrispondenze di FREETEXT. Per ulteriori informazioni, vedere Cenni preliminari sui predicati full-text CONTAINS e FREETEXT, precedentemente in questo argomento. Quando si eseguono query che utilizzano le funzioni CONTAINSTABLE e FREETEXTTABLE, è necessario unire in join in modo esplicito le righe restituite alle righe della tabella di base SQL Server.

In genere, il risultato di CONTAINSTABLE o FREETEXTTABLE deve essere unito in join alla tabella di base. In questi casi, è necessario conoscere il nome della colonna chiave univoca. Questa colonna, presente in ogni tabella full-text, viene utilizzata per applicare righe univoche per la tabella (la colonna chiave univoca). Per ulteriori informazioni, vedere Gestione di indici full-text..

[TORNA ALL'INIZIO]

Esempi

A.Utilizzo di CONTAINSTABLE

Nell'esempio seguente vengono restituiti l'ID della descrizione e la descrizione di tutti i prodotti per cui la colonna Description contiene la parola "aluminum" accanto alla parola "light" o "lightweight". Vengono restituite solo le righe con un valore di pertinenza maggiore o uguale a 2.

USE AdventureWorks2012
GO

SELECT FT_TBL.ProductDescriptionID,
   FT_TBL.Description, 
   KEY_TBL.RANK
FROM Production.ProductDescription AS FT_TBL INNER JOIN
   CONTAINSTABLE (Production.ProductDescription,
      Description, 
      '(light NEAR aluminum) OR
      (lightweight NEAR aluminum)'
   ) AS KEY_TBL
   ON FT_TBL.ProductDescriptionID = KEY_TBL.[KEY]
WHERE KEY_TBL.RANK > 2
ORDER BY KEY_TBL.RANK DESC;
GO

B.Utilizzo di FREETEXTTABLE

Nell'esempio seguente viene estesa una query FREETEXTTABLE in modo che vengano restituite per prime le righe con valore di pertinenza maggiore e che la classificazione di ogni riga venga aggiunta all'elenco di selezione. Per specificare la query, è necessario sapere che ProductDescriptionID è la colonna chiave univoca per la tabella ProductDescription.

USE AdventureWorks2012
GO

SELECT KEY_TBL.RANK, FT_TBL.Description
FROM Production.ProductDescription AS FT_TBL 
     INNER JOIN
     FREETEXTTABLE(Production.ProductDescription, Description,
                    'perfect all-around bike') AS KEY_TBL
     ON FT_TBL.ProductDescriptionID = KEY_TBL.[KEY]
ORDER BY KEY_TBL.RANK DESC
GO

Di seguito viene riportata un'estensione della stessa query che restituisce solo le righe con valore di pertinenza maggiore o uguale a 10:

USE AdventureWorks2012
GO

SELECT KEY_TBL.RANK, FT_TBL.Description
FROM Production.ProductDescription AS FT_TBL 
     INNER JOIN
     FREETEXTTABLE(Production.ProductDescription, Description,
                    'perfect all-around bike') AS KEY_TBL
     ON FT_TBL.ProductDescriptionID = KEY_TBL.[KEY]
WHERE KEY_TBL.RANK >= 10
ORDER BY KEY_TBL.RANK DESC
GO

[TORNA ALL'INIZIO]

Utilizzo degli operatori booleani AND, OR e NOT in CONTAINS e CONTAINSTABLE

Il predicato CONTAINS e la funzione CONTAINSTABLE utilizzano le stesse condizioni di ricerca. Entrambi supportano la combinazione di più termini di ricerca tramite gli operatori booleani AND, OR, AND NOT per eseguire operazioni logiche. È ad esempio possibile utilizzare AND per trovare righe che contengono sia "latte" sia New York-style bagel". È possibile utilizzare AND NOT, ad esempio, per trovare le righe che contengono "bagel", ma non "cream cheese".

[!NOTA]

Al contrario, FREETEXT e FREETEXTTABLE considerano i termini booleani come parole da cercare.

Per informazioni sulla combinazione di CONTAINS con altri predicati che utilizzano gli operatori logici AND, OR, AND NOT, vedere Condizione di ricerca (Transact-SQL).

Esempio

Nell'esempio seguente viene utilizzata la tabella ProductDescription del database AdventureWorks2012 . Nella query viene utilizzato il predicato CONTAINS per cercare le descrizioni il cui ID sia diverso da 5 e contenenti le parole "Aluminum" e "spindle". Nella condizione di ricerca viene utilizzato l'operatore booleano AND.

USE AdventureWorks2012
GO

SELECT Description
FROM Production.ProductDescription
WHERE ProductDescriptionID <> 5 AND
   CONTAINS(Description, 'aluminum AND spindle')
GO

[TORNA ALL'INIZIO]

Considerazioni aggiuntive per le query full-text

Quando si scrivono query full-text, considerare anche gli elementi seguenti:

• Opzione LANGUAGE

  Molti termini di query dipendono in larga misura dal comportamento del word breaker. Per assicurarsi di utilizzare il word breaker (e lo stemmer) e il thesaurus corretti, è consigliabile specificare l'opzione LANGUAGE. Per ulteriori informazioni, vedere Scelta di una lingua durante la creazione di un indice full-text.

• Parole non significative

  Quando si definisce una query full-text, il motore di ricerca full-text ignora le parole non significative dai criteri di ricerca. Per parole non significative si intendono parole quali "circa", "con", "devo" e "cui" che possono ricorrere di frequente, ma che non hanno alcuna utilità nella ricerca di testo specifico. Le parole non significative vengono riunite in un elenco. Ogni indice full-text è associato a un elenco di parole non significative specifico per determinare quali di queste parole omettere dalla query o dall'indice durante l'indicizzazione. Per ulteriori informazioni, vedere Configurare e gestire parole non significative ed elenchi di parole non significative per la ricerca full-text.

• Thesaurus

  Per impostazione predefinita, le query FREETEXT e FREETEXTTABLE utilizzano il thesaurus. CONTAINS e CONTAINSTABLE supportano un argomento THESAURUS facoltativo.

• Distinzione maiuscole/minuscole

  Per le query di ricerca full-text non viene fatta distinzione tra maiuscole e minuscole. La lingua giapponese, tuttavia, prevede più forme fonetiche basate sul concetto di normalizzazione ortografica simile alla mancanza di distinzione tra maiuscole e minuscole, ad esempio kana = senza distinzione. Questo tipo di normalizzazione ortografica non è supportato.

[TORNA ALL'INIZIO]

Esecuzione di query su colonne di tipo varbinary(max) e xml

Se una colonna varbinary(max), varbinary o xml viene sottoposta a indicizzazione full-text, le query su questa colonna possono essere eseguite utilizzando predicati (CONTAINS e FREETEXT) e funzioni (CONTAINSTABLE e FREETEXTTABLE) full-text, proprio come su ogni altra colonna indicizzata full-text.

Importante
La ricerca full-text può anche essere eseguita con colonne di tipo image. Il tipo di dati image verrà comunque rimosso in una versione futura di SQL Server. Evitare pertanto di utilizzarlo nelle nuove attività di sviluppo e pianificare la modifica delle applicazioni che attualmente lo utilizzano. Utilizzare invece il tipo di dati varbinary(max).

dati varbinary(max) o varbinary

In una singola colonna varbinary(max) o varbinary possono essere archiviati vari tipi di documenti. SQL Server supporta qualsiasi tipo di documento per cui viene installato un filtro e disponibile nel sistema operativo. Il tipo di ogni documento è identificato dall'estensione file relativa. Per un'estensione file doc, ad esempio, la ricerca full-text utilizza il filtro che supporta i documenti di Microsoft Word. Per un elenco dei tipi di documento disponibili, eseguire una query sulla vista del catalogo sys.fulltext_document_types.

Si noti che il motore di ricerca full-text può utilizzare i filtri esistenti installati nel sistema operativo. Prima di poter utilizzare i filtri, i word breaker e gli stemmer del sistema operativo, è necessario caricarli nell'istanza del server, come illustrato di seguito:

EXEC sp_fulltext_service @action='load_os_resources', @value=1

Per creare un indice full-text in una colonna varbinary(max), il motore di ricerca full-text deve accedere alle estensioni file dei documenti nella colonna varbinary(max). Queste informazioni devono essere archiviate in una colonna di tabella, denominata colonna del tipo, che deve essere associata alla colonna varbinary(max) nell'indice full-text. Quando si esegue l'indicizzazione di un documento, il motore di ricerca full-text utilizza l'estensione del file nella colonna del tipo per identificare il filtro da utilizzare.

[TORNA ALL'INIZIO]

dati xml

Una colonna del tipo di dati xml archivia esclusivamente documenti e frammenti XML, inoltre per i documenti viene utilizzato soltanto il filtro XML. Una colonna del tipo non è pertanto necessaria. Nelle colonne xml l'indice full-text indicizza il contenuto degli elementi XML, ma ignora il markup XML. Ai valori di attributo viene applicata l'indicizzazione full-text a meno che non siano valori numerici. I tag elemento sono utilizzati come limiti del token. Sono supportati documenti e frammenti XML o HTML ben formati e contenenti più lingue.

Per ulteriori informazioni sull'esecuzione di query relative a una colonna xml, vedere Utilizzo della ricerca full-text con colonne XML.

[TORNA ALL'INIZIO]

Forme supportate di termini della query

Questa sezione contiene un riepilogo del supporto fornito per ciascuna forma di query dai predicati full-text e dalle funzioni con valori del set di righe.

[!NOTA]

Per ottenere la sintassi di un termine di query specificato, fare clic sui collegamenti corrispondenti nella colonna Supportata da della tabella seguente.

[TORNA ALL'INIZIO]

Ricerca di parole o frasi specifiche (termine semplice)

È possibile utilizzare CONTAINS, CONTAINSTABLE, FREETEXT o FREETEXTTABLE per cercare una frase specifica in una tabella. Se, ad esempio, si desidera eseguire una ricerca nella tabella ProductReview del database AdventureWorks2012 per individuare tutti i commenti su un prodotto utilizzando la frase "learning curve", è possibile utilizzare il predicato CONTAINS nel modo seguente:

USE AdventureWorks2012
GO

SELECT Comments
FROM Production.ProductReview
WHERE CONTAINS(Comments, '"learning curve"')
GO

La condizione di ricerca, in questo caso "learning curve", può essere molto complessa ed essere costituita da uno o più termini.

[TORNA ALL'INIZIO]

Esecuzione di ricerche di prefissi (termine di prefisso)

È possibile utilizzare CONTAINS o CONTAINSTABLE per cercare parole o frasi con un prefisso specificato. Vengono restituite tutte le voci nella colonna che contengono testo che inizia con il prefisso specificato. È possibile cercare, ad esempio, tutte le righe che contengono il prefisso top-, come in topple, topping e top. La query è la seguente:

USE AdventureWorks2012
GO

SELECT Description, ProductDescriptionID
FROM Production.ProductDescription
WHERE CONTAINS (Description, '"top*"' )
GO

La query restituisce tutto il testo che corrisponde a quello specificato prima dell'asterisco (*). Se il testo e l'asterisco non sono delimitati da virgolette doppie, come in CONTAINS (DESCRIPTION, 'top*'), la ricerca full-text non considera l'asterisco un carattere jolly.

Se il termine di prefisso è una frase, ogni token che la compone viene considerato come un termine di prefisso distinto e vengono restituite tutte le righe contenenti parole che iniziano con tali prefissi. Se, ad esempio, viene specificato il termine di prefisso "pennelli gialli*", vengono trovate le righe che contengono il testo "pennelli giallini", "pennellini giallini" o "pennelli gialli", ma non "pennelli sottili gialli".

[TORNA ALL'INIZIO]

Ricerca di forme flessive di una parola specifica (termine di generazione)

È possibile utilizzare CONTAINS, CONTAINSTABLE, FREETEXT o FREETEXTTABLE per cercare tutti i diversi tempi e coniugazioni di un verbo o le forme sia singolare sia plurale di un sostantivo (ricerca di forme flessive) oppure i sinonimi di una parola specifica (ricerca thesaurus).

Nell'esempio seguente vengono cercate tutte le forme del termine "piede" ("piede", "piedi" e così via) presenti nella colonna Comments della tabella ProductReview nel database AdventureWorks.

USE AdventureWorks2012
GO

SELECT Comments, ReviewerName
FROM Production.ProductReview
WHERE CONTAINS (Comments, 'FORMSOF(INFLECTIONAL, "foot")')
GO

[!NOTA]

Nella ricerca full-text vengono utilizzati gli stemmer, che consentono di cercare i diversi tempi e coniugazioni di un verbo o le forme sia singolare sia plurale di un sostantivo. Per ulteriori informazioni sugli stemmer, vedere Configurazione e gestione di word breaker e stemmer per la ricerca.

[TORNA ALL'INIZIO]

Ricerca di parole o frasi tramite valori ponderati (termine ponderato)

È possibile utilizzare CONTAINSTABLE per cercare parole o frasi e specificare un valore di ponderazione. Il peso, espresso come numero compreso tra 0,0 e 1,0, indica l'importanza di ogni parola o frase all'interno di un set di parole o frasi. Il valore 0,0 corrisponde al peso minimo, mentre il valore 1,0 corrisponde al peso massimo.

Nell'esempio seguente viene illustrata una query per la ricerca di tutti gli indirizzi dei clienti, tramite valori di ponderazione, in cui il testo che inizia con la stringa "Bay" contiene anche la parola "Street" o "View". I risultati assegnano un livello di importanza superiore alle righe che contengono il numero maggiore di parole specificate.

USE AdventureWorks2012
GO

SELECT AddressLine1, KEY_TBL.RANK 
FROM Person.Address AS Address INNER JOIN
CONTAINSTABLE(Person.Address, AddressLine1, 'ISABOUT ("Bay*", 
         Street WEIGHT(0.9), 
         View WEIGHT(0.1)
         ) ' ) AS KEY_TBL
ON Address.AddressID = KEY_TBL.[KEY]
ORDER BY KEY_TBL.RANK DESC
GO

È possibile utilizzare un termine ponderato insieme a qualsiasi termine semplice, termine di prefisso, termine di generazione o termine di prossimità.

[TORNA ALL'INIZIO]

Visualizzazione del risultato della suddivisione in token di una combinazione di word breaker, thesaurus ed elenco di parole non significative

Dopo aver applicato una determinata combinazione di word breaker, thesaurus ed elenco di parole non significative all'input di una stringa di query, è possibile visualizzare il risultato della suddivisione in token tramite la vista a gestione dinamica sys.dm_fts_parser. Per ulteriori informazioni, vedere sys.dm_fts_parser (Transact-SQL).

[TORNA ALL'INIZIO]

Vedere anche

Attivitá

Creazione di query di ricerca full-text (Visual Database Tools)

Riferimento

CONTAINS (Transact-SQL)

CONTAINSTABLE (Transact-SQL)

FREETEXT (Transact-SQL)

FREETEXTTABLE (Transact-SQL)

Concetti

Migliorare le prestazioni delle query full-text