Please read my first post to understand the goals and security implications of the samples I am presenting. 

I will assume that you are in the sysadmin role on your SQL2005 server, you are using a program that allows you to execute TSQL commands (like Management Studio), and that you are running it on the same machine as your F# installation.  Change any paths to be appropriate to your machine.

First of all, you need to turn on CLR support in SQL2005:

sp_configure 'clr enabled', 1
go
reconfigure
go

Next, you need to create a database and allow the execution of "unsafe" assemblies in it.  There is a more sophisticated (and complicated) method involving signing the unsafe assemblies with a key and then granting the appropriate permissions to that key, but for testing purposes I will go the simple route:

create database fsharp
go
alter database fsharp set trustworthy on
go
use fsharp
go

Now you need to load the two utility libraries used by most F# programs into the database:

create assembly fslib from 'c:\fsharp\fsharp-1.1.11.7\bin\fslib.dll' with permission_set = unsafe
go
create assembly mllib from 'c:\fsharp\fsharp-1.1.11.7\bin\mllib.dll' with permission_set = unsafe
go

Now you are ready to write your first F# stored procedure.  Create a file called sqlclr.fs (the name is important) with following code:

open System
open System.Data
open System.Data.Sql
open System.Data.SqlTypes
open Microsoft.SqlServer.Server

[<SqlProcedure>]
let printToday() =
   SqlContext.Pipe.Send(DateTime.Now.ToString())

Compile this into a DLL.  To load and test out the procedure, issue the following commands:

create assembly sqlclr from 'c:\fsharp\sqlclr\sqlclr.dll' with permission_set = unsafe
go
create procedure PrintToday external name sqlclr.sqlclr.PrintToday
go
exec PrintToday
go

I won't go into all of the details of SQL CLR integration at this point, I want to point out some important facts.  CLR stored procedures have to be static functions; this works well with normal F# functions since they are defined this way.  By default, they get created in a class with the same name as the source file;  the name of that class must be used as the second part of the external name of the stored procedure.

This has been a lot of setup work for very little immediate reward, but it lays the foundation for what I'll present in future posts.  Next, I will demonstrate less trivial (and more useful) user-defined functions in F#.

Generating a sequence of integers as a table is a useful utility to have.  Historically, this was done either by having a temporary table or falling back to a TSQL while loop.  However, the UDT feature in SQL2000 and the CTE (common table expression) feature in SQL2005 make it easy to write a function that does this without explicitly looping or materializing the sequence.

Here is the code:

select sum(1.0 / (1.0 + num)) from Ints(1000000)

It comes back with the answer (14.39272671788580) in under two seconds on AMD 4200 X2.  It automatically parallelized the query since two cores are available.

I am going to use this function in further notes on using SQL queries for unorthodox purposes.