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'
ModifierSQL 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
ModifierSQL 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