#眉標=SQL Server 2008、.NET CLR、SQLCLR
#副標=實用SQL Server 2008（6）
#大標=SQL Server 2008 CLR程式寫作(下)
#作者=文／圖 胡百敬



===<反灰>=============
SqlUserDefinedAggregate(Format.UserDefined, MaxByteSize=-1,IsNullIfEmpty=true)
==<end>==============





==<box>===========
程式1

'MaxByteSize:=-1代表Max
<Serializable()> _
<Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.UserDefined, MaxByteSize:=-1, IsNullIfEmpty:=True)> _
Public Structure LargeAgg
 Implements IBinarySerialize
 Dim strAnswer As String
 Public Sub Init()
  strAnswer = ""
 End Sub

 Public Sub Accumulate(ByVal value As SqlString)
  If value.IsNull Then
   Return
  End If
   strAnswer += value.Value
 End Sub

 Public Sub Merge(ByVal value As LargeAgg)
   strAnswer += value.strAnswer
 End Sub

 Public Function Terminate() As SqlString
  Return strAnswer
 End Function

'實作二進位序列化，僅是將字串資料以二進位格式讀出/寫入
 Public Sub Read(ByVal r As System.IO.BinaryReader) Implements IBinarySerialize.Read
  strAnswer = r.ReadString()
 End Sub

 Public Sub Write(ByVal w As System.IO.BinaryWriter) Implements IBinarySerialize.Write
  w.Write(strAnswer)
 End Sub
End Structure
==<end>==============






==<box>===========
程式2

use tempdb
go
--將組件註冊到SQL Server內
create assembly SqlCLR from 'C:\SqlServer2008CLRNew\bin\SqlServer2008CLRNew.dll'
go
--設定彙總函數回傳的資料結構可以達到2G位元組
create aggregate concatenate(@input1 nvarchar(max))
returns nvarchar(max)
external name SqlCLR.[SqlServer2008CLRNew.LargeAgg]
go
create table tb (
 id int primary key identity,
 col1 varchar(max)
)
go
 
declare @a nvarchar(max) = replicate(convert(nvarchar(max), 'a'),10000)
insert tb values(@a),(@a),(@a)
go
--簡單地型成 30000 位元組資料彙總，證明可以大於 8K 位元組
select dbo.concatenate(col1), LEN(dbo.concatenate(col1)) from tb
==<end>==============






==<box>===========
程式3

'簡單地彙總，將兩欄相乘後累加，再求平均
<Serializable()> _
<Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.UserDefined, MaxByteSize:=1000, IsNullIfEmpty:=True)> _
Public Structure AggTwo
 Implements IBinarySerialize
  '需要用 Decimal 資料類型來對應 SQL Server 內的 Decimal 類型
  Dim mdecSum As Decimal
  Dim mintCount As Integer
 Public Sub Init()
   mdecSum = 0
   mintCount = 0
 End Sub

 '透過SqlFacet Attribute來設定 SQL Server Decimal 類型的總位數和小數位數
 Public Sub Accumulate(<SqlFacet(Precision:=20, Scale:=10)> ByVal Value1 As SqlDecimal, _
  <SqlFacet(Precision:=20, Scale:=10)> ByVal Value2 As SqlDecimal)
   If (Value1.IsNull Or Value2.IsNull) Then Return
    mdecSum += Value1.Value * Value2.Value
    mintCount += 1
 End Sub

 Public Sub Merge(ByVal value As AggTwo)
    mdecSum += value.mdecSum
    mintCount += value.mintCount
 End Sub

 Public Function Terminate() As <SqlFacet(Precision:=20, Scale:=10)> SqlDecimal
   If mintCount = 0 Then Return 0
    Return mdecSum / mintCount
 End Function

 Public Sub Write(ByVal w As System.IO.BinaryWriter) Implements IBinarySerialize.Write
   w.Write(mdecSum)
   w.Write(mintCount)
 End Sub

 Public Sub Read(ByVal r As System.IO.BinaryReader) Implements IBinarySerialize.Read
   mdecSum = r.ReadDecimal
   mintCount = r.ReadInt32()
 End Sub
End Structure
==<end>==============





==<box>===========
程式4

create assembly SqlCLR from 'D:\SqlServer2008CLRNew\bin\SqlServer2008CLRNew.dll'
go

create aggregate AggTwo(@input1 decimal(20,10), @input2 decimal(20,10))
returns decimal(20,10)
external name SqlCLR.[SqlServer2008CLRNew.AggTwo];
go

create table tb (
  c1 decimal(20,10),
  c2 decimal(20,10)
)
go

insert tb values(1,2),(3,4),(5,6);
go

select dbo.AggTwo(c1, c2) from tb
--簡單驗證一下自訂彙總函數計算的結果是否正確
select AVG(c1*c2) from tb

drop aggregate AggTwo;
drop assembly SqlCLR;
==<end>==============






==<box>===========
程式5

Imports System.Data.SqlTypes
Public Class FData
 Implements IEnumerable, IEnumerator
  Dim iPrev As Integer '序列中的前一個數字
  Dim iCur As Integer '序列中當下的數字
  Dim iCount As Integer
  Dim iMax As Integer '序列要取幾個數字
  Dim iPreStart As Integer 
  Dim iStart As Integer

 Private Sub New(ByVal preStart As Integer, ByVal Start As Integer, ByVal Count As Integer)
  iPreStart = preStart
  iStart = Start
　　iPrev = preStart
　　iCur = Start
　　iMax = Count
　　iCount = 0
 End Sub

 Public Function GetEnumerator() As System.Collections.IEnumerator Implements System.Collections.IEnumerable.GetEnumerator
　　Return Me
 End Function

 Public ReadOnly Property Current() As Object Implements System.Collections.IEnumerator.Current
  Get
   Return Me
  End Get
 End Property

 Public Function MoveNext() As Boolean Implements System.Collections.IEnumerator.MoveNext
'序列中當下的數字等於前兩個數字的相加
'若序列產生的數字量已經超過使用者輸入的最大值，就傳回 False，表示沒有下一筆了
'否則傳回 True
  If iCount <> 0 Then
    Dim p As Integer = iCur
    iCur += iPrev
    iPrev = p
  End If
    iCount += 1
    If iCount > iMax Then
      Return False
    Else
      Return True
    End If
 End Function

 Public Sub Reset() Implements System.Collections.IEnumerator.Reset
    iCount = 0
    iPrev = 0
    iCur = iStart
 End Sub

 <Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName:="FillRow", TableDefinition:="NextOut INT, PrevOut INT")> _
 Public Shared Function Fibonacci(ByVal iPrevStart As Integer, ByVal iStart As Integer, ByVal iCount As Integer) As IEnumerable
    Return New FData(iPrevStart, iStart, iCount)
 End Function

 Public Shared Sub FillRow(ByVal obj As Object, _
  <System.Runtime.InteropServices.Out()> ByRef NextOut As SqlInt32, _
  <System.Runtime.InteropServices.Out()> ByRef PrevOut As SqlInt32)
    Dim f As FData = CType(obj, FData)
      NextOut = f.iCur
      PrevOut = f.iPrev
 End Sub
End Class
==<end>==============






==<box>===========
程式6

--以Order關鍵字告知回傳的記錄將依照Next欄位排序
create function Fibonacci (@prevstart int, @start int, @rows int)
returns table (Next int, Prev int)
order (Next asc)
as external name SqlCLR.[SqlServer2008CLRNew.FData].Fibonacci
go
--未指定排序
create function FibonacciUnOrdered (@prevstart int, @start int, @rows int)
returns table (next int, prev int)
as external name SqlCLR.[SqlServer2008CLRNew.FData].Fibonacci
go
--排序運算子指定錯誤，資料記錄值是以由小到大排序傳回
--但在Order選項設定時，告知是由大到小(desc)
create function FibonacciWrong (@prevstart int, @start int, @rows int)
returns table (next int, prev int)
order (prev desc)
as external name SqlCLR.[SqlServer2008CLRNew.FData].Fibonacci
go

SET STATISTICS TIME ON

--沒有排序提示，一定要使用Order 運算子
-- TVF -> Sort -> Select
select * from dbo.FibonacciUnordered(1,2,25)
order by next

-- TVF -> Segment -> Sequence Project -> Assert -> Select
select * from dbo.Fibonacci(1,2,25)
order by next
==<end>==============






==<box>===========
程式7

--指定排序的欄位沒有用到建立函數時，透過Order關鍵字定義的欄位
--所以不影響執行計畫的建立
select * from dbo.Fibonacci(1,2,25)
order by Prev

-- 以 prev 欄位排序，而非 next 欄位
--未符合ORDER hint，沒用上所以沒有錯誤
select * from dbo.FibonacciWrong(3,4,5)
order by prev 

--符合ORDER hint，使用時才發現有以下的錯誤
--訊息5332，層級16，狀態1，行4
--資料流中的資料順序不符合為CLR TVF 'FibonacciWrong' 所指定的ORDER 提示。
--資料的順序必須符合CLR TVF 的ORDER 提示中所指定的順序。請更新ORDER 提示，以反映輸入資料的排列順序，
--或更新CLR TVF，以符合ORDER 提示所指定的順序。
select * from dbo.FibonacciWrong(3,4,5)
order by prev desc

--若起始數值是負數，就符合由大到小排序的設定
--其語法可以正確執行
select * from dbo.FibonacciWrong(-3,-4,5)
order by prev desc
==<end>==============






==<box>===========
程式8

create proc spSimple @strIn nvarchar(100)=N'.NET CLR'
as
external name SqlCLR.[SqlServer2008CLRNew.StoredProcedures].spSimple
==<end>==============






==<box>===========
程式9

/*** Object:  StoredProcedure [dbo].[spSimple]    Script Date: 08/01/2009 21:40:50 ***/
CREATE PROCEDURE [dbo].[spSimple]
	@strIn [nvarchar](100) = .NET CLR  --這裡會發生錯誤
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SqlCLR].[SqlServer2008CLRNew.StoredProcedures].[spSimple]
==<end>==============