Comment faire pour Partitionner une Table par Mois (“les Deux” ANNÉE & MOIS) et de créer mensuel des partitions automatiquement?

SSIS est un ETL (extraction, transformation, chargement). Ce n'est pas ce que vous voulez faire.
Vous avez juste besoin de créer des instructions DDL dynamiquement .

Je travaille trimestre, ci-dessous, mais elle fonctionne aussi bien avec 1, 2 ou X mois si vous le souhaitez.

Si vous voulez la partition de la table, vous devez d'abord créer le fichier, fichiers et groupes de partionned table et définir le partitionnement manuellement

La création de N+1 partitions pour 2015 T1 (avant, T1 et après T2) sur une table avec un int identité PK et une datetime2 partitionné de la colonne.
Mise à jour pour ajouter des mois, de faire tous les mois ou tout ce que vous avez besoin...

• D'abord créer des N groupes de fichiers:

  Alter Database [Test] Add Filegroup [Part_Before2015]
  Go
  Alter Database Test Add Filegroup [Part_201501]
  Go
  Alter Database Test Add Filegroup [Part_201504]
  Go

• Ajouter un fichier pour chaque groupe de fichiers:

  Alter Database [Test] Add FILE ( NAME = N'Part_Before2015', FILENAME = N'...\Part_Before2015.ndf' , SIZE = 5120KB , FILEGROWTH = 1024KB ) TO Filegroup [Part_Before2015]
  Alter Database [Test] Add FILE ( NAME = N'Part_201501', FILENAME = N'...\Part_201501.ndf' , SIZE = 5120KB , FILEGROWTH = 1024KB ) TO Filegroup [Part_201501]
  Alter Database [Test] Add FILE ( NAME = N'Part_201504', FILENAME = N'...\Part_201504.ndf' , SIZE = 5120KB , FILEGROWTH = 1024KB ) TO Filegroup [Part_201504]

• Créer une fonction de partition sur un datetime2 type (ou la date ou même datetime):

  Create Partition Function RangePartFunction (datetime2)
  as Range Right For Values ('20150101', '20150401') 

• Créer un schéma de partition à l'aide de la fonction de partition sur chaque groupe de fichiers (N+1):

  Create Partition Scheme RangePartScheme as Partition RangePartFunction
  To ([Part_Before2015], [Part_201501], [Part_201504])

• Créer la table partitionnée sur son schéma de partition:

  Create TABLE [PartitionTable] (id int identity(0, 1) not null, date datetime2 not null, text char(8000))
  On RangePartScheme (date) ;

• Ajouter un index Cluster sur la partitionné colonne et le schéma de partition:

  Create Clustered Index IDX_Part On dbo.PartitionTable(date) 
      On RangePartScheme (date);

• Ajouter une clé primaire de la colonne id:

  Alter Table dbo.PartitionTable Add COntraint PK_Part Primary Key Nonclustered(id, date);

Construire la requête utilisée pour ajouter des groupes de fichiers après la limite droite et de diviser la dernière partition

• Révision schéma de partition de l'extension et de la fonction de partition split
• Examen DMV utilisé

• Revoir tout cela et comment l'utiliser pour créer des dynamiques SQL

  Declare @currentDate datetime2
  Declare @endDate datetime2 = '20160701' -- new end date
  Declare @dateAdd int = 3 -- Add 3 month = 1 Quarter
  -- Get Current boundaries 
  Select @currentDate = DATEADD(MONTH, @dateAdd,Cast(MAX(value) as datetime2)) From sys.partition_range_values as r
  Inner Join sys.partition_functions as f on r.function_id = f.function_id
  Where f.name = 'RangePartFunction'
  -- Get all quarters between max and end date
  ; with d(id, date, name) as (
  Select 0, @currentDate, Convert(char(6), @currentDate, 112)
  Union All
  Select id+1, DATEADD(MONTH, @dateAdd, date), Convert(char(6), DATEADD(MONTH, @dateAdd, date), 112)
  From d Where d.date <= @endDate
  )
  Select * From (
  Select id = id*10, query = 'If Not Exists(Select 1 From sys.filegroups Where name = ''Part_'+name+''')
  Begin 
  Print ''Create Filegroup [Part_'+name+']''
  Alter Database [Test] Add Filegroup [Part_'+name+']
  End
  GO'
  From d
  Union All
  Select id*10+1, 'If Not Exists(Select 1 From sys.sysfiles Where name = ''Part_'+name+''')
  Begin 
  Print ''Create File [Part_'+name+'.ndf]''
  Alter Database [Test] Add FILE ( NAME = N''Part_'+name+''', FILENAME = N''C:\DB\MSSQL11.MSSQLSERVER\MSSQL\DATA\Part_'+name+'.ndf'' , SIZE = 5120KB , FILEGROWTH = 1024KB ) TO Filegroup [Part_'+name+']
  End
  GO'
  From d
  Union All
  Select id*10+2, 'Print ''Add Range [Part_'+name+']''
  Alter Partition Scheme RangePartScheme Next Used [Part_'+name+']
  Go'
  From d
  Union All
  Select id*10+3, 'Print ''Split Function ['+Convert(char(8), date, 112)+']''
  Alter Partition Function RangePartFunction() Split Range ('''+Convert(char(8), date, 112)+''');
  Go'
  From d
  ) as q order by id

le résultat de cette requête est une liste de requêtes SQL qui doivent être exécutées dans l'ordre.

Exécuter le SQL dynamique

• Il peut être exécuté manuellement (copiez et passé dans SSMS)
• Il peut être exécuté dans une boucle while ou avec un curseur qui sera exécuté à chaque ligne de la sortie de table un par un (utiliser sp_executesql)

Automatisation

• Créer un Travail SQL Server qui excute des requêtes SQL: exécution de la requête utilisée pour créer le SQL dynamique, enregistrer sa sortie dans une variable de table, puis d'exécuter chaque instruction avec une boucle/curseur

Si vous voulez l'exécuter mensuel et assurez-vous que les 12 prochains mois sont toujours créés, utilisez ce Set @endDate = DATEADD(MONTH, 12, getdate())

Enfin

• Il sera de sortie 4*N de lignes pour le N trimestres manquants entre la dernière limite de la fonction et @date de fin:

  • Créer Un Groupe De Fichiers
  • De créer le Fichier dans le groupe de fichiers
  • À élargir la portée du Schéma de partition
  • Diviser la gamme de la fonction de partition

• Vous pouvez l'exécuter ligne par ligne avec un curseur ou une boucle while ou vous pouvez simplement copier et le coller dans le SMS.

• Il peut être automatisée avec un travail aussi bien ie. @endDate = DATEADD(MONTH, 3, getdate() permettra de créer les 3 prochains mois
• Modification @dateAdd 1 si vous voulez mensuel partitions
• Ajouter vous possédez des colonnes ou des contrôles

Lien

Créer de l'emploi = https://www.mssqltips.com/sqlservertip/3052/simple-way-to-create-a-sql-server-job-using-tsql/

sp_executesql = https://technet.microsoft.com/en-us/library/ms188001%28v=sql.110%29.aspx

Boucle While = https://dba.stackexchange.com/questions/57933/can-exec-work-with-while-loop-of-cursor

OriginalL'auteur Julien Vavasseur