Tips Full text search

Table des Matières [Cacher/Visualiser]


Modifier

SQL 2000

1/ Sur une vue
Il faut que la vue soit schema bindée
CODE
Lors de la création
CREATE VIEW vTotalCustomerOrders
WITH SCHEMABINDING
AS
SELECT oh.CustomerID, SUM(LineTotal) AS OrdersTotalAmt, COUNT_BIG(*) AS TotalOrderLines
FROM Sales.SalesOrderDetail od
JOIN Sales.SalesOrderHeader oh
ON od.SalesOrderID=oh.SalesOrderID
GROUP BY oh.CustomerID
Après la création
TODO
DANS L'INTERFACE
Mettez vous en design de la vue, sur la colonne de droite propriété activer yes dans l'option shema bind.

Cette vue doit ensuite comporter un identifiant unique
CODE
CREATE UNIQUE CLUSTERED INDEX PK_MOVIEID
ON WebSearchMovies(MOVIEID)
Attention : quelques limitations si votre vue ne contient pas d'identifiant unique
- essayez d'en créer un en retirant des éléments de votre vue (no output)
- utlisez un incrément automatique sur la vue
- utilisez l'option groupby

TIPS VUE SQL 2005
- sur les vue la console de management ajout automatiquement TOP(100) PERCENT lorsque vous mettez un order by cela empèche la création d'index et donc de full texte search, retirer cet élément ou passer en code TSQL pour créer la vue
- si vous utilisez GROUP BY pour créer des éléments unique, vous devez placer dans la requete COUNT_BIG(*) AS NbLine pour identifier de manière unique chaque ligne, sans cela vous ne pourrez creer d'index
INTERFACE
Todo

PROBLEME POSSIBLE
1 Pas d'id unique sur votre vue
Cela peut arriver en fonction des critères de votre vue, un id unique dans la table ne signifie pas obligatoire que ce soit le cas sur la vue.
vous aurez alors l'erreur suivante
Msg 1505, Level 16, State 1, Line 1
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.WebSearchMovies' and the index name 'PK_MOVIEID'. The duplicate key value is (372).
The statement has been terminated.

2/ Sur une table
Appliquez les mêmes principes, ces principes doivent en théorie déjà l'être
SCRIPT DE CREATION DE VUE POUR LA VOD
SELECT dbo.TB_MOVIE.MOVIEID, dbo.TB_MOVIE.IS_AVAILABLE, dbo.TB_MOVIE.IS_META, COUNT_BIG(*) AS NbLine
FROM dbo.TB_CATEGORY INNER JOIN
dbo.TB_MOVIE_CATEGORY ON dbo.TB_CATEGORY.CATEGORYID = dbo.TB_MOVIE_CATEGORY.CATEGORYID INNER JOIN
dbo.TB_MOVIE ON dbo.TB_MOVIE_CATEGORY.MOVIEID = dbo.TB_MOVIE.MOVIEID
WHERE (dbo.TB_CATEGORY.VISIBLE_ON_WEB = 1) AND (dbo.TB_CATEGORY.IS_PUBLISHED = 1) AND (dbo.TB_CATEGORY.REDIRECT_URL IS NULL) AND
(dbo.TB_MOVIE.IS_AVAILABLE = 1) AND (dbo.TB_MOVIE.IS_META IS NULL) OR
(dbo.TB_CATEGORY.VISIBLE_ON_WEB = 1) AND (dbo.TB_CATEGORY.REDIRECT_URL IS NULL) AND (dbo.TB_MOVIE.IS_AVAILABLE = 1) AND
(dbo.TB_MOVIE.IS_META IS NULL) AND (dbo.TB_CATEGORY.PARENTCATEGORYID = - 1) OR
(dbo.TB_CATEGORY.VISIBLE_ON_WEB = 1)
GROUP BY dbo.TB_MOVIE.MOVIEID, dbo.TB_MOVIE.IS_AVAILABLE, dbo.TB_MOVIE.IS_META
SCRIPT CREATION INDEX UNIQUE
CREATE UNIQUE CLUSTERED INDEX PK_MOVIEID
ON WebSearchMovies(MOVIEID)
VERIFICATION DE COLONNES INDEXEES
sp_help_fulltext_columns 'TB_MOVIE'
SCRIPT DE CREATION DE FULL TEXTE SEARCH
Note : ce script est automatisé et fait pour être exécuter plusieurs fois de suite sans autres manipulation, il est donc très pratique pour faire varier des tests de recherche. Cad qu'il crée le backup intégral de la base avec le full texte search, l'active au besoin et populate le catalogue en prenant en compte les noise word, word break, langue.

Spécifique à la vod : le backup n'est pas activé, il est cependant nécessaire de backuper lors de l'activation de FTS si cet élément est en place


/*
Pour créer un autre CATALOGUE FTS

Vérifier que le répertoire est bien crée sur \\devsql01\fulltext\\
Ajouter/Remplacer les noms des colones : Titre Corps
  • /
declare @IsFullText as bit
declare @Catalogue as nvarchar(250)
declare @Table as nvarchar(250)
declare @Base as nvarchar(250)
declare @PrimaryKey as nvarchar(250)
declare @CataloguePath as nvarchar(250)
declare @ExistCatalogue as bit
VARIABLES A MODIFIER
set @Catalogue = 'WebSearchMovies' Le catalogue comporte le même nom que la table.
set @Table = 'WebSearchMovies' par défaut @Catalogue
set @Base = 'VOD' Base
set @PrimaryKey = 'PK_MOVIEID' Clé primaire
set @CataloguePath = 'D:\SQLData\'

use VOD
 SUPPRESSION DU CATALOGUE SI EXISTANT
BEGIN TRY
exec sp_help_fulltext_catalogs @Catalogue;
exec sp_fulltext_table @Table, 'drop'
select 'Catalogue table supprimé'
END TRY
BEGIN CATCH
SELECT 'Catalogue/Table non déjà existant'
END CATCH

BEGIN TRY
exec sp_fulltext_catalog @Catalogue, 'drop'
select 'Catalogue supprimé'
END TRY
BEGIN CATCH
SELECT 'Catalogue non déjà existant'
END CATCH



 BACKUP POUR CONSERVER LA COHERENCE DES BASES/BACKUPS
BACKUP LOG VOD TO
DISK = N'D:\SQLData\VOD_LOG.bak' WITH NOFORMAT, NOINIT,
NAME = N'VOD-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

 TEST DU FONCTIONNEMENT DU SERVICE
Select @IsFullText = databaseproperty(@Base ,'IsFulltextEnabled');
if (@IsFullText = 0)
BEGIN
Activation du full-text
select 'Activiation du full text'
exec sp_fulltext_database 'enable'
END

Select 'Création du catalogue et ajout des colonnes indexées'
 Création d'un catalogue full-text en spécifiant le chemin
exec sp_fulltext_catalog @Catalogue, 'create', @CataloguePath

Select 'Ajout de la table à indexer'
ajout d'une table à la liste des tables indexées pour le full-text
exec sp_fulltext_table @tabname=@Table, @action='create', @ftcat=@Catalogue , @keyname=@PrimaryKey
exec sp_fulltext_table @tabname='WebSearchMovies', @action='create', @ftcat='WebSearchMovies' , @keyname='PK_MOVIEID'
Select 'Ajout des colonnes'
Ajout d'une colonne à la liste des colonnes indexées pour le full-text
exec sp_fulltext_column @tabname = @Table, @colname ='PUBLISHER_TITLEVF', @action='add', @language=0x040c
exec sp_fulltext_column @tabname = @Table, @colname ='PUBLISHER_SUMMARY', @action='add', @language=0x040c

 BACKUP POUR CONSERVER LA COHERENCE DES BASES/BACKUPS
BACKUP LOG assistance TO
DISK = N'L:\BACKUP\LOG\assistance_LOG.bak' WITH NOFORMAT, NOINIT,
NAME = N'assistance-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
 Activate the full-text index
EXEC sp_fulltext_table @Table,'activate';

/* DEBUG
Creation du catalogue ( cad on démare le remplisage !)
exec Sp_fullText_Table @Catalogue, 'Start_change_tracking'
 Popolation en diferentielle automatique (Quand le server est en idle )
exec Sp_fullText_Table @Catalogue, 'Start_background_updateindex'
  • /
 Population manuelle du catalogue, remplace un sheduling
select 'Population manuelle intégration du catalogue'
exec sp_fulltext_catalog @Catalogue, 'start_full'
Attente de population du catalogue
WHILE(SELECT FullTextCatalogProperty(@Catalogue,'populatestatus')) <> 0
BEGIN
WAITFOR DELAY '00:00:02'
CONTINUE
END
Select 'Catalogue rempli'
 Verification du catalogue


Select 'Vérification dune recherche'
select RANK,A.* from WebSearchMovies as A,FREETEXTTABLE(TB_MOVIE,*,'transformer') where A.MOVIEID=KEY order by RANK desc

/* CONTOURNEMENT ERREUR SQL 2005

CAS 1
select 30 FicheID
from Fiches as article JOIN freetexttable (Fiches , * , 'Modem')
AS ct on article.FicheID = ct.KEY
CAS 2
Sp_fullText_Table 'Fiches', 'Update_index'
select Rank from FREETEXTTABLE(Fiches,*,'modem')
CAS 3
SELECT Fiches.FicheID FROM Fiches WHERE CONTAINS (*, '"Modem"')

AIDE
Vérification d'indexation
sp_help_fulltext_columns 'TB_MOVIE'
  • /

Modifier

SQL 2005

/*
Pour créer un autre CATALOGUE FTS

Vérifier que le répertoire est bien crée sur \\devsql01\fulltext\\
Ajouter/Remplacer les noms des colones : Titre Corps
  • /
declare @IsFullText as bit
declare @Catalogue as nvarchar(250)
declare @Table as nvarchar(250)
declare @Base as nvarchar(250)
declare @PrimaryKey as nvarchar(250)
declare @CataloguePath as nvarchar(250)
declare @ExistCatalogue as bit
VARIABLES A MODIFIER
set @Catalogue = 'VueFiches' Le catalogue comporte le même nom que la table.
set @Table = 'VueFiches' par défaut @Catalogue
set @Base = 'Assistance' Base
set @PrimaryKey = 'PK_Fiches' Clé primaire
set @CataloguePath = 'E:\fulltext\'

use Assistance
 SUPPRESSION DU CATALOGUE SI EXISTANT
BEGIN TRY
exec sp_help_fulltext_catalogs @Catalogue;
exec sp_fulltext_table @Table, 'drop'
select 'Catalogue table supprimé'
END TRY
BEGIN CATCH
SELECT 'Catalogue/Table non déjà existant'
END CATCH

BEGIN TRY
exec sp_fulltext_catalog @Catalogue, 'drop'
select 'Catalogue supprimé'
END TRY
BEGIN CATCH
SELECT 'Catalogue non déjà existant'
END CATCH



 BACKUP POUR CONSERVER LA COHERENCE DES BASES/BACKUPS
BACKUP LOG assistance TO
DISK = N'L:\BACKUP\LOG\assistance_LOG.bak' WITH NOFORMAT, NOINIT,
NAME = N'assistance-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

TEST DU FONCTIONNEMENT DU SERVICE
Select @IsFullText = databaseproperty(@Base ,'IsFulltextEnabled');
if (@IsFullText = 0)
BEGIN
 Activation du full-text
select 'Activiation du full text'
exec sp_fulltext_database 'enable'
END

Select 'Création du catalogue et ajout des colonnes indexées'
Création d'un catalogue full-text en spécifiant le chemin
exec sp_fulltext_catalog @Catalogue, 'create', @CataloguePath

Select 'Ajout de la table à indexer'
 ajout d'une table à la liste des tables indexées pour le full-text
exec sp_fulltext_table @tabname=@Table, @action='create', @ftcat=@Catalogue , @keyname=@PrimaryKey

Select 'Ajout des colonnes'
Ajout d'une colonne à la liste des colonnes indexées pour le full-text
exec sp_fulltext_column @tabname = @Table, @colname ='Titre', @action='add', @language=0x040c
exec sp_fulltext_column @tabname = @Table, @colname ='Accroche', @action='add', @language=0x040c

 BACKUP POUR CONSERVER LA COHERENCE DES BASES/BACKUPS
BACKUP LOG assistance TO
DISK = N'L:\BACKUP\LOG\assistance_LOG.bak' WITH NOFORMAT, NOINIT,
NAME = N'assistance-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
Activate the full-text index
EXEC sp_fulltext_table @Table,'activate';

/* DEBUG
Creation du catalogue ( cad on démare le remplisage !)
exec Sp_fullText_Table @Catalogue, 'Start_change_tracking'
Popolation en diferentielle automatique (Quand le server est en idle )
exec Sp_fullText_Table @Catalogue, 'Start_background_updateindex'
  • /
Population manuelle du catalogue, remplace un sheduling
select 'Population manuelle intégration du catalogue'
exec sp_fulltext_catalog @Catalogue, 'start_full'
 Attente de population du catalogue
WHILE(SELECT FullTextCatalogProperty(@Catalogue,'populatestatus')) <> 0
BEGIN
WAITFOR DELAY '00:00:02'
CONTINUE
END
Select 'Catalogue rempli'
Verification du catalogue

select RANK,A.* from GLOSSAIRE as A,FREETEXTTABLE(GLOSSAIRE,*,'modem') where A.ID=KEY order by RANK desc
/*

XML_Assistance_FullTextSearch 'Modem'
  • /
/* CONTOURNEMENT ERREUR SQL 2005

CAS 1
select 30 FicheID
from Fiches as article JOIN freetexttable (Fiches , * , 'Modem')
AS ct on article.FicheID = ct.KEY
CAS 2
Sp_fullText_Table 'Fiches', 'Update_index'
select Rank from FREETEXTTABLE(Fiches,*,'modem')
CAS 3
SELECT Fiches.FicheID FROM Fiches WHERE CONTAINS (*, '"Modem"')

AIDE
Vérification d'indexation
sp_help_fulltext_columns 'Fiches'
  • /
Modifier

Liste des commandes full text search


Vérification préalable : le full-text est il activé ?
select databaseproperty('nomdelabase' ,'IsFulltextEnabled');



Activation du full-text
use nomdelabase
exec sp_fulltext_database 'enable'



Création d'un catalogue full-text
exec sp_fulltext_catalog 'nomducatalogueacreer', 'create'



Création d'un catalogue full-text en spécifiant le chemin
exec sp_fulltext_catalog 'nomducatalogueacreer', 'create', 'disque:\répertoire'


Ajout d'une table à la liste des tables indexées pour le full-text
exec sp_fulltext_table 'table', 'create', 'nomducataloguefulltext', 'nomcléprimaire'


Ajout d'une colonne à la liste des colonnes indexées pour le full-text
exec sp_fulltext_column 'table', 'colonne', 'add', 0x040c


Indexation d'un catalogue
exec sp_fulltext_catalog 'nomducatalogue', 'start_full'

Vérifier qu'une colonne soit indexéeSELECT ColumnProperty ( ObjectId('titles'),'titles','IsFulltextIndexed' )

Liste des colonnes indexée d'une tablesp_help_fulltext_columns 'Article'Pour ajouter/supprimer une colonne à un index il faut d'abord desactiver celui-cisp_fulltext_table 'Articles', 'deactivate'puissp_fulltext_column 'Articles', 'type', 'drop'sp_fulltext_column 'Articles', 'title', 'add'et on reactivesp_fulltext_table 'Articles', 'activate'
IMPORTANT : Je conseille de mettre tous les calagos en mode tracking, cela signifie que le catalogue se reconstitue lorsque le serveur a 100ms de dispo.Rappel pour celaSp_fullText_Table 'Articles', 'Start_change_tracking'Sp_fullText_Table 'Articles', 'Start_background_updateindex'
______



AUTRE
Emplacement des fichiers

L'indexation full-text portant sur des tables comportant moins d'un million de lignes ne nécessite aucune optimisation ou presque. Si le nombre de lignes est supérieur au million, il est nécessaire configurer Sql Server afin de favoriser les performances entrée/sortie vers les fichiers utilisés. Comme souvent, il est préférable de mettre les fichiers sur un disque physique distinct de celui utilisé par Sql Server lui-même.

Sous Windows 2000, le pagefile.sys doit être 1,5 à 2 fois plus grand que la taille de la mémoire. De nouveau, ce fichier doit se trouver sur un autre disque que ceux utilisés par Sql Server, l'environnement hôte et le catalogue si cela est possible

Modifier

update des catalogue

Sp_fullText_Table 'ActuNews', 'Start_change_tracking'
Sp_fullText_Table 'ActuNews', 'Start_background_updateindex'

Permet d'avoir une maj dès que le serveur à 100ms de disponibilité.
Evite aussi d'inclure un timestamp dans la table

Modifier

SQL 2000 : Analyse et optimisation

utilitaire permettant l'analyse des base et service sous SQL 2000 pour l'optimisation des bases
http://www.microsoft.com/downloads/details.aspx?familyid=B352EB1F-D3CA-44EE-893E-9E07339C1F22&displaylang=en

Modifier

XML : Sauvegarde de données OPEN XML

Exemple simple

USE NorthWind

CREATE PROC GetRegions_XML
@empdata text
AS

DECLARE @hDoc int
DECLARE @tbl TABLE(state VARCHAR(20))

exec sp_xml_preparedocument @hDoc OUTPUT, @empdata

INSERT @tbl
SELECT StateName
FROM OPENXML(@hDoc, 'root/States')
WITH (StateName VARCHAR(20))

EXEC sp_xml_removedocument @hDoc

SELECT * FROM Suppliers
WHERE Region IN (SELECT * FROM @tbl)

/*
declare @s varchar(100)
set @s = '

'

exec GetRegions_XML @s
  • /

_______
Avec Curseur

 Transact SQL Cursor
CREATE PROC SQL_Cursor
AS
/*Local variables */
DECLARE @ContName VARCHAR(100),
@CompName VARCHAR(50)
/*create the cursor*/
DECLARE MyCursor CURSOR FOR
SELECT CompanyName, ContactName
FROM Suppliers
WHERE ContactName LIKE 'c%'
/*open the cursor*/
OPEN MyCursor
/*get row values*/
FETCH MyCursor INTO @CompName, @ContName
/*perform oterations with single row*/
PRINT 'T_SQL cursor row | ' + @ContName + ' | ' + @CompName
/*establish loop structure*/
WHILE 
FETCH_STATUS = 0{BR}
BEGIN{BR}
/*get row values*/{BR}
FETCH MyCursor INTO @CompName, @ContName{BR}
/*perform operations with single row*/{BR}
PRINT 'T_SQL cursor row | ' + @ContName + ' | ' + @CompName{BR}
END{BR}
/*close the cursor*/{BR}
CLOSE MyCursor{BR}
/*remove the cursor definition*/{BR}
DEALLOCATE MyCursor{BR}
{BR}
-- Cursor Simulator{BR}
CREATE PROC CursorSimulator{BR}
AS{BR}
/*Prepare TABLE variable to take resultset*/{BR}
DECLARE @tbl TABLE({BR}
RowID INT IDENTITY(1, 1),{BR}
CompanyName VARCHAR(100),{BR}
ContactName VARCHAR(50)){BR}
/*Local variables */{BR}
DECLARE @ContName VARCHAR(100),{BR}
@CompName VARCHAR(50),{BR}
@count int, /*create local
fetch_status*/
@iRow int /*row pointer (index)*/
/* create array simulator */
INSERT @tbl
SELECT CompanyName, ContactName
FROM Suppliers
WHERE ContactName LIKE 'c%'
/*get array Upper Bound (highest ID number)*/
SET @count = @@ROWCOUNT
/*initialize index counter*/
SET @iRow = 1
/*establish loop structure*/
WHILE @iRow <= @count
BEGIN
/*get row values*/
SELECT @ContName = CompanyName, @CompName = ContactName
FROM @tbl
WHERE RowID = @iRow
/*perform operations with single row*/
PRINT 'My cursor row | ' + @ContName + ' | ' + @CompName
/*go to next row*/
SET @iRow = @iRow + 1
END

Modifier

Changer le propriétaire d'un objet en base

Executer la commande suivante :

EXEC sp_changeobjectowner 'NOM_DE_LA_TABLE', 'OWNER'

Pour plus de renseignements:
http://doc.ddart.net/mssql/sql70/sp_ca-cz_17.htm


MAJ

Commande générique pour tous les objets!! Beaucoup plus praqtique :


SELECT 'EXEC(sp_changeobjectowner @objname = +
ltrim(u.name) + '.' + ltrim(s.name) + ' + ', @newowner = dbo'')'
FROM sysobjects s,sysusers u
WHERE s.uid = u.uid AND u.name <> 'dbo'
AND xtype in ('V', 'P', 'U','FN','IF','TF')
AND u.name not like 'INFORMATION%' order by s.name