Discussion :

[misscricri]

Bonjour

J'ai rempli une table avec un champ de type varchar comme suit :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
 
 
'1, 2, 3, 4, 5, 6'

J'aimerais savoir comment récupérer chacune de ces valeurs dans un select !

Merci

[Invité]

Tout d'abord, c'est le signe d'une très mauvaise modélisation et c'est à proscrire.
Ta question prélude des problèmes que cela engendre... Et ça n'ira pas en s'améliorant.

Pour le kick, je t'offre une solution mais si tu peux revoir ton modèle fais-le tout de suite !

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
-- parse d'une chaine de caractères avec séparateur
WITH 
	TEST as ( select '1, 2, 3, 4, 5, 6' as texte ) ,
	CTE AS ( SELECT CAST('<c>' + REPLACE(texte, ', ','</c><c>') + '</c>' AS XML) AS texte_xml FROM TEST )
SELECT T.C.value('(./text())[1]','INT') AS C
FROM CTE
CROSS APPLY texte_xml.nodes('//c') AS T(C)

[elsuket]

Tout d'abord, c'est le signe d'une très mauvaise modélisation et c'est à proscrire.
Ta question prélude des problèmes que cela engendre... Et ça n'ira pas en s'améliorant.

ça c'est clair, rien à ajouter.

J'ai donné une solution performante ici en ce qui concerne le dépouillement.

Avec la solution proposée par 7gyY9w1ZY6ySRgPeaefZ, ça fonctionne pas mal lorsque la chaîne n'est pas trop longue.
Quand elle le devient, la conversion en XML coûte cher.

Avec la fonction que je donne, il est impossible à SQL Server de faire un estimation correcte de cardinalité, puisque la fonction retourne une variable de type table, et que SQL Server n'estime pas les cardinalités sur une telle table, sauf si vous y collez une clé primaire (bonjour les problèmes de contention dans TempDB ... )
En fait, il considère qu'il y a toujours une seule ligne dans une telle table, sauf si on utilise l'indicateur de requête OPTION (RECOMPILE), mais cela coûte cher en CPU, et l'amélioration n'est pas forcément au rendez-vous.

Quelqu'un a-t-il testé le stockage de telles valeurs dans une table "utilitaire" :

- à l'aide d'un jeton, ladite table étant découpée en de nombreuses partitions sur ABS(BINARY_CHECKSUM(<jeton>)) % nbPartitions,
- <jeton> étant de type uniqueidentifier ?

@++

[zinzineti]

Je me permets de rebondir sur ce post qui met le doigt sur le problème de non conformité des SGBDs relationnel à la norme SQL.

Rappelons que c'est depuis 1999 (13 ans déjà !) que la norme SQL:1999 a introduit les types orientés objets : Tableau (ARRAY), Ligne (ROW), Héritage de table, ...et depuis MS SQL SERVER n'a pas encore implémenté nativement le type ARRAY ! si c'était fait, misscricri ne serait pas confronté à ce problème. misscricri aurait écrit quelque chose du genre :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
CREATE TABLE myTable (col1 CHAR(1) ARRAY[6])
 
INSERT INTO myTable (col1) VALUES (1,2,3,4,5,6)
 
SELECT col1 [1], col1[2], col1[3],col1[4],col1[5],col1[6]
FROM myTable

et on ne lui demanderait pas de revoir son model de données !
Non seulement MS n'a pas implémenté le type ARRAY, mais n'a pas non plus implémenté la fonction SPLIT (qui permet de découper une chaîne de caractères en fonction d'un séparateur) !
On me dira que ce n'est le travail d'un SGDBR de spliter des chaines de caractères ! ou que SQL est ENSEMBLISTE !
Ensembliste oui mais aussi procédural. Rappelons aussi que la norme SQL:1999 a introduit du procédural dans la norme SQL. Donc SQL est non seulement ENSEMBLISTE mais aussi PROCEDURAL !
A mon avis le minimum qu'on devrait voir implémenter dans tout SGBD digne de ce nom c'est d'abord le minimum que prévoit la norme SQL (qui évolue bien sûr). Une fois ce minimum de fonctionnalités ISO implémentées, chaque éditeur est libre d'ajouter les ingrédients qu'il veut à sa sauce, histoire de se démarquer de la concurrence. Et ceci dans l'intérêt des entreprises et des organisations.

A+

[elsuket]

Post intéressant, néanmoins je trouve que le type ARRAY va à l'encontre de la première forme normale, puisque c'est un type qui permet de stocker plusieurs valeurs scalaires.
Bref, je trouve que c'est anti-relationnel, et en ce sens que de tels types s'approchent du XML ...

D'autre part, le plus grand intérêt des bases de données étant la puissance de l'indexation, ces types ne sont pas implémentés par SQL Server (et probablement d'autres) parce qu'il n'est pas simple d'indexer de telles colonnes, et encore moins de les vectoriser.
On le voit d'ailleurs avec l'indexation XML : c'est lourd et peu performant ... mais peut-être que d'autre éditeurs (j'entends de SGBDR) ont fait mieux; cela dit, je demande à voir

Donc SQL est non seulement ENSEMBLISTE mais aussi PROCEDURAL !

Certes, mais aussi déclaratif; à ce titre l'écrivain de la requête ou d'une suite de requête ne doit jamais se soucier de la façon dont sont stockées les valeurs ... or avec un "type" comme ARRAY, ce n'est pas tout à fait le cas.

A mon avis le minimum qu'on devrait voir implémenter dans tout SGBD

Vous avez oublié le R ... intentionnellement ?

@++

[mikedavem]

J'ai donné une solution performante ici en ce qui concerne le dépouillement.

Yep ta fonction est plus performante que XML dans ce cas mais parce que nous parlons de nombre ici. La force du XML dans ce genre de problème est lorsque nous sommes confronté à mélange de types à valeurs aléatoires non connues.

Rappelons que c'est depuis 1999 (13 ans déjà !) que la norme SQL:1999 a introduit les types orientés objets : Tableau (ARRAY), Ligne (ROW), Héritage de table, ...et depuis MS SQL SERVER n'a pas encore implémenté nativement le type ARRAY ! si c'était fait, misscricri ne serait pas confronté à ce problème. misscricri aurait écrit quelque chose du genre :

Ce n'est pas tout à fait juste. Le type ARRAY est apparue (malheureusement) avec la norme SQL:2003 avec les types COLLECTIONS. Il est clair que ce type viole la forme normale 1. Il n'y a malheureusement aucune solution simple de pouvoir afficher ou transmettre un ensemble de données avec ce type. Je suis de l'avis de Elsuket.

Maintenant la question du modèle incorrect c'est à voir ... Peut-être qu'à la base la colonne créée par misscricri n'est simplement qu'une colonne d'information au niveau sémantique qui n'est ou n'était pas forcément concernée à chaque fois par ce type d'opération. Dans ce cas le modèle n'est pas mauvais. Cependant si toutes les valeurs de colonnes sont de ce type (suite de nombre séparée par une virgule) et sont assujetties par un découpage de chaîne alors oui effectivement le modèle est à revoir.

++

[elsuket]

Yep ta fonction est plus performante que XML dans ce cas mais parce que nous parlons de nombre ici. La force du XML dans ce genre de problème est lorsque nous sommes confronté à mélange de types à valeurs aléatoires non connues.

D'accord, mais encore une fois ne pas avoir des valeurs de même type de données passe au-delà du relationnel.
Quand on dépouille un document XML sous SQL Server, c'est dans le but de transformer ce qui est un échange de données en une suite d’occurrences d'entités relationnelles.
Utiliser XML dans un autre but, c'est un peu comme dire qu'on enrichit de l'uranium sans avoir jamais pensé en faire une bombe atomique

Maintenant la question du modèle incorrect c'est à voir ... Peut-être qu'à la base la colonne créée par misscricri n'est simplement qu'une colonne d'information au niveau sémantique qui n'est ou n'était pas forcément concernée à chaque fois par ce type d'opération. Dans ce cas le modèle n'est pas mauvais.

Pour moi il est juste juste limite

Cependant si toutes les valeurs de colonnes sont de ce type (suite de nombre séparée par une virgule) et sont assujetties par un découpage de chaîne alors oui effectivement le modèle est à revoir.

Entièrement d'accord. Malheureusement j'ai vu cela chez tous mes employeurs ...

Espérons que misscricri n'est pas la victime d'une mauvaise modélisation

@++

[zinzineti]

Ce n'est pas tout à fait juste. Le type ARRAY est apparue (malheureusement) avec la norme SQL:2003 avec les types COLLECTIONS.

Le type ARRAY existe depuis SQL:1999
J'ai sous les yeux le document ISO/EEC 9075-3:1999 qui mentionne bien le type ARRAY. Si je me trompe merci de me le signaler.

[zinzineti]

Bonjour Nicolas,
je trouve intéressant tes arguments, je suis globalement d'accord. Néanmoins je vais essayer de présenter des contre-arguments histoire de nourrir la discussion

néanmoins je trouve que le type ARRAY va à l'encontre de la première forme normale, puisque c'est un type qui permet de stocker plusieurs valeurs scalaires.

Je suis d'accord.

Bref, je trouve que c'est anti-relationnel, et en ce sens que de tels types s'approchent du XML ...

Si le type ARRAY est proche du type XML alors le type XML viole aussi la première forme normale. Et intégrer le type XML dans un SGBDR c'est une façon de s’asseoir sur le model relationnel conçu par le Professeur Edgar Frank Codd (paix à son âme). Je ne mets pas en cause les avantages, l'intérêt du XML dans un SGBDR, loin de là mais c'est la politique de deux poids deux mesures qui me gène. De plus la tendance des SGBDs actuellement est de s'orienter vers le model relationnel-objet pour plus de flexibilité. Parce qu'il des situations où dans un SGBDR on peut être amené à privilégier la flexibilité par rapport à l'intégrité des données.

D'autre part, le plus grand intérêt des bases de données étant la puissance de l'indexation, ces types ne sont pas implémentés par SQL Server (et probablement d'autres) parce qu'il n'est pas simple d'indexer de telles colonnes, et encore moins de les vectoriser.
On le voit d'ailleurs avec l'indexation XML : c'est lourd et peu performant ... mais peut-être que d'autre éditeurs (j'entends de SGBDR) ont fait mieux; cela dit, je demande à voir

Je suis d'accord mais au moins le choix est possible avec XML. Ce qui n'est pas le cas pour le type ARRAY. Ce qui me gène c'est le "2 poids 2 mesures"

Certes, mais aussi déclaratif; à ce titre l'écrivain de la requête ou d'une suite de requête ne doit jamais se soucier de la façon dont sont stockées les valeurs ... or avec un "type" comme ARRAY, ce n'est pas tout à fait le cas.

Oui et qu'en est-il du type XML dans ce cas ?

[Waldar]

Il y a eu une conversation similaire ces deux dernières semaines sur les forums Oracle, je vous invite à la lire vous serez surpris de certaines réponses :
http://www.developpez.net/forums/d11...standard-sql3/

[mikedavem]

Comme je disais précédemment tout dépend de la sémantique du modèle. On ne peut pas dire comme cela que le modèle ne respecte pas la 1NF.

De la même manière dire que le XML est anti relationnel, tout dépend encore de la façon dont il est utilisé dans le modèle ... après on peut bien débattre sur le sujet ...

++