Il est possible de déplacer une table (non système) ou un index d’un espace de stockage (groupe de fichier SQL Server) à l’autre en utilisant différentes techniques…
Notons que le déplacement du code tel que vues, procédures stockées, fonctions, triggers… n’est pas possible car ce sont en fait des données placées dans les tables systèmes, et que les tables systèmes sont les seules à ne pas pouvoir être déplacées. En effet les tables système sont stockées systématiquement dans le groupe de fichier PRIMARY, cela afin de pouvoir retrouver immédiatement tous les objets d’une base sans avoir à se poser la question de savoir ou les trouver…
Pour exécuter ces exemples nous avons besoin d’une base de données de tests. Assurez vous que le répertoire SQL existe bien en racine de C:
Si tel n’est pas le cas :
EXECUTE master..xp_cmdshell 'MKDIR "C:\SQL\"'
GO
CREATE DATABASE DB_TEST
ON
PRIMARY
(NAME = F_PRIM1,
FILENAME = 'C:\SQL\F_prim.mdf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 10 ),
FILEGROUP FG_DATA
(NAME = F_DATA,
FILENAME = 'C:\SQL\F_data.mdf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 10 )
LOG ON
(NAME = F_LOG,
FILENAME = 'C:\SQL\F_log.ldf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 10 );
GO
USE DB_TEST;
GO
Dans notre exemple nous allons supposer que nous voulons déplacer toute table ou index de PRIMARY à FG_DATA.
1 Рd̩placement de la table
1.1) la table a une primary key CLUSTERED
Il faut utiliser CREATE INDEX … WITH DROP EXISTING
CREATE TABLE dbo.T1
(COL INT NOT NULL PRIMARY KEY,
DATA VARCHAR(8))
GO
INSERT INTO dbo.T1 VALUES (11, 'toto')
INSERT INTO dbo.T1 VALUES (12, 'tata')
INSERT INTO dbo.T1 VALUES (13, 'titi')
GO
Quel est le nom de l’index cluster sous jacent à la primary key de la table ? (l’index de la PK porte le nom de contrainte de la PK)
SELECT CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'T1'
AND CONSTRAINT_TYPE = 'PRIMARY KEY';
Ou est situé cet index ? (dans quel groupe de fichier…)
SELECT i.name AS INDEX_NAME, groupname AS GROUP_NAME
from sysindexes i
INNER JOIN sysfilegroups fg
ON i.groupid = fg.groupid
WHERE i.name = (SELECT CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'T1'
AND CONSTRAINT_TYPE = 'PRIMARY KEY');
Comment le déplacer ? …
Si vous connaissez le nom de l’index :
CREATE UNIQUE CLUSTERED INDEX <nom_index> ON dbo.T1 (COL)
WITH ( DROP_EXISTING )
ON FG_DATA ;
Sinon en SQLdynamique :
DECLARE @SQL VARCHAR(8000);
SELECT @SQL = 'CREATE UNIQUE CLUSTERED INDEX '
+ CONSTRAINT_NAME
+ ' ON dbo.T1 (COL) ON FG_DATA WITH DROP_EXISTING'
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'T1'
AND CONSTRAINT_TYPE = 'PRIMARY KEY';
EXEC (@SQL);
1.2) la table a un index CLUSTERED sous jacent à une contrainte d’unicité
Il faut utiliser de même le CREATE INDEX … WITH DROP EXISTING
CREATE TABLE dbo.T2
(COL INT UNIQUE CLUSTERED,
DATA VARCHAR(8))
GO
INSERT INTO dbo.T2 VALUES (21, 'toto')
INSERT INTO dbo.T2 VALUES (22, 'tata')
INSERT INTO dbo.T2 VALUES (23, 'titi')
GO
Pour connaître le nom de l’index cluster dans ce cas, il faut utiliser la requête suivante :
SELECT name
FROM sysindexes
WHERE indid = 1
AND id = OBJECT_ID('dbo.T2');
Pour déplacer cet index il faudra utiliser la même commande SQL qu’en 1.
1.3) la table a un index CLUSTERED indépendant de toute contrainte
il faut utiliser de même le CREATE INDEX … WITH DROP EXISTING
en repêchant le nom de l’index cluster à l’aide de la requête précédent :
En revanche la commande de déplacement de l’index est la suivante :
CREATE CLUSTERED INDEX <nom_index> ON dbo.T1 (COL)
ON FG_DATA
WITH DROP_EXISTING;
Le mot clef UNIQUE n’a plus à figurer.
1.4) la table n’a aucun index
Le cas est plus simple qu’il n’y paraît. En fait il suffit de créer un index cluster sur le bon groupe de fichier, ce qui déplace la table. Puis le supprimer. La table est alors laissé dans ce groupe de fichier et les feuilles, comme la racine de l’index sont supprimées.
CREATE TABLE dbo.T3
(COL INT,
DATA VARCHAR(8));
GO
INSERT INTO dbo.T3 VALUES (31, 'toto')
INSERT INTO dbo.T3 VALUES (32, 'tata')
INSERT INTO dbo.T3 VALUES (33, 'titi')
GO
Déplacer la table :
CREATE CLUSTERED INDEX X ON dbo.T3 (COL) ON FG_DATA
GO
DROP INDEX dbo.T3.X
GO
— vérification :
SELECT OBJECT_NAME(i.id) AS TABLE_NAME, s.groupname AS FILE_GROUP
FROM sysfilegroups s
INNER JOIN sysindexes i
ON i.groupid = s.groupid
WHERE i.id = OBJECT_ID('dbo.T3');
Rien à voir avec l’horrible code que pisse l’IHM :
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_T3
(
COL int NULL,
DATA varchar(8) NULL
) ON [FG_DATA]
GO
IF EXISTS(SELECT * FROM dbo.T3)
EXEC('INSERT INTO dbo.Tmp_T3 (COL, DATA)
SELECT COL, DATA FROM dbo.T3 TABLOCKX')
GO
DROP TABLE dbo.T3
GO
EXECUTE sp_rename N'dbo.Tmp_T3', N'T3', 'OBJECT'
GO
COMMIT
2 – déplacement d’un index heap (hors CLUSTER)
Il s’agit tout simplement de recréer cet index à l’aide du CREATE INDEX … DROP EXISTING.
Vous savez maintenant comment faire…
Un seul exemple :
CREATE TABLE dbo.T4
(COL INT,
DATA VARCHAR(8));
GO
INSERT INTO dbo.T4 VALUES (31, 'toto')
INSERT INTO dbo.T4 VALUES (32, 'tata')
INSERT INTO dbo.T4 VALUES (33, 'titi')
GO
CREATE INDEX X ON dbo.T4 (DATA)
GO
— ou est situé cet index ?
SELECT OBJECT_NAME(i.id) AS TABLE_NAME, i.name as INDEX_NAME,
s.groupname AS FILE_GROUP
FROM sysfilegroups s
INNER JOIN sysindexes i
ON i.groupid = s.groupid
WHERE i.name = 'X';
Le déplacer :
CREATE INDEX X ON dbo.T4 (DATA) WITH DROP_EXISTING ON FG_DATA;
Ou cet index s’est-il déplacé ?
SELECT OBJECT_NAME(i.id) AS TABLE_NAME, i.name as INDEX_NAME,
s.groupname AS FILE_GROUP
FROM sysfilegroups s
INNER JOIN sysindexes i
ON i.groupid = s.groupid
WHERE i.name = 'X';
CQFD
--------
Frédéric Brouard, SQLpro - ARCHITECTE DE DONNÉES, http://sqlpro.developpez.com/
Expert bases de données relationnelles et langage SQL. MVP Microsoft SQL Server
www.sqlspot.com : modélisation, conseil, audit, optimisation, tuning, formation
* * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *
l’index cluster représente la table
si vous déplacer l’index cluster sur un deuxième Group de Fichier tout serai déplacer
Précision : j’ai une table avec une clé primaire et un index nonclustered (donc les données ne sont pas dans l’index)
Bonjour,
mais en fait vous ne déplacez que les index et pas les données de la table (lorsque je regarde les propriétés de ma table, j’ai toujours PRIMARY pour les données)
cordialement.