Discussion :

[StringBuilder]

Bonjour,

Qui n'a jamais envi� la fonction d'aggr�gation "groupconcat" de MySQL ?
Qui ne s'est jamais cass� la t�te avec des CTE, fonctions r�cursives et autres tables temporaires pour obtenir une fonction split performante ?

H� bien vous ne r�vez pas, voici les deux sous forme de bundle.

Elles utilisent la capacit� de SQL Server, depuis la version 2005 R2 d'utiliser des librairies externes programm�es en CLR (n'importe quel langage .NET managed) directement depuis des requ�tes SQL.

Si l'appel au CLR est assez lourd, SQL Server tire cependant partie d'un code compil�, qui monte tr�s bien en charge, tr�s utile pour des calculs intensifs, ou pour des traitements sur des cha�nes de caract�re.

Je ne dirais pas que ces deux fonctions ci-dessous n'ont pas d'�quivalent aussi rapide, si ce n'est plus, en T-SQL (quoique ?), mais elles ont cependant plusieurs avantages :
- leur mont�e en charge devrait �tre lin�aire, l� o� du code T-SQL peur s'av�rer catastrophique
- leur impl�mentation support le parall�lisme d'ex�cution (par exemple traiter plusieurs blocs de donn�es en // au sein de la m�me requ�te)
- ils utilisent ce qui se fait de "mieux" pour aborder ces probl�mes (une VRAIE fonction d'agr�gation, un VRAI split)
- leur code est tellement enfantin que m�me mon chien saurait les faire �voluer (bon, j'ai pas de chien, mais j'ai un chat, �a compte ?)

J'ai flagu� ce topic "SQL Server 2014" car j'ai fait le d�veloppement et test sur cette version (avec Visual Studio 2013).
Cependant, rien ne devrait emp�cher le code ci-dessous de compiler avec Visual Studio 2005 et SQL Server 2005R2.

Le support des fonctions CLR est possible avec SQL Server Express, et est compilable avec Visual Studio Express.

Pour commencer, il vous faut cr�er un projet de type "classe" dans Visual Studio.
Choisissez comme version cible ".NET 2.0" (pas de version plus r�cente, car m�me sous 2014, �a demande � bidouiller, et vu qu'on n'en a pas besoin ici...)

Puis cr�er deux classes :

Aggregates.cs (GroupConcat)

Code c# :

S�lectionner tout - Visualiser dans une fen�tre � part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
 
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.IO;
using Microsoft.SqlServer.Server;
using Microsoft.SqlServer.Types;
using System.Text;
 
namespace DeveloppezDotNet
{
    [Serializable]
    [SqlUserDefinedAggregate(
        Format.UserDefined,
        IsInvariantToNulls = true,
        IsInvariantToDuplicates = false,
        IsInvariantToOrder = false,
        MaxByteSize = 8000,
        Name = "GroupConcat")
    ]
    public class GroupConcat : IBinarySerialize
    {
        private StringBuilder groupconcat;
 
        public void Init()
        {
            groupconcat = new StringBuilder();
        }
 
        public void Accumulate(SqlString value)
        {
            if (!value.IsNull && value.Value.Length > 0)
            {
                this.groupconcat.Append(value.Value).Append(',');
            }
        }
 
        public void Merge(GroupConcat other)
        {
            this.groupconcat.Append(other.groupconcat);
        }
 
        public SqlString Terminate()
        {
            string output = string.Empty;
            if (this.groupconcat != null && this.groupconcat.Length > 0)
            {
                output = this.groupconcat.ToString(0, this.groupconcat.Length - 1);
            }
 
            return new SqlString(output);
        }
 
        public void Read(BinaryReader r)
        {
            groupconcat = new StringBuilder(r.ReadString());
        }
 
        public void Write(BinaryWriter w)
        {
            w.Write(this.groupconcat.ToString());
        }
    }
}

TableFunctions.cs (Split)

Code c# :

S�lectionner tout - Visualiser dans une fen�tre � part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
 
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections;
 
namespace DeveloppezDotNet
{
    public class TableFunctions
    {
        [SqlFunction(FillRowMethodName = "SplitFillRow")]
        public static IEnumerable SplitInit(SqlString list, SqlString separator)
        {
            return list.Value.Split(separator.Value.ToCharArray());
        }
 
        public static void SplitFillRow(object obj, out SqlString line)
        {
            line = new SqlString((string)obj);
        }
    }
}

Vous allez ensuite ajouter deux fichiers SQL � votre solution :

Deploy.sql (corrigez le script pour refl�ter votre base de donn�es et les chemins d'acc�s)

Code sql :

S�lectionner tout - Visualiser dans une fen�tre � part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
 
ALTER DATABASE SandBox SET TRUSTWORTHY ON
go
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO
 
PRINT N'Deploying assemply to database'
CREATE ASSEMBLY DeveloppezDotNet 
FROM N'C:\in\DeveloppezDotNet.dll'
WITH PERMISSION_SET = UNSAFE
PRINT N'Assembly DeveloppezDotNet.dll created'
GO
 
CREATE AGGREGATE GroupConcat (@input nvarchar(200)) RETURNS nvarchar(max)
EXTERNAL NAME DeveloppezDotNet.[DeveloppezDotNet.GroupConcat];
go
 
CREATE FUNCTION Split(@liste nvarchar(max), @separateurs nvarchar(10))
RETURNS TABLE 
(
	ligne nvarchar(50)
)
AS 
EXTERNAL NAME DeveloppezDotNet.[DeveloppezDotNet.TableFunctions].SplitInit;
GO

Remove.sql

Code sql :

S�lectionner tout - Visualiser dans une fen�tre � part

1
2
3
4
5
6
7
8
9
10
11
 
PRINT N'Removing assemply from database'
 
IF EXISTS (SELECT * FROM sys.assemblies WHERE name = 'DeveloppezDotNet') 
BEGIN
	DROP AGGREGATE GroupConcat
	DROP FUNCTION Split
	DROP ASSEMBLY DeveloppezDotNet
	PRINT N'Assembly DeveloppezDotNet.dll dropped'
END
GO

Dans les options du projet, allez dans "Build Events" et dans la section "Post-build event command line" copiez ce script :
(A nouveau, adaptez les chemins d'acc�s et base de donn�es)

Code bat :

S�lectionner tout - Visualiser dans une fen�tre � part

1
2
3
4
5
 
sqlcmd -S " localhost" -d "SandBox" -i "$(TargetDir)\Remove.sql"
del "c:\in\DeveloppezDotNet.dll" /q
copy "$(TargetDir)\DeveloppezDotNet.dll" "C:\in\DeveloppezDotNet.dll" /y
sqlcmd -S " localhost" -d "SandBox" -i "$(TargetDir)\Deploy.sql"

Et enfin, vous pouvez compiler !

Ensuite, dans SQL Server, vous disposez des deux nouvelles fonctions.

Voici un script d'exemple d'utilisation :

Code sql :

S�lectionner tout - Visualiser dans une fen�tre � part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
 
-- Je vous décourage très fortement à utiliser ce genre de structure, qui ne respecte pas la première forme normale !
create table livre
(
   id int not null primary key identity,
   auteur nvarchar(50),
   titre nvarchar(50),
   motcles nvarchar(max)
);
go
 
-- Faites pas attention aux valeurs, c'est un exemple de test à 22h15...
insert into livre (auteur, titre, motcles) values ('Jack London', 'Croc Blanc', 'loup,chien,nature,nord,roman');
insert into livre (auteur, titre, motcles) values ('Jack London', 'L''Appel de la Forêt', 'loup,chien,nature,nord,roman');
insert into livre (auteur, titre, motcles) values ('Stephen King', 'Ça', 'clown,horreur');
insert into livre (auteur, titre, motcles) values ('Stephen King', 'Rage', 'école,meurtre,drame');
 
-- Bon, alors, la liste des livres publiés par chaque auteur
select auteur, dbo.groupconcat(titre) from livre group by auteur;
 
-- Et l'explosion des mots clés sous forme de ligne
select distinct livre.auteur, motcle.ligne
from livre
cross apply split(livre.motcles, ',') motcle;
 
-- Et la magie opère lorsqu'on regroupe les deux fonctions en un seul appel :
select livre.auteur, dbo.groupconcat(distinct motcle.ligne)
from livre
cross apply split(livre.motcles, ',') motcle
group by livre.auteur

J'esp�re que vous trouverez autant d'utilit� � ma contribution que moi de plaisir � l'�crire !

[Tidus159]

Bonjour,

ton post/article est tr�s int�ressant mais je me pose la question de la maintenance (renommage, �volutions, etc.) de ce genre d'utilisation dans un cadre professionnel.
Qu'en est-il aussi d'un potentiel changement de SGBD (oracle permet-il de faire pareil ?) ?
Il serait donc int�ressant de savoir les cas d'utilisation ad�quats...
A+

[StringBuilder]

Oracle permet de faire l'�quivalent, mais en Java.

Sinon, pour l'�volution du code, il s'agit au final que d'une DLL "assembly" qu'on peut recompiler � la demande pour la faire �voluer.

Les noms des fonctions "SQL Server" sont d�finies au moment o� on les d�clare dans SQL Server.

Ainsi, la fonction "split" peut parfaitement s'appeler "eclate" sur une autre base, sans pour autant changer quoi que ce soit au niveau de la DLL :

On peut m�me faire cohabiter les deux alias :

Code sql :

S�lectionner tout - Visualiser dans une fen�tre � part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
 
-- Split
CREATE FUNCTION Split(@liste nvarchar(max), @separateurs nvarchar(10))
RETURNS TABLE 
(
	ligne nvarchar(50)
)
AS 
EXTERNAL NAME DeveloppezDotNet.[DeveloppezDotNet.TableFunctions].SplitInit;
GO
 
-- Eclate
CREATE FUNCTION Eclate(@groupe nvarchar(max), @delimiteurs nvarchar(10))
RETURNS TABLE 
(
	morceau nvarchar(50)
)
AS 
EXTERNAL NAME DeveloppezDotNet.[DeveloppezDotNet.TableFunctions].SplitInit;
GO

[StringBuilder]

Sinon, un exemple concret de cas d'utilisation (qui m'a fait d�couvrir cette fonctionnalit�), c'est lors de traitements plus complexes qu'on ne peut absolument pas traiter en T-SQL, mais qu'on souhaite cependant traiter dans la transaction.

J'ai un client qui nous a demand�, lors de la cr�ation d'une fiche client :
- de v�rifier l'existence g�ographique de l'adresse
- de corriger la syntaxe de l'adresse afin de r�pondre aux normes postales
- de g�olocaliser l'adresse
Si l'adresse n'est pas reconnu, ne pas enregistrer la fiche client et retourner une erreur.

Google Maps peut facilement �tre appel� par WebServices, et permet d'effectuer ces trois op�rations.

J'ai donc �crit un trigger en CLR qui effectue un appel au WebService de GoogleMaps.
Ainsi, j'ai pu g�rer directement, au niveau de ma transaction unitaire la g�olocalisation et la validit� des adresses saisies, garantissant un fichier client d�nu� de toute adresse erron�e.

[StringBuilder]

D�terrage, d�sol�.

Pour information, la solution CLR a �t� benchmark�e ici, et cette solution s'av�re, de loin ou presque, la plus rapide.

http://sqlperformance.com/2014/08/t-...-concatenation

La seule solution non CLR qui s'approche un peu (� 20% pr�s quand m�me) c'est la syntaxe "for xml path", qui, personnellement, me rebute (car totalement d�tourn�e, donc illisible)