Nous pouvons utiliser SELECT…INTO dans SQL Server pour créer une nouvelle table à partir d’une source de table. SQL Server utilise les attributs des expressions de la liste SELECT pour définir la structure de la nouvelle table.

Avant SQL Server 2005, utilisation de SELECT…INTO dans le code de production était une «odeur de code» de performance car il acquérait des verrous de schéma sur les tables système de la base de données, ce qui faisait que SQL Server ne répondait pas pendant l’exécution de la requête. Cela est dû au fait qu’il s’agit d’une instruction DDL dans une transaction implicite, qui est inévitablement longue car les données sont insérées dans la même instruction SQL. Cependant, ce problème a été corrigé dans SQL Server 2005, lorsque le modèle de verrouillage a changé.

SELECT…INTO est devenu populaire car c’était un moyen plus rapide d’insérer des données que d’utiliser INSERT INTO…SELECT…. Cela était principalement dû au fait que l’opération SELECT…INTO était, dans la mesure du possible, consignée en masse. Bien que INSERT INTO puisse désormais être journalisé en bloc, vous pouvez toujours voir cet avantage de performances dans SQL Server 2012 et 2014, car SELECT…INTO peut être parallélisé sur ces versions, alors que la prise en charge de la parallélisation de INSERT INTO n’est apparue que dans SQL Server 2016. Cependant, avec SELECT…INTO, vous avez toujours la tâche de définir tous les index et contraintes requis, et ainsi de suite, sur la nouvelle table.

Une recommandation à éviter d’utiliser SELECT…INTO, pour le code de production, est inclus en tant que règle d’analyse de code dans l’invite SQL (PE003).

Création de tables à l’aide de l’instruction SELECT INTO

La fonctionnalité SELECT…INTO de SQL Server a été conçue pour stocker ou «conserver» une source de table dans le cadre d’un processus. Voici un exemple simple:

Cependant, une source de table peut être bien d’autres choses qu’une table conventionnelle, comme une fonction définie par l’utilisateur, un OpenQuery, une OpenDataSource, une clause OPENXML, une table dérivée, une table jointe, un tableau croisé dynamique, une source de données distante, une variable de table ou une fonction variable. C’est avec ces sources de table plus exotiques que la syntaxe SELECT…INTO devient plus utile.

Les gens utilisent souvent SELECT…INTO avec le malentendu qu’il s’agit d’un moyen rapide de copier des tables. Il est donc surprenant qu’aucun des index, contraintes, colonnes calculées ou déclencheurs définis dans la table source est transférée vers la nouvelle table. Ils ne peuvent pas non plus être spécifiés dans l’instruction SELECT…INTO. Il ne fait rien non plus sur la nullité ou la préservation des colonnes calculées. Toutes ces tâches doivent être effectuées rétrospectivement avec les données en place, ce qui prend inévitablement du temps.

Vous pouvez cependant utiliser la fonction IDENTITY (datatype, seed, incrément) pour configurer un champ d’identité, et il est possible, lorsque la source est une seule table, de transformer une colonne de la table de destination en une colonne d’identité. C’est ce fait qui amène probablement les développeurs à supposer qu’il transférera d’autres attributs de colonne.

De plus, il ne peut pas non plus créer de tables partitionnées, de colonnes éparses ou de tout autre attribut hérité d’une table source. Comment est-ce possible que les données proviennent d’une requête impliquant de nombreuses jointures ou d’une source de données externe exotique?

Depuis SQL 2012 SP1 CU10, SELECT…INTO peut être exécuté en parallèle, Cependant, depuis SQL Server 2016, l’insertion parallèle est autorisée sur l’instruction INSERT INTO…SELECT conventionnelle, avec certaines restrictions, donc tout avantage de performance lié à l’utilisation de SELECT… INTO est maintenant plutôt diminué. Le processus INSERT INTO peut également être accéléré s’il peut être journalisé en bloc, plutôt que complètement restauré, en définissant le modèle de récupération à simple ou en bloc, en l’insérant dans une table vide ou dans un tas, et en définissant l’astuce TABLOCK pour la table.

Ce qui suit résume certaines des restrictions et limitations lors de l’utilisation de SELECT…INTO.

• La propriété IDENTITY d’une colonne est transférée, mais pas si:
  • L’instruction SELECT contient des tables jointes (en utilisant soit JOIN soit UNION), une clause GROUP BY ou une fonction d’agrégation.Si vous devez éviter qu’une propriété IDENTITY ne soit reportée dans la nouvelle table, mais que vous avez besoin des valeurs de la colonne, il vaut la peine d’ajouter une JOIN à votre table-source sur une condition qui n’est jamais vraie, ou un UNION qui ne fournit aucune ligne.
  • Le IDENTITY est répertoriée plusieurs fois dans la liste SELECT
  • La colonne IDENTITY fait partie d’une expression
  • La colonne IDENTITY provient d’une source de données distante
• Vous ne pouvez pas SELECT…INTO soit un paramètre table, soit une variable de table comme destination, mais vous pouvez les sélectionner FROM.
• Même si votre source est une table partitionnée, la nouvelle table est créée dans le groupe de fichiers par défaut. Cependant, dans SQL Server 2017, il est possible de spécifier le groupe de fichiers dans lequel la table de destination est créée, via la clause ON.
• Vous pouvez spécifier un ORDER BY, mais elle est généralement ignorée. Pour cette raison, l’ordre de IDENTITY_INSERT n’est pas garanti.
• Lorsqu’une colonne calculée est incluse dans SELECT list, la colonne correspondante dans la nouvelle table n’est pas une colonne calculée. Les valeurs de la nouvelle colonne sont les valeurs qui ont été calculées au moment où SELECT…INTO a été exécuté.
• Comme pour un CREATE TABLE instruction, si une instruction SELECT…INTO est contenue dans une transaction explicite, la ou les lignes sous-jacentes des tables système concernées sont exclusivement verrouillé jusqu’à ce que la transaction soit explicitement validée. En attendant, cela entraînera des blocages sur d’autres processus qui utilisent ces tables système.

Il y a une certaine confusion sur les problèmes qui peuvent survenir avec l’utilisation de SELECT…INTO en utilisant des tables temporaires. SELECT…INTO a acquis une réputation quelque peu injuste pour cela, mais cela faisait partie d’un problème plus général impliquant un conflit de verrouillage dans tempdb sous une lourde charge de la création et de la suppression de petites tables temporaires. L’adoption de SELECT…INTO avec enthousiasme pourrait considérablement accroître ce type d’activité. Le problème pourrait être facilement résolu dans SQL Server 2000 avec l’introduction de l’utilisation de l’indicateur de trace TF1118, qui n’est plus nécessaire à partir de SQL Server 2016. Pour une explication complète, voir Idées fausses autour de TF 1118.

Résumé

En résumé, SELECT…INTO est un bon moyen de créer un tableau- source temporairement persistante dans le cadre d’un processus, si vous ne vous souciez pas des contraintes, des index ou des colonnes spéciales. Ce n’est pas un bon moyen de copier une table car seuls les éléments essentiels du schéma de table peuvent être copiés. Au fil des ans, certains facteurs ont augmenté ou diminué l’attraction de SELECT…INTO, mais dans l’ensemble, il est judicieux d’éviter de l’utiliser autant que possible. Au lieu de cela, créez une table explicitement, avec la gamme complète des fonctionnalités que la table possède et qui sont conçues pour garantir la cohérence des données.