forked from vincent/notebook
79 lines
2.8 KiB
Plaintext
79 lines
2.8 KiB
Plaintext
|
USE DB_GRAND_HOTEL;
|
|||
|
|
GO
|
|||
|
|
|
|||
|
|
|
|||
|
|
|
|||
|
|
-- cr<63>ation des espaces de stockage
|
|||
|
|
ALTER DATABASE DB_GRAND_HOTEL ADD FILEGROUP FG_PART_OLD;
|
|||
|
|
ALTER DATABASE DB_GRAND_HOTEL
|
|||
|
|
ADD FILE (NAME = 'F_PART_OLD',
|
|||
|
|
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019FBIN2\MSSQL\DATA\HOTEL_PART_OLD.ndf',
|
|||
|
|
SIZE = 25 MB,
|
|||
|
|
FILEGROWTH = 10 MB)
|
|||
|
|
TO FILEGROUP FG_PART_OLD;
|
|||
|
|
GO
|
|||
|
|
ALTER DATABASE DB_GRAND_HOTEL ADD FILEGROUP FG_PART_2006;
|
|||
|
|
ALTER DATABASE DB_GRAND_HOTEL
|
|||
|
|
ADD FILE (NAME = 'F_PART_2006',
|
|||
|
|
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019FBIN2\MSSQL\DATA\HOTEL_PART_2006.ndf',
|
|||
|
|
SIZE = 25 MB,
|
|||
|
|
FILEGROWTH = 10 MB)
|
|||
|
|
TO FILEGROUP FG_PART_2006;
|
|||
|
|
GO
|
|||
|
|
ALTER DATABASE DB_GRAND_HOTEL ADD FILEGROUP FG_PART_2007;
|
|||
|
|
ALTER DATABASE DB_GRAND_HOTEL
|
|||
|
|
ADD FILE (NAME = 'F_PART_2007',
|
|||
|
|
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019FBIN2\MSSQL\DATA\HOTEL_PART_2007.ndf',
|
|||
|
|
SIZE = 25 MB,
|
|||
|
|
FILEGROWTH = 10 MB)
|
|||
|
|
TO FILEGROUP FG_PART_2007;
|
|||
|
|
GO
|
|||
|
|
ALTER DATABASE DB_GRAND_HOTEL ADD FILEGROUP FG_PART_2008;
|
|||
|
|
ALTER DATABASE DB_GRAND_HOTEL
|
|||
|
|
ADD FILE (NAME = 'F_PART_2008',
|
|||
|
|
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019FBIN2\MSSQL\DATA\HOTEL_PART_2008.ndf',
|
|||
|
|
SIZE = 25 MB,
|
|||
|
|
FILEGROWTH = 10 MB)
|
|||
|
|
TO FILEGROUP FG_PART_2008;
|
|||
|
|
GO
|
|||
|
|
|
|||
|
|
|
|||
|
|
|
|||
|
|
-- 1) cr<63>ation de la fonction de partitionnement
|
|||
|
|
CREATE PARTITION FUNCTION PF_DATE_FACTURE (DATETIME)
|
|||
|
|
AS RANGE RIGHT
|
|||
|
|
FOR VALUES ('2006-01-01', '2007-01-01', '2008-01-01');
|
|||
|
|
|
|||
|
|
-- 2) cr<63>ation du sch<63>ma de r<>partition
|
|||
|
|
CREATE PARTITION SCHEME PS_DATE_FACTURE
|
|||
|
|
AS PARTITION PF_DATE_FACTURE
|
|||
|
|
TO (FG_PART_OLD, FG_PART_2006, FG_PART_2007, FG_PART_2008);
|
|||
|
|
-- 3) cr<63>ation de l'objet sur la partition
|
|||
|
|
|
|||
|
|
BEGIN TRANSACTION;
|
|||
|
|
BEGIN TRY
|
|||
|
|
--> il faut commencer par retirer la contrainte FK de la table T_FACTURE_ITEM_ITM
|
|||
|
|
ALTER TABLE [S_CHB].[T_FACTURE_ITEM_ITM] DROP CONSTRAINT [FK_T_FACTUR_CONTIENT_T_FACTUR];
|
|||
|
|
--> il faut commencer par retirer la PK !!!
|
|||
|
|
ALTER TABLE [S_CHB].[T_FACTURE_FAC] DROP CONSTRAINT [PK_T_FACTURE_FAC];
|
|||
|
|
--> pas possible !
|
|||
|
|
CREATE UNIQUE CLUSTERED INDEX X ON [S_CHB].[T_FACTURE_FAC] ([FAC_DATE], [FAC_ID])
|
|||
|
|
ON PS_DATE_FACTURE(FAC_DATE);
|
|||
|
|
--> remettre la PK (ATTENTION : par d<>faut la PK est cr<63><72>e sous forme d'index clustered)
|
|||
|
|
ALTER TABLE [S_CHB].[T_FACTURE_FAC]
|
|||
|
|
ADD CONSTRAINT [PK_T_FACTURE_FAC] PRIMARY KEY NONCLUSTERED ([FAC_ID])
|
|||
|
|
ON FG_DATA;
|
|||
|
|
--> remettre la FK
|
|||
|
|
ALTER TABLE [S_CHB].[T_FACTURE_ITEM_ITM]
|
|||
|
|
ADD CONSTRAINT [FK_T_FACTUR_CONTIENT_T_FACTUR]
|
|||
|
|
FOREIGN KEY ([FAC_ID])
|
|||
|
|
REFERENCES [S_CHB].[T_FACTURE_FAC] (FAC_ID);
|
|||
|
|
COMMIT;
|
|||
|
|
-- tout se passe bien => COMMIT
|
|||
|
|
END TRY
|
|||
|
|
BEGIN CATCH
|
|||
|
|
-- quelque chose se passe mal => ROLLBACK
|
|||
|
|
IF XACT_STATE() <> 0
|
|||
|
|
ROLLBACK;
|
|||
|
|
THROW;
|
|||
|
|
END CATCH
|