Récupérer valeurs d'une table

Version imprimable

07/03/2012, 19h33
misscricri

Récupérer valeurs d'une table
Bonjour

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

1 2 3 '1, 2, 3, 4, 5, 6'
J'aimerais savoir comment récupérer chacune de ces valeurs dans un select !

Merci

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:

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)

08/03/2012, 01h14
elsuket

Citation:

Envoyé par 7gyY9w1ZY6ySRgPeaefZ

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 ?

@++ ;)
09/03/2012, 11h04
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:

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+
09/03/2012, 18h04
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 :)

Citation:

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.

Citation:

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

Vous avez oublié le R ... intentionnellement ? :)

@++ ;)
10/03/2012, 19h57
mikedavem

Citation:

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.

Citation:

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.

++
11/03/2012, 04h23
elsuket

Citation:

Envoyé par Mikedavem

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 :)

Citation:

Envoyé par Mikedavem

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 :)

Citation:

Envoyé par Mikedavem

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 ;)

@++ ;)
12/03/2012, 10h33
zinzineti

Citation:

Envoyé par mikedavem

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. ;)
12/03/2012, 11h20
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 ;)

Citation:

Envoyé par elsuket

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.

Citation:

Envoyé par elsuket

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.

Citation:

Envoyé par elsuket

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"

Citation:

Envoyé par elsuket

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 ?
12/03/2012, 14h11
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/
12/03/2012, 16h33
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 ...

++
12/03/2012, 17h31
elsuket

Salut Étienne,

Citation:

Envoyé par Zinzinetti

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.

Le XML me convient s'il est vu comme une valeur atomique, au même titre qu'une valeur chaîne de caractères LOB qui est indexée en plain texte.
l'indexation XML se fait, au moins sous SQL Server, de façon relativement lente, même si elle procure de bonnes performances en lecture. Mais comme d'habitude, avec les gros volumes de données, ça passe moins bien.
La force du XML ici c'est que l'on peut le typer à l'aide d'un schéma.
Dans ce cas là, pour moi, on reste dans le cadre du modèle relationnel, même si ça en touche un peu les bords :)
Néanmoins je suis d'accord sur le problème du 2 poids, 2 mesures.

En revanche il est clair que le modèle relationnel objet est un échec; ce n'est pas le modèle lui-même qui l'est, mais la possibilité d'indexation; en effet il n'existe pas à l'heure actuelle de méthode aussi performante qu'un index relationnel pour indexer de façon générique n'importe quel objet. Même les index spatiaux, bien que performants, ne le sont pas autant qu'un index relationnel; or dans une valeur spatiale, on peut stocker des lignes, des polygones, des morceaux de planisphère, ou même la Terre entière ... ce que l'on a gagné dans la simplicité de la modélisation, on l'a un peu perdu dans la puissance de l'indexation.

Citation:

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

On peut (et on devrait !) le typer par un schéma :)
Donc c'est assez parallèle au type ARRAY, qui limite par sa définition le nombre de valeurs (et leur type) que l'on peut y stocker.

Que ce soit XML ou ARRAY, y'a quand même un truc qui me gêne toujours un peu :)

Citation:

Envoyé par 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

Merci, c'est une bonne discussion aussi ;)

Je conclurai en citant Cinéphil, citant Date :

Citation:

The real point I’m getting at here is that the notion of atomicity has no absolute meaning; it just depends on what we want to do with the data.

Ce qui rejoint la dernière réponse de Mikedavem.

Donc comme d'habitude, il y a ce qu'il est possible de faire avec un outil, et dans cet ensemble là, il y a les bonnes façons de faire.
Celles-ci me semblent difficile à définir dès lors que les valeurs à stocker sont à la limite de l'atomicité, même si on peut tout à fait respecter le principe de cette dernière.
Sinon en capillotractant, on peut considérer qu'une chaîne de caractères est un tableau de caractères (un ARRAY ? :sm:)

@++ ;)
13/03/2012, 11h33
zinzineti

Dans ta conclusion, tu as cité l'excellent pédagogue et compagnon du professeur E.F. Codd, je veux nommer C.J. Date.
Comme par hazard, je suis en train de lire actuellement son livre intitulé SQL and Relational Therory (How to Write Accurate SQL Code) éditer chez O'REILLY en cette année 2012. Je vais essayer de mettre en correspondance les arguments de C.J. Date et ton commentaire. l'idée c'est de faire en sorte que notre discussion soit fructueuse... je l'espère ;)

1. Atomicité et 1NF

Citation:

Envoyé par elsuket

Donc comme d'habitude, il y a ce qu'il est possible de faire avec un outil, et dans cet ensemble là, il y a les bonnes façons de faire.
Celles-ci me semblent difficile à définir dès lors que les valeurs à stocker sont à la limite de l'atomicité, même si on peut tout à fait respecter le principe de cette dernière.

C.J. Date (Page 31 chapitre 2)

Citation:

I said that 1NF meant that every tuple in every relation contains just a single value (of the appropriate type) in every attribute position─and it’s usual to add that those “single values” are supposed to be atomic. But this latter requirement raises the obvious question: What does it mean for data to be atomic?
... E.F.Codd defines atomic data as data that “cannot be decomposed into smaller pieces by the DBMS (excluding certain special functions).” Even if we ignore that parenthetical exclusion, however, this definition is a trifle puzzling; at best, it’s certainly not very precise. For example, what about character strings? Are character strings atomic? Well, every database product I know provides a variety of operators─LIKE, SUBSTR (substring), “||” (concatenate), and so on─that rely by definition on the fact that character strings in general can be “decomposed into smaller pieces by the DBMS.” So are such strings atomic? What do you think?

Et C.J.Date donne par la suite dans le livre quelques exemples montrant l'atomicité et la non atomicité. Et il dit :

C.J. Date (Page 33 chapitre 2)

Citation:

But I could have used any number of different examples to make my point; I could have shown attributes (and therefore domains) that contained arrays; or bags (multisets); or lists; or photographs; or audio or video recordings; or X rays; or fingerprints; or XML documents; or any other kind of value, “atomic” or “nonatomic,” you might care to think of.

Bref une colonne de type ARRAY ou XML ou ... peut être atomique ou non atomique selon les cas. Et c'est après cette démonstration qu'il conclut en disant :

Citation:

The real point I’m getting at here is that the notion of atomicity has no absolute meaning; it just depends on what we want to do with the data. Sometimes we want to deal with an entire set of part numbers as a single thing; sometimes we want to deal with individual part numbers within that set─but then we’re descending to a lower level of detail, or lower level of abstraction. The following analogy might help. In physics (which after all is where the terminology of atomicity comes from) the situation is exactly parallel: Sometimes we want to think about individual atoms as indivisible things, sometimes we want to think about the subatomic particles (i.e., the protons, neutrons, and electrons) that make up those atoms. What’s more, protons and neutrons, at least, aren’t really indivisible, either─they contain a variety of “subsubatomic” particles called quarks. And so on, possibly (?).

ce qui rejoint ta conclusion.

2. Model objet/relationel

Citation:

Envoyé par elsuket

En revanche il est clair que le modèle relationnel objet est un échec; ce n'est pas le modèle lui-même qui l'est, mais la possibilité d'indexation; en effet il n'existe pas à l'heure actuelle de méthode aussi performante qu'un index relationnel pour indexer de façon générique n'importe quel objet. ...

C.J. Date (Page 34 chapitre 2)

Citation:

A proper object/relational system is just a relational system with proper type support (including proper user defined type support in particular)─which just means it’s a proper relational system, no more and no less. And what some are pleased to call “the object/relational model” is, likewise, just the relational model, no more and no less.

Je pense que ton commentaire et celui de C.J.Date sont complémentaire. ;)

Citation:

Envoyé par elsuket

Donc c'est assez parallèle au type ARRAY, qui limite par sa définition le nombre de valeurs (et leur type) que l'on peut y stocker

Je me demande si on fait de l'objet sans le savoir...
Rigoureusement parlant, ARRAY n'est pas un type ! c'est un générateur de type. par contre ARRAY[6] par exemple est un type. De même VARCHAR n'est pas un type c'est un générateur de type mais VARCHAR(6) est un type. Question : Qu'est qu'un type SQL au sens strict ? INTEGER est un type ou un générateur de type ? mais là c'est un autre débat ...;)

Aller un dernier pour la route ...

C.J. Date (Page 39 chapitre 2)

Citation:

Strictly speaking, CHAR (for example) isn’t really a type as such─rather, it’s a type generator. By contrast, CHAR(25), for example, is a type as such, and it’s obtained by invoking that type generator with the value 25 as sole argument to that invocation. What’s more, analogous remarks apply to everything in the foregoing list except for type BOOLEAN and the various integer types (SMALLINT, INTEGER, BIGINT). SQL also supports a ROW type generator, as we know. In fact, it also supports ARRAY, MULTISET, and REF (but, oddly enough, not TABLE) as type generators

A+
13/03/2012, 12h44
elsuket

Merci pour ces extraits ;)

J'adore celui-ci :

Citation:

but, oddly enough, not TABLE

:mrgreen: