Comment master..spt_values ​​peut-il diviser efficacement les valeurs séparées par des virgules dans SQL Server ?

Diviser les colonnes avec master..spt_values

Contexte : tables de recherche et projections

Master..spt_values ​​est une table système dans Microsoft SQL Server qui contient diverses tables de recherche et de projection utilisées par les procédures stockées du système. Il permet une manipulation efficace des données en fournissant des séquences prédéfinies de nombres, de masques de bits et d'autres valeurs couramment requises.

Les tables de recherche permettent d'étendre les valeurs numériques à leurs représentations sous forme de chaîne correspondantes, par exemple en convertissant les types de verrous de nombres en noms. . D'un autre côté, les tables de projection offrent un moyen de parcourir une plage de valeurs, ce qui est utile pour diviser des colonnes en plusieurs lignes.

Type = "P": Table de projection

Tapez "P" dans master..spt_values ​​désigne un tableau de projection. Il contient une séquence de nombres consécutifs de 0 à 2047. Ce tableau permet la projection efficace de un ensemble de valeurs via diverses opérations, telles que le comptage ou l'agrégation.

Avantages de l'utilisation du type "P" pour le fractionnement des colonnes

Le avantage de l'utilisation de master..spt_values ​​(Type " P") pour diviser une colonne CSV réside dans ses performances et son efficacité. En exploitant la séquence de nombres pré-calculée, SQL Server peut éviter de créer et d'insérer dans une table temporaire, ce qui serait nécessaire si la séquence était générée dynamiquement. Cela peut améliorer considérablement la vitesse d'exécution des requêtes qui effectuent des opérations de fractionnement de colonnes.

Exemple de code

Le code en question montre comment diviser une colonne CSV en plusieurs lignes à l'aide de master..spt_values :

SELECT
    T.col1, RIGHT(LEFT(T.col4,Number-1),
    CHARINDEX(',',REVERSE(LEFT(','+T.col4,Number-1))))
FROM
    master..spt_values,
    table1 T
WHERE
    Type = 'P' AND Number BETWEEN 1 AND LEN(T.col4)+1 AND
    (SUBSTRING(T.col4,Number,1) = ','
    -- OR SUBSTRING(T.col4,Number,1)  = '') --this does not work correctly anyway

Ce code exploite la table de type "P" dans master..spt_values ​​pour créer une séquence de nombres qui sоответствует chaque caractère de la colonne CSV (col4). Il utilise ensuite ces nombres pour diviser la colonne en lignes en fonction du délimiteur virgule.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!