T-SQL is default language supported by SQL server. It is right tool to interact with the relational data. It has well-defined syntax for database features such as queries, triggers, stored procedures and functions.
Even though it is a powerful language, its nature is quite different from modern day programming language such as C# and Vb.Net.

In some situations you may find that T-SQL is finding difficult to do tasks such as Mathematical calculations, recursive functions and so on. Common approach in this situation is writing the logic using high level programming language (such as C# or Vb.Net) in business layer or data access layer. But it pays the penalty of network traffic.
Instead of that, if you have a chance to run your high level programming language code inside SQL server that will reduce the network traffic.

In SQL Server 2005, you have the ability to use the rich features of languages such as C# or Vb.Net for specific tasks that are better suited to them. Usually you would be forced to write this code inside the business layer or data access layer, instead now you can write the code in a .Net language and run it directly inside the SQL Server database. The code will execute by using CLR in SQL Server or SQLCLR.

SQLCLR or CLR in SQL Server creates the runtime environment inside the SQL Server and it allows executing the .Net code with in SQL Server.

Now you are ready to create the sample example that will execute inside the SQL Server.

In order to run any .Net code inside the SQL Server, first you have to compile it as an assembly and register that assembly inside the SQL Server by using CREATE ASSEMBLY statement. Then you have to create SQL UDF (SQL User Defined Function), which will call the code inside the assembly.

Here I am explaining how to get factorial of a number in SQL Server.

Create Class Library project in Vb.Net and name it as FactorialProgram.
Rename the class to Factorial.vb from Class1.vb.

The code to create the factorial number is given below.

    Imports System
    Imports System.Data.SqlTypes
    Imports Microsoft.SqlServer.Server
    Public Class Factorial
        <Microsoft.SqlServer.Server.SqlFunction()> _
        Public Shared Function GetFactNumber(ByVal num As Integer) As SqlInt32
            Dim i, fact As Integer
            i = 1
            fact = 1
            For i = 1 To num
                fact = fact * i
            Next
            Return fact
        End Function
    End Class

Now build the project. So you created the assembly successfully. Then you have to register this assembly in SQL Server.
Before registering the assembly, you have to enable CLR in SQL Server.

To do that, execute following script in SQL Server.

    --Enabling the CLR in Sql server 2000

    EXEC sp_configure 'show advanced options' , '1';
    go
    reconfigure;
    go
    EXEC sp_configure 'clr enabled' , '1'
    go
    reconfigure;

Next, you have to register assembly in the SQL Server by executing following script.

    Create Assembly FactorialAssembly
    from
    'path of the Assembly'
    GO

    Eg:

    Create Assembly FactorialAssembly
    from
    'D\FactorialProgram\bin\Debug\FactorialProgram.dll'
    GO

Here I have my assembly in D drive. It may differ for you, mention correct path for the assembly.

You successfully registered the assembly in SQL Server. Now you have to create SQL UDF (SQL User defined function), that will call the code inside the Assembly.
You can create SQL UDF by using following script.

    Create Function GetFactNumber (@num Int)
    Returns Int
    As
    External Name
    FactorialAssembly.[FactorialProgram.Factorial].GetFactNumber 
    Go

After creating the SQL UDF, test your code by calling this SQL UDF.

    select dbo.GetFactNumber(5)

If you executes the above query in SQL Server, you can find factorial of 5 i.e., 120.

In this way you can execute any .Net language code with in SQL Server by using SQLCLR or CLR in SQL Server.

If you have any queries or doubts post at Forum.