using System;
using System.Collections.Generic;
using System.Data.SqlTypes;
using System.IO;
using Microsoft.SqlServer.Server;
 
[SqlUserDefinedAggregate
    (
    Format.UserDefined,
    IsInvariantToDuplicates = true,
    IsInvariantToNulls = true,
    IsInvariantToOrder = true,
    IsNullIfEmpty = false,
    MaxByteSize = -1
    )
]
public struct CountDistinctInt : IBinarySerialize
{
    // The hash table
    private Dictionary<int, object> dict;
 
    // Recreate the hash table for each new group
    public void Init()
    {
        dict = new Dictionary<int, object>();
    }
 
    // Ignore NULLs, store key values in the hash table
    public void Accumulate(SqlInt32 Data)
    {
        if (!Data.IsNull)
        {
            dict[Data.Value] = null;
        }
    }
 
    // Merge partial aggregates
    public void Merge(CountDistinctInt Group)
    {
        foreach (var item in Group.dict.Keys)
        {
            dict[item] = null;
        }
    }
 
    // Return the DISTINCT COUNT result
    public int Terminate()
    {
        return dict.Count;
    }
 
    // Required by SQL Server to serialize this object
    void IBinarySerialize.Write(BinaryWriter w)
    {
        w.Write(dict.Count);
 
        foreach (var item in dict.Keys)
        {
            w.Write(item);
        }
    }
 
    // Required by SQL Server to deserialize this object
    void IBinarySerialize.Read(BinaryReader r)
    {
        var recordCount = r.ReadInt32();
        dict = new Dictionary<int, object>(recordCount);
 
        for (var i = 0; i < recordCount; i++)
        {
            dict[r.ReadInt32()] = null;
        }
    }
}

cd C:\Windows\Microsoft.NET\Framework64\v3.5
csc.exe /t:library "c:\clr\distinct_count.cs"
copy distinct_count.dll c:\clr\

drop aggregate distinct_count
drop assembly distinct_count
create assembly distinct_count
from 'c:\clr\distinct_count.dll'
go

create aggregate distinct_count (@input int) returns int
external name distinct_count.CountDistinctInt

Imports System
Imports System.Data
Imports Microsoft.SqlServer.Server
Imports System.Data.SqlTypes
Imports System.IO
Imports System.Text
Imports System.Collections

<Serializable(), SqlUserDefinedAggregate(Format.UserDefined, IsInvariantToNulls:=True, IsInvariantToDuplicates:=False, IsInvariantToOrder:=False, MaxByteSize:=-1)> _
Public Class CountDistinct
    Implements IBinarySerialize

    ''' <summary>
    ''' The variable that holds the intermediate result of the concatenation
    ''' </summary>
    Private intermediateResult As StringBuilder

    ''' <summary>
    ''' Initialize the internal data structures
    ''' </summary>
    Public Sub Init()
        Me.intermediateResult = New StringBuilder()
    End Sub

    ''' <summary>
    ''' Accumulate the next value, not if the value is null
    ''' </summary>
    ''' <param name="value"></param>
    Public Sub Accumulate(ByVal value As SqlString)
        If value.IsNull Then
            '''Return
	   value = "3$^!~||-09abr$$@45!"
        End If

        Me.intermediateResult.Append(value.Value).Append(","c)
    End Sub
    ''' <summary>
    ''' Merge the partially computed aggregate with this aggregate.
    ''' </summary>
    ''' <param name="other"></param>
    Public Sub Merge(ByVal other As CountDistinct)
        Me.intermediateResult.Append(other.intermediateResult)
    End Sub

    ''' <summary>
    ''' Called at the end of aggregation, to return the results of the aggregation.
    ''' </summary>
    ''' <returns></returns>
    Public Function Terminate() As SqlInt64
        Dim output As String = String.Empty

        'delete the trailing comma, if any
        If Not (Me.intermediateResult Is Nothing) AndAlso Me.intermediateResult.Length > 0 Then
            output = Me.intermediateResult.ToString(0, Me.intermediateResult.Length - 1)
        End If

        Dim words As String() = output.Split(New Char() {","c})
	Dim word As String
        Dim i As Integer
        Dim dict As New Hashtable()
        i = 0

	For Each word In words
            If (dict.ContainsKey(word) = 0 and word <> "3$^!~||-09abr$$@45!")
                dict.Add(word, i)
                i = i + 1
            End If
	Next


        Return dict.Count
    End Function

    Public Sub Read(ByVal r As BinaryReader) Implements IBinarySerialize.Read
        intermediateResult = New StringBuilder(r.ReadString())
    End Sub

    Public Sub Write(ByVal w As BinaryWriter) Implements IBinarySerialize.Write
        w.Write(Me.intermediateResult.ToString())
    End Sub
End Class

cd C:\Windows\Microsoft.NET\Framework64\v3.5

vbc.exe /t:library "c:\clr\distinct_count.vb"