1. Client send a request to update record
2. SQL Server receives the request
3. SQL server checks if  the records exist in the data page cache
4. If not SQL server load the pages from hard disk to page cache in memory
5. SQL Server place the following  locks:

a. shared database lock
b. Intent locks on the table
c. Intent lock on the page
d. Update lock  on the rows

6. SQL Server “exclusive lock”  on the  rows to be updated
7. Updates happens in the data page cache in memory
8. SQL server logs the changes to log pages in memory
9. SQL server writes the log pages in memory  to transaction log on disk
10. SQL server release the locks and tell the user that update has completed
11. SQL server write to the logs which is called the “write ahead log”
12. SQL server updates the records in the data file at every checkpoint.

            One of the new feature of SQL server 2005 is the CLR integration.  This feature allows the use .NET base classes such as the use of arrays  to expand SQL Server functionality. This feature is somewhat similar to extended stored procedures in earlier SQL Server versions.

            This feature is supported even by the express edition. To make use of this feature you must first enable it using the SQL server surface area configuration by clicking on start>programs>Microsoft SQL Server 2005>configuration tools>SQL server configuration manager> “Surface area configuration for features”; choose the database server you want to configure;  choose CLR integration feature and checking the checkbox to enable this feature.

SQL-CLR Integration Exercise

     In this exercise you shall be able to create a simple assembly and deploy it to SQL server database.

Software requirements:

1. For advance users you need SQL server 2005 express
2. For beginners, SQL server 2005 enterprise or developer edition
3. .Net framework version 2

Steps

1. Open notepad. And type the following codes

Imports System
Imports System.Data
Imports Microsoft.SqlServer.Server
Imports System.Data.SqlTypes
 
Public Class HelloWorldProc
    <Microsoft.SqlServer.Server.SqlProcedure> _
    Public Shared  Sub HelloWorld()
        SqlContext.Pipe.Send("Hello world!")
    End Sub
End Class

2. Create a CLR folder on drive c: and save the file you create in step 1 as “clrtest.vb”

3. Locate your Visual basic compiler(VBC) compiler which is deployed together with the .net framework.  It is commonly located in  “windows” or “winnt” folder

4. Add the path to your environment in the control panel>system>system properties>advance>environment variables. Click new and type “path” on the “variable name” textbox and type the path to the compiler in the  “variable value” in my case its “C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727”
5. Click start > run > type “cmd”
6. Compile the codes to an assemble using the “/target” switch
7. type vbc /target:library c:/clr/clrtest.vb
8. Its going to create a dll file called “clrtest.dll”
9. Run the following T-SQL statement. Be sure that you are running on a Sql server 2005 database.

CREATE ASSEMBLY testclr  from 'c:\clr\clrtest.dll' WITH PERMISSION_SET = SAFE

This will load the assembly into the database

10. Create a stored procedure that uses the assembly

CREATE PROCEDURE hello
AS
EXTERNAL NAME testclr.HelloWorldProc.HelloWorld

11. Execute the stored proc

Exec hello

12. It should return

“Hello World!”

When and when not to use CLR integration

When to use CLR
 Use SQL-CLR integration for:
 1.  Computationally intensive functions
 2.  Complex logic
 3.  Data structures not supported in T-Sql (e.g. arrays)
 4.  Accessing  functionality in the base class library
 5.  SQL reader is faster than TSQL cursor for forward only read only navigation
6.  string handling, regular expression validation, file access, general   cryptography, replacing external procedures

When not to SQL-CLR

 Do not use CRL integration when/for
 1.  Basic data access operations
 2.  Procedures with little or no procedural logic

Security

SQL-CLR integration is off by default

Three levels of security  for assemblies specified during create assembly refer to step 9 of the exercise.

1.  Safe (default)
2.  External Access(registry, files,environment)
3.  Unsafe (unrestricted)