Introducing SQL Server 2005's CLR Integration - Part 1

T-SQL is great for database code, but writing procedural code in T-SQL has always been difficult. Invariably, your project includes a stored procedure or two, requiring some text parsing or complex math operations. Doing this in SQL Server has been difficult. Either you wrote T-SQL code, or you wrote an extended stored procedure/function and used COM to interoperate them. Neither was a good solution, but that is all we had.
In comes SQL Server 2005 with its CLR integration to alleviate these problems. By integrating the CLR, SQL Server 2005 allows you to deploy C# or VB.NET code that is used within the SQL Server process. This means that if you need complex procedural code, you can write it as managed code.

Integrating the CLR into SQL Server is not a step to eliminating T-SQL. As .NET developers, it may seem like a good idea to do all your database code in managed code, but this is not the case. Think of the CLR integration as just another tool in your toolbox. This is the hammer that, I suspect, will be used to hammer in nails, screws, and 2x4s in projects the next year. It will be overused. Don't let your project be the ones caught guilty of this.

Details of the CLR Integration
Integrating the CLR into SQL Server involves a number of different features. Many of those features allow developers to do things they have never had the opportunity to do before in SQL Server. But, before I discuss the features and how they work, it's important to consider some details on how the CLR is hosted in SQL Server.

Integrating the CLR into the SQL Server 2005 engine was not done in a trivial way. At the end of the day, SQL Server 2005 has to be a rock-solid implementation of the database. Any new feature has had to endure intense scrutiny for how it will impact the stability of SQL Server.
In the 1.x version of the CLR, the chief client for hosting the CLR was Internet Information Server (IIS). IIS is a peculiar beast. If it finds badly behaving code, it is happy to kill threads and processes and just restart the code. Any code living within IIS was free to allocate memory, threads or even new processes, as it saw fit. Unfortunately, in SQL Server this is the opposite of the needed requirements. If some piece of CLR code starts to act in a bad way, destroying the SQL Server process is the completely wrong thing to do. The health of the SQL Server processes are critical to the stability of the platform.

The 2.0 version of the hosting environment has many more ways to communicating with the host environment. These new communication mechanisms allow for SQL Server to be in control of key operations of the CLR. SQL Server may refuse to allow creation of new memory or new threads, and disallow the destruction of the host process. In addition, the CLR integration puts CLR code into a secure sandbox of operations to improve on the stability and security inside SQL Server.

Using Managed Code

Now that you have an assembly or two loaded, you want to know how to actually have code run within SQL Server. Within SQL Server, most types of code blocks that you are familiar with in T-SQL are supported in managed code:

• Stored Procedures
• Functions
• Triggers.

A new type of code is supported in SQL Server 2005 called a custom aggregation. This allows you to write code that supports aggregating data. You can do things like create a custom SUM or COUNT aggregation. You might create useful extensions to SQL Server, like Standard Deviations. I'll cover custom aggregations in more specifics below.
Using managed code within SQL Server 2005 requires three steps:
1. You must write the managed code and compile it into an assembly.
2. You must install the managed code's assemblies into SQL Server 2005.
3. You must use DDL statements to tie the managed code to named objects (Stored Procedures, functions, etc.)

Writing Managed Code

The easiest way to write managed code for SQL Server 2005 is to use Visual Studio 2005. As of the writing of this article, the full Beta 2 of Visual Studio is available; it works well with the SQL Server 2005 April(2005) CTP. To write managed code for SQL Server, you must have at least the Team Server edition of Visual Studio.

Using Visual Studio to write the managed code takes many of the details of deployment out of your hands, as it supports automatic deployment. This article ignores that fact; I'll explain how to write managed code and install it in SQL Server manually. Since your projects likely need Install scripts for managed code, this skill will soon be required for most projects.

For each type of managed code that is supported, there are related attributes that are used to decorate the code to help SQL Server know about specific behaviors about the managed code. These attributes include SqlProcedure, SqlFunction, SqlUserDefinedAggregate, SqlUserDefinedType and SqlMethod. Each of these attributes is explained below.

CLR Integration Vs Extended Stored Procedures

Writing code in a higher-level language isn't a brand new concept. In fact, even in SQL Server 2000 you could write extended stored procedures in a language such as C++ and run them directly inside the database. So, what does CLR integration offer that extended stored procedures don't? Consider the following:

1. CLR objects inside SQL Server allow you to neatly categorize them into three buckets of access restrictions. The developers at Microsoft took a long, hard look at every available class in the .NET framework and categorized them into three categories. Depending upon the access restriction your CLR object is under, the CLR object is restricted from using certain features of the framework. The three categories are as follows:
• SAFE: This is the default level and it is the most restrictive. This means that your code does not need any external resources in addition to the operation. Safe code can access data from the local SQL Server databases or perform computations and business logic.
• EXTERNAL_ACCESS: This level signifies that certain external resources, such as files, networks, Web services, environmental variables, and the Registry, are accessible.
• UNSAFE: This level, which you should try very hard to avoid, specifies that your code is allowed to do anything. In other words, you are requesting to be free of any granular-level control, and thus have the same permissions as an extended stored procedure.
2. Extended stored procedures are written in C++. CLR objects can be written in .NET-compliant languages such as C# or VB.NET, which are safer and easier to use.
3. CLR code is much more reliable than the native unmanaged code that extended stored procedures are written in. This is because you are freed from issues such as memory management, and your code is tied to appropriate access restrictions based on the code access security model of the .NET Framework.
4. CLR code has the ability to work with newly introduced data types such as varchar(max), varbinary(max), and XML.
5. CLR code has the ability to latch on to the current running database connection, also referred to as the context connection. Extended procedures have no option but to create a loop back connection, which involves a significant overhead.
6. CLR objects can be of various types, stored procedures, triggers, UDFs, TVFs, and aggregates, whereas extended stored procedures can only be extended stored procedures.
7. Native unmanaged code runs faster than .NET 2.0 code in most cases. This means that unless your code explicitly needs to work with the local data store it is operating on, native unmanaged code or extended stored procedures will in general perform better than CLR objects. There may be other factors affecting the actual performance, such as native-to-managed code transitions. This, however, is a very weak reason to give up the compelling advantages that the CLR presents you in general. In fact, with the introduction of CLR integration, you should almost never have to write extended stored procedures.

mscorcfg.msc

mscorcfg.msc is a .Net configuration tool, which allows us to manage .Net Framework GAC, security policies through Microsoft management console.We can run this tool from VS.NET command prompt by typing: - mscorcfg.msc.
(OR)
Open Start Menu>RUN>MMCThis opens the microsoft management console.In that window,File>Open>Browse to C:\WINNT\Microsoft.NET\Framework\v1.1.4322Here We can find mscorcfg.msc.Click Open.With this tool We can do the following tasks,

Manage the Assembly Cache
The assembly cache stores assemblies that are designed to be shared by several applications. Use the assembly cache to view, add, and remove the managed components that are installed on this computer.

Managed Configured Assemblies
Configured assemblies are the set of assemblies from the assembly cache that have an associated set of rules. These rules can determine which version of the assembly gets loaded and the location used to load the assembly.

Configure Code Access Security Policy
The common language runtime uses code access security to control applications' access to protected resources. Each application's assemblies are evaluated and assigned permissions based on factors that include the assembly's origin and author.

Adjust Remoting Services
Use the Remoting Services Properties dialog box to adjust communication channels for all applications on this computer.

Manage Individual Applications
Each application can have its own set of configured assemblies and remoting services.

Thanks to Satheesh

Ajax: A New Approach to Web Applications

If anything about current interaction design can be called “glamorous,” it’s creating Web applications. After all, when was the last time you heard someone rave about the interaction design of a product that wasn’t on the Web? (Okay, besides the iPod.) All the cool, innovative new projects are online.
Despite this, Web interaction designers can’t help but feel a little envious of our colleagues who create desktop software. Desktop applications have a richness and responsiveness that has seemed out of reach on the Web. The same simplicity that enabled the Web’s rapid proliferation also creates a gap between the experiences we can provide and the experiences users can get from a desktop application.
That gap is closing. Take a look at Google Suggest. Watch the way the suggested terms update as you type, almost instantly. Now look at Google Maps. Zoom in. Use your cursor to grab the map and scroll around a bit. Again, everything happens almost instantly, with no waiting for pages to reload.
Google Suggest and Google Maps are two examples of a new approach to web applications that we at Adaptive Path have been calling Ajax. The name is shorthand for Asynchronous JavaScript + XML, and it represents a fundamental shift in what’s possible on the Web.
What Is AJAX?
AJAX, short for Asynchronous JavaScript And XML, isn't a technology but rather a grouping of technologies. AJAX uses a communication technology (typically SOAP and XML) to send and receive an asynchronous request/response to the server, and then leverages presentation technologies (JavaScript, DOM, HTML, and CSS) to process the response. Applications using AJAX are legitimate today, because most browsers support the necessary technology. For a more detailed definition of AJAX, visit the AJAX Wikipedia entry.
What does AJAX really mean? It lets you execute a server-side method through a JavaScript call, without requiring a browser refresh. Think of it as a mini request/response that happens behind the scenes from the user. If you still aren't clear what AJAX is, take a look at two popular examples from Google: Google Suggests and Google Maps. If you are new to AJAX, the responsiveness of those two applications should make your skin tingle slightly.

Defining Ajax
Ajax isn’t a technology. It’s really several technologies, each flourishing in its own right, coming together in powerful new ways. Ajax incorporates:
· standards-based presentation using XHTML and CSS;
· dynamic display and interaction using the Document Object Model;
· data interchange and manipulation using XML and XSLT;
· asynchronous data retrieval using XMLHttpRequest;
· and JavaScript binding everything together.
The classic web application model works like this: Most user actions in the interface trigger an HTTP request back to a web server. The server does some processing — retrieving data, crunching numbers, talking to various legacy systems — and then returns an HTML page to the client. It’s a model adapted from the Web’s original use as a hypertext medium, but as fans of The Elements of User Experience know, what makes the Web good for hypertext doesn’t necessarily make it good for software applications.

This approach makes a lot of technical sense, but it doesn’t make for a great user experience. While the server is doing its thing, what’s the user doing? That’s right, waiting. And at every step in a task, the user waits some more.
Obviously, if we were designing the Web from scratch for applications, we wouldn’t make users wait around. Once an interface is loaded, why should the user interaction come to a halt every time the application needs something from the server? In fact, why should the user see the application go to the server at all?

How Ajax is Different
An Ajax application eliminates the start-stop-start-stop nature of interaction on the Web by introducing an intermediary — an Ajax engine — between the user and the server. It seems like adding a layer to the application would make it less responsive, but the opposite is true.
Instead of loading a webpage, at the start of the session, the browser loads an Ajax engine — written in JavaScript and usually tucked away in a hidden frame. This engine is responsible for both rendering the interface the user sees and communicating with the server on the user’s behalf. The Ajax engine allows the user’s interaction with the application to happen asynchronously — independent of communication with the server. So the user is never staring at a blank browser window and an hourglass icon, waiting around for the server to do something.

Who’s Using Ajax
Google is making a huge investment in developing the Ajax approach. All of the major products Google has introduced over the last year — Orkut, Gmail, the latest beta version of Google Groups, Google Suggest, and Google Maps — are Ajax applications. (For more on the technical nuts and bolts of these Ajax implementations, check out these excellent analyses of Gmail, Google Suggest, and Google Maps.) Others are following suit: many of the features that people love in Flickr depend on Ajax, and Amazon’s A9.com search engine applies similar techniques.
These projects demonstrate that Ajax is not only technically sound, but also practical for real-world applications. This isn’t another technology that only works in a laboratory. And Ajax applications can be any size, from the very simple, single-function Google Suggest to the very complex and sophisticated Google Maps.

A Primary & Foreign Key Retrieval Stored Procedure

Here is the stored procedure which will retrieve the primary and foreign key and its reference for a database in SQL Server. This is also an example for a stored procedure with cursors and temporary table.

create proc sp_GetMasterConstraints(@tabnm nvarchar(128),@flag nvarchar(128)='')
as

declare @reftabid int
declare @constid int
declare @refcolid int
declare @colid int

declare @colnm nvarchar(128)
declare @reftabnm nvarchar(128)
declare @refcolnm nvarchar(128)
declare @constnm nvarchar(128)

DECLARE @TempTable TABLE([Table Name] nvarchar(50),[Constraint Column Name] nvarchar(50),[Constraint Type] nvarchar(50),[Reference Table] nvarchar(50),[Reference Column Name] nvarchar(50),[Constraint Name] nvarchar(100))
select @flag=UPPER(@flag)
if @tabnm=''
begin
DECLARE cur_tabnm CURSOR FOR
select name from sysobjects where xtype='u'
OPEN cur_tabnm
FETCH NEXT FROM cur_tabnm into @tabnm
WHILE @@FETCH_STATUS = 0
BEGIN

begin
if @flag='PK'
begin
/*syscolumns.typestat=1 -> Not Null*/
/*syscolumns.xoffset=4 -> Primary Key*/
/*sysobjects.xtype='PK' -> Primary Key*/
if not exists(select SysObj1.name as [Table Name], SysCol.name as [Constraint Column Name], SysObj2.xtype as [Constraint Type] from sysobjects SysObj1,sysobjects SysObj2,syscolumns SysCol where SysObj1.name=@tabnm AND SysObj1.id=SysObj2.parent_obj and SysCol.id=SysObj1.id and SysObj2.xtype='PK' and SysCol.xoffset=4 and SysCol.typestat=1)
begin
INSERT INTO @TempTable VALUES(@tabnm,'-','-NA-','','','')
end
else
begin
select @colnm=SysCol.name from sysobjects SysObj1,sysobjects SysObj2,syscolumns SysCol where SysObj1.name=@tabnm AND SysObj1.id=SysObj2.parent_obj and SysCol.id=SysObj1.id and SysObj2.xtype='PK' and SysCol.xoffset=4 and SysCol.typestat=1
INSERT INTO @TempTable VALUES(@tabnm,@colnm,'PK','','','')
end end
else if @flag='FK'
begin
/*Foreign Key*/
select @constid=constid,@reftabid=rkeyid,@colid=fkey,@refcolid=rkey from sysforeignkeys where fkeyid=(select id from sysobjects where name=@tabnm)
select @reftabnm=name from sysobjects where id=@reftabid
select @constnm=name from sysobjects where id=@constid
select @colnm=name from syscolumns where id=(select id from sysobjects where name=@tabnm) and colid=@colid
select @refcolnm=name from syscolumns where id=@reftabid and colid=@refcolid
if not exists(select name from sysobjects where id=@reftabid)
begin
Select 'No Foreign Key Exist' as [Comments]
end
else
begin
INSERT INTO @TempTable VALUES(@tabnm,@colnm,'FK', @reftabnm, @refcolnm,@constnm)
end
end
else if @flag='ALL'
begin
/*syscolumns.typestat=1 -> Not Null*/
/*syscolumns.xoffset=4 -> Primary Key*/
/*sysobjects.xtype='PK' -> Primary Key*/
if not exists(select SysObj1.name as [Table Name], SysCol.name as [Constraint Column Name], SysObj2.xtype as [Constraint Type] from sysobjects SysObj1,sysobjects SysObj2,syscolumns SysCol where SysObj1.name=@tabnm AND SysObj1.id=SysObj2.parent_obj and SysCol.id=SysObj1.id and SysObj2.xtype='PK' and SysCol.xoffset=4 and SysCol.typestat=1)
begin
INSERT INTO @TempTable VALUES(@tabnm,'-','-NA-','','','')
end
else
begin
select @colnm=SysCol.name from sysobjects SysObj1,sysobjects SysObj2,syscolumns SysCol where SysObj1.name=@tabnm AND SysObj1.id=SysObj2.parent_obj and SysCol.id=SysObj1.id and SysObj2.xtype='PK' and SysCol.xoffset=4 and SysCol.typestat=1
INSERT INTO @TempTable VALUES(@tabnm,@colnm,'PK','','','')
end
/*Foreign Key*/
select @constid=constid,@reftabid=rkeyid,@colid=fkey,@refcolid=rkey from sysforeignkeys where fkeyid=(select id from sysobjects where name=@tabnm)
select @reftabnm=name from sysobjects where id=@reftabid
select @constnm=name from sysobjects where id=@constid
select @colnm=name from syscolumns where id=(select id from sysobjects where name=@tabnm) and colid=@colid
select @refcolnm=name from syscolumns where id=@reftabid and colid=@refcolid
if not exists(select name from sysobjects where id=@reftabid)
begin
Select 'No Foreign Key Exist' as [Comments]
end
else
begin
INSERT INTO @TempTable VALUES(@tabnm,@colnm,'FK', @reftabnm, @refcolnm,@constnm)
end
end
end
FETCH NEXT FROM cur_tabnm into @tabnm
END
CLOSE cur_tabnm
DEALLOCATE cur_tabnm
end
else if not exists(select * from sysobjects where name=@tabnm)
begin
Select 'Table Does Not Exist' as [Comments]
end
else
begin

if @flag='PK'
begin
/*syscolumns.typestat=1 -> Not Null*/
/*syscolumns.xoffset=4 -> Primary Key*/
/*sysobjects.xtype='PK' -> Primary Key*/
if not exists(select SysObj1.name as [Table Name], SysCol.name as [Constraint Column Name], SysObj2.xtype as [Constraint Type] from sysobjects SysObj1,sysobjects SysObj2,syscolumns SysCol where SysObj1.name=@tabnm AND SysObj1.id=SysObj2.parent_obj and SysCol.id=SysObj1.id and SysObj2.xtype='PK' and SysCol.xoffset=4 and SysCol.typestat=1)
begin
INSERT INTO @TempTable VALUES(@tabnm,'-','-NA-','','','')
end
else
begin
select @colnm=SysCol.name from sysobjects SysObj1,sysobjects SysObj2,syscolumns SysCol where SysObj1.name=@tabnm AND SysObj1.id=SysObj2.parent_obj and SysCol.id=SysObj1.id and SysObj2.xtype='PK' and SysCol.xoffset=4 and SysCol.typestat=1
INSERT INTO @TempTable VALUES(@tabnm,@colnm,'PK','','','')
end
end
else if @flag='FK'
begin
/*Foreign Key*/
select @constid=constid,@reftabid=rkeyid,@colid=fkey,@refcolid=rkey from sysforeignkeys where fkeyid=(select id from sysobjects where name=@tabnm)
select @reftabnm=name from sysobjects where id=@reftabid
select @constnm=name from sysobjects where id=@constid
select @colnm=name from syscolumns where id=(select id from sysobjects where name=@tabnm) and colid=@colid
select @refcolnm=name from syscolumns where id=@reftabid and colid=@refcolid
if not exists(select name from sysobjects where id=@reftabid)
begin
Select 'No Foreign Key Exist' as [Comments]
end
else
begin
INSERT INTO @TempTable VALUES(@tabnm,@colnm,'FK', @reftabnm, @refcolnm,@constnm)
end
end
else if @flag='ALL'
begin
/*syscolumns.typestat=1 -> Not Null*/
/*syscolumns.xoffset=4 -> Primary Key*/
/*sysobjects.xtype='PK' -> Primary Key*/
if not exists(select SysObj1.name as [Table Name], SysCol.name as [Constraint Column Name], SysObj2.xtype as [Constraint Type] from sysobjects SysObj1,sysobjects SysObj2,syscolumns SysCol where SysObj1.name=@tabnm AND SysObj1.id=SysObj2.parent_obj and SysCol.id=SysObj1.id and SysObj2.xtype='PK' and SysCol.xoffset=4 and SysCol.typestat=1)
begin
INSERT INTO @TempTable VALUES(@tabnm,'-','-NA-','','','')
end
else
begin
select @colnm=SysCol.name from sysobjects SysObj1,sysobjects SysObj2,syscolumns SysCol where SysObj1.name=@tabnm AND SysObj1.id=SysObj2.parent_obj and SysCol.id=SysObj1.id and SysObj2.xtype='PK' and SysCol.xoffset=4 and SysCol.typestat=1
INSERT INTO @TempTable VALUES(@tabnm,@colnm,'PK','','','')
end
/*Foreign Key*/
select @constid=constid,@reftabid=rkeyid,@colid=fkey,@refcolid=rkey from sysforeignkeys where fkeyid=(select id from sysobjects where name=@tabnm)
select @reftabnm=name from sysobjects where id=@reftabid
select @constnm=name from sysobjects where id=@constid
select @colnm=name from syscolumns where id=(select id from sysobjects where name=@tabnm) and colid=@colid
select @refcolnm=name from syscolumns where id=@reftabid and colid=@refcolid
if not exists(select name from sysobjects where id=@reftabid)
begin
Select 'No Foreign Key Exist' as [Comments]
end
else
begin
INSERT INTO @TempTable VALUES(@tabnm,@colnm,'FK', @reftabnm, @refcolnm,@constnm)
end
end
end

select * from @TempTable
GO


Here is the execution of particular stored procedure in pubs database

exec sp_GetMasterConstraints '','pk'
exec sp_GetMasterConstraints '','fk'
exec sp_GetMasterConstraints '','all'
/*exec sp_GetMasterConstraints [table name – it will retrive all tables info if table name is not there],[Option ‘pk’ – Primary Key Alone, ‘fk’ Foreign Key Alone, ‘all’ - Both]*/