SQLServerCentral / Discuss Content Posted by Steve Jones / Article Discussions / Article Discussions by Author / The CLR in SQL Server 2005 / Latest Posts

RE: The CLR in SQL Server 2005

Ryan P. Fonkert — Tue, 29 Jan 2008 14:50:20 GMT

I agree with what Alan and Steve have said. It's great tool that has its uses, but shouldn't be used whenever possible simply because it can be. So far I've only had one case where I found it necessary to create a .NET (CLR) stored procedure, but it was an invaluable tool when I needed it. The procedure involved some pretty advanced string manipulation, with an eventual query string of over 8k, pulling data down from an Oracle db to our SQL Svr instance. For all my other report and ETL processes, I've never actually needed it, even though it'd be cool to use, so didn't use it. But that one time it was a lifesaver, and I wouldn't hesitate to do it again if the situation presented itself.

RE: The CLR in SQL Server 2005

timothyawiseman — Tue, 29 Jan 2008 11:42:18 GMT

[quote][b]rick (1/25/2008)[/b][hr]Grasshopper: I have to take issue with your statement that TSQL is a horrible language. From my 38 years first as a developer and then a DBA, I can vouch for the fact that for the languages I have used over the years, Assembler, Autocoder, Cobol, Basic, RPG, Fortran, and for the past 15 yeas SQL, including TSQL, there are many more bad developers than bad languages. SQL, IF you know how to use it, is an extremely powerful and fast tool. But it's just like woodworking. Having the best tools doesn't buy you a thing if you don't know how to use them. ...[/quote]I have to completely agree with Rick. SQL is a beautiful language for what it is intended. There are some things for which it is simply not meant that you can shoehorn it into, but within its domain it is unsurpassed and graceful.

RE: The CLR in SQL Server 2005

Martin Bastable — Tue, 29 Jan 2008 02:20:51 GMT

On the backend business logic front, ive been steadily increasing how much I put directly in the db rather than in the front end for some time now.My main db here has several front ends crossing seperate technologies. being able to write a single shared set of instructions on the server once, rather than having to re-write several versions in each front end, I find is a real saver.CLR wise, most of my interest has been (as others seem to like too) in mroe `support` functions - data validation, string manipulation, that kind of thing. And to that end, I have a dream!Part of the sql server environment in management studio, an extra `CLR` branch under your database somewhere around Programmability - where adding/editing/deleting/etc. simple (e.g. .net) functions is as easy as doing the same with stored procedures :) No messing around!martin :)martin!

RE: The CLR in SQL Server 2005

Sean Zhang-369248 — Sat, 26 Jan 2008 15:40:47 GMT

I totally agree with Alan Robbins. .Net framework is powerful, solid and beautiful. Hosting CLR in SQL Server brings the core of .Net to us SQL developers and administrators. It is up to us to learn to harness that power to our advantage.In our experience with the integration, in addition to the trivial CLR string manipulation (mainly Regex) functions and UDTs, we have successfully brought thirty-party "sealed" objects (either in Win32 DLL, COM, or .Net assemblies) into our SQL applications. For example, we can do massive VIN decoding, address parsing and geo-decoding, yellow/white paging, SSN decoding, and IBM MQ messaging, etc, within our databases natively. That has greatly enhanced programming productivity and processs integrity. Of course, we have had to carefully architect the CLR integration so that all those heavy duty thirty-party objects are hosted on a separate .Net application server with the SQL server being only the .Net remoting client.System.Runtime.Remoting is not fully supported in SQL 2005. We are looking forward to SQL 2008 to have a more complete CLR support.

RE: The CLR in SQL Server 2005

Marios Philippopoulos — Fri, 25 Jan 2008 15:45:58 GMT

Apologies if this is not the right forum for code samples, but here is a quick example of how to set up a sproc called CLR_usp_CopyFile to copy files. For the sake of brevity I have omitted any code not needed for this illustration:.NET code:-----------using System;using System.Collections;using System.Data;using System.Data.SqlClient;using System.Data.SqlTypes;using System.IO;using Microsoft.SqlServer.Server;namespace fred{public sealed partial class FileIO{ public static void CopyFile( string sourceFileName, string destFileName ) { using (SqlConnection conn = new SqlConnection("context connection = true")) { File.Copy( sourceFileName, destFileName ); } }}}//end of codeCompile into an assembly, copy dll to location accessible to SQL Server instance.SQL Script to deploy assembly and build SQLCLR sproc:-----------------------------------------------------USE [master]GO-- Enter correct path of dll:CREATE ASYMMETRIC KEY FileIO_Key FROM EXECUTABLE FILE = 'C:\SQLCLR\FileIO.dll';CREATE LOGIN FileIO_Login FROM ASYMMETRIC KEY FileIO_KeyGRANT EXTERNAL ACCESS ASSEMBLY TO FileIO_LoginGO-- Create the assemblyCREATE ASSEMBLY FileIO FROM 'C:\SQLCLR\FileIO.dll' -- Enter correct path of dllWITH permission_set = EXTERNAL_ACCESS;GO--CLR_usp_CopyFileCREATE PROCEDURE dbo.CLR_usp_CopyFile( @SourceFileName NVARCHAR(255), @DestFileName NVARCHAR(255))AS EXTERNAL NAME FileIO.[fred.FileIO].CopyFile;GO--end of codeUsage:------EXEC master.dbo.CLR_usp_CopyFile @SourceFileName = 'c:\fred', @DestFileName = 'd:\fred'

RE: The CLR in SQL Server 2005

Greg Grow — Fri, 25 Jan 2008 09:53:13 GMT

Wow, this has been a good discussion to read. Of course, it took me a while to get through all of the posts. It must be that I am on the West Coast, and I already have 6 pages to read. Well, my thoughts regarding CLR and TSQL. I expect TSQL will stay around for as long as we have DBMS systems. SQL Server and other DBMS systems are maintained by DBAs or there "want to be" counter-part, the Developer. DBAs are trained and skilled with planning, maintaining, designing, and tuning the database engine and tools. Developers are mainly focused on the development aspect and they try to perform the same tasks that the DBA performs, which in my opinion takes years to accomplish. Therefore, I don't expect any SQL language to disappear but further enhancements will be made to help the DBA or Database developer to work more efficiently.When we talk about CLR and SQL Server, I believe there is a time and place for all technology. I have used a few CLR stored procedures and functions, but they are uncommon in most of the environments that I have worked in. I have created REG-EX functions, record-set based stored procedures for data collection of Operating System data that would otherwise be difficult with regular TSQL. Anyone who writes code using the sp_OA objects could potentially rewrite using CLR with greater security and flexibility. I do advocate the use of CLR objects when they are needed or are tested with greater performance for complex calculations. However, TSQL is my top choice for most logic, set based, and code reuse. Profiling CLR is not the easiest task. Monitoring performance and reviewing "Execution Plans" are an important part of the DBA Role. If these tasks are not performed, then the DBA probably isn't focused on the well-being of there environments.Technology is awesome, and the CLR has provided more features and greater benefits to the DBA. The CLR must not be used as the default logic for the RDBMS. Experienced DBAs will carefully evaluate the technology used in the DBMS.Thanks Steve, this is another good post.

RE: The CLR in SQL Server 2005

Marios Philippopoulos — Fri, 25 Jan 2008 09:29:57 GMT

Thank you Adam, will do

RE: The CLR in SQL Server 2005

Adam Machanic — Fri, 25 Jan 2008 09:27:37 GMT

[quote][b]Marios Philippopoulos (1/25/2008)[/b][hr]Inside MICROSOFT SQL SERVER 2005: T-SQL PROGRAMMINGby Itzik Ben-GanSolid Quality Learningis a great introduction to SQL CLR[/quote]Absolutely! And may I also suggest..."Pro SQL Server 2005"by Tom Rizzo, Adam Machanic, et al(Apress, 2005)... and when you're done with that one, check out the book listed in my signature for even more ...:D

RE: The CLR in SQL Server 2005

Adam Machanic — Fri, 25 Jan 2008 09:25:08 GMT

[quote][b]don_goodman (1/25/2008)[/b][hr]What is not understood about interpreted code be it T-SQL, CLR, JAVA or VB3 code? When you add 2 layers of interpretation to a piece of code, it will run slower than it did when it had to be interpreted once.[/quote]First of all, CLR code is not interpreted after the first time it's run. So yes, there will be a performance hit the very first time the code is called, but subsequently (until a reboot, service restart, etc) a natively compiled version will be used. This is known as just-in-time compilation... Google for more info.Second, SQLCLR called from T-SQL is certainly not slower than straight T-SQL. I have tested this fairly extensively over the past three and a half years and am confident in the fact that the technology performs well and has some great potential uses. That said, it certainly has incredibly potential for misuse. But I am personally of the mind that you shouldn't shield potentially powerful technologies simply because they might not get used in the best of ways. If developers and/or architects don't do their research and misuse this stuff, that's their problem, not yours or mine.

RE: The CLR in SQL Server 2005

Marios Philippopoulos — Fri, 25 Jan 2008 09:23:48 GMT

Inside MICROSOFT SQL SERVER 2005: T-SQL PROGRAMMINGby Itzik Ben-GanSolid Quality Learningis a great introduction to SQL CLR

RE: The CLR in SQL Server 2005

YSLGuru — Fri, 25 Jan 2008 09:20:51 GMT

[quote][b]Alan Robbins (1/25/2008)[/b][hr]I am reminded of the old adage:If the only tool you have is a hammer, everything looks like a nail.The more flexibility we have, the more agility we have, and that endears us to the business. A professional picks the RIGHT tool for the job, and doesn't complain about the other guy's toolbox.There is a place in this world for DOS Batch files, T/SQL, .NET, and yes even VBA and they are all good tools if they are used to solve the problems that they were designed for. .net code in the database gives us another option, and this is not a bad thing. Complaining that those "developers" want to put their "bad" code in "your" database is seriously immature, learn .net and get over it.:w00t:[/quote]Well said Alan!

RE: The CLR in SQL Server 2005

Marios Philippopoulos — Fri, 25 Jan 2008 09:19:22 GMT

Alan Robbins, I couldn't agree more! :)

RE: The CLR in SQL Server 2005

YSLGuru — Fri, 25 Jan 2008 09:18:21 GMT

[quote][b]rick (1/25/2008)[/b][hr][quote][b]Straegen (5/2/2005)[/b][hr]Not buying it. In many (read most) small companies, the dev is the SQL guy and the software developer. Most small company devs will leave TSQL quickly since working in one language is easier than two. It will take longer for large companies to switch as they move slower for good reason, but small companies are usually the first indicators of where the industry is headed. With the switch to dotNet and many apps being rewritten, it won't take long for TSQL to become CLR code in most shops. The great news is that TSQL developers willing to make the switch will likely find good jobs over the next decade porting code. Also, TSQL is horrible... let me say it again... HORRIBLE as a language. CLR is better and I speak from experience. This has been coming for years and all the major commercial SQLs will all switch sooner or later. I can see MySQL and smaller engines staying in SQL syntax for a while, but it wouldn't suprise me to see some PHP or similar engines being fitted for them as well.Just like COBOL had to die and VB Classic will soon be pronounced, it is time to stick a fork in a language that should have been dead years ago. I get that DBAs with little or no programming skill might beworried, but in my experience it isn't the TSQL that keeps a DBA around. It is the tuning and maintenance which software developers generally don't want to do or are incapable of doing.My last comment on the subject is don't be an ostrich on this one. If you do,youwill likely pull your head from the sand and realize that a part of your skillset has been rendered obsolete. I still remember my COBOL friends from the mid 90s saying there is just too much tested COBOL code to replace. COBOL will be around for my lifetime. That VB stuff isn't as solid and proven. Yada... yada... yada. Theyall eventually converted and unfortunately for most it was too late. Being the new kid on the block at age 50 just isn't pretty.[/quote][/quote]I must concur with Ricks comments about the above with a big AMEN and would add the following:Your comments about TSQL and Programming languages (notice how I listed these seperately) show either a lack of a clear understanding of the differences between each or a lack of expereince in general. TSQL can't be a HORRIBLE language because it is not a Programming langauge like VB/C/C#/ . TSQL is a set of commands for working with data sets, not for programming. Yes you can create Stored Procedures and even UDFs which are similiar in some ways to functions/procedures in programming langauges but they are very different. SQL, be it T-SQL, PL/SQL or any other flavor of the core SQL are for working with sets of data where as programing langauges are for not. This is not to say you can't work with a RDBMS using a langauge like VB, only that the flavors of SQL are designed to work directly with the RDBMS where as VB and other programming langauges are not. In fact progarmming lanaguges like VB end up using SQL at some point to get at data in a RDBMS, you just don't see it because the SQL is in a Blackbox such as a DLL or similiar object/file that contains objects you can consume. Lastly, you are being the Ostrich if you honestly believe that expecting a developer to have to know/work with just 1 langauge (whether its because its easier or any other reason) and or if TSQL is headed the way of past programming langauges. So long as we use RDBMS to store data there will be some form of SQL in use even if it's not named T-SQL in the case of SQL Server.Ed CDBA/Developer

RE: The CLR in SQL Server 2005

Alan Robbins-417146 — Fri, 25 Jan 2008 09:10:19 GMT

I am reminded of the old adage:If the only tool you have is a hammer, everything looks like a nail.The more flexibility we have, the more agility we have, and that endears us to the business. A professional picks the RIGHT tool for the job, and doesn't complain about the other guy's toolbox.There is a place in this world for DOS Batch files, T/SQL, .NET, and yes even VBA and they are all good tools if they are used to solve the problems that they were designed for. .net code in the database gives us another option, and this is not a bad thing. Complaining that those "developers" want to put their "bad" code in "your" database is seriously immature, learn .net and get over it.:w00t:

RE: The CLR in SQL Server 2005

don_goodman — Fri, 25 Jan 2008 08:46:21 GMT

What is not understood about interpreted code be it T-SQL, CLR, JAVA or VB3 code? When you add 2 layers of interpretation to a piece of code, it will run slower than it did when it had to be interpreted once.T-SQL is interpreted by the database engine (more or less). C# or VB.NOT is interpreted by the CLR. Assuming that the C#/VB.NOT programmers put T-SQL into the C# stored procedures, we get multiple interpretations of the code. This must run slower that T-SQL alone. That C#/VB.NOT coders would combine T-SQL and their language code together is a certainty; that they can do it means they WILL do it. Microsoft knows this yet they created a situation where this compound interpretation problem must inevitiably result.The CLR inside SQL Server is a stupid idea just as JAVA inside Oracle was a stupid idea. It is bad enough that T-SQL must be interpreted and that Stored Procedures and Functions are partially compiled instead of fully compiled. But, adding the CLR and enabling inexperienced programmers (the kind usually hired by startup specialty companies with a good business idea) to use it in stored procedures added insult to injury.Truly a dumn, dumn, dumn idea to start with and it will be an even dummer idea in 2008 and beyond.

RE: The CLR in SQL Server 2005

Steve Jones - SSC Editor — Fri, 25 Jan 2008 08:42:43 GMT

Some interesting comments and thanks for the debate.I think the idea of replacing xp_cmdshell for specific functions is a great one, and one that I hadn't thought of when I first wrote this article. that alone would make sense, though I'm not sure why MS hasn't implemented those. Maybe we need an open source project for some those functions!I'd defintely be interested to know specifically where you've found it useful. I have heard of issues with large strings (>8K), so be careful if you're building XML to send out.I don't like the idea of SQL Server being an application server. It's too limited a resource to be straining with other functions. Maybe there should be a "SSApp Server" that lets you easily run some of these processes, costs less than SQL, and manages things like business logic.

RE: The CLR in SQL Server 2005

barkers1221 — Fri, 25 Jan 2008 08:02:59 GMT

I am thinking of implementing a CLR solution for sending an XML file to another company. Scenario: Company A runs SQL 2005 and so does company B. In a stored procedure at company A, instantiate a CLR object that is passed an result set from SELECT....FOR XML Auto, etc... and the CLR sends the XML file to a stored procedure at company B, which is configured with EndPoints. Therefore, the stored procedure at company A may be schedule to process throught the day.Let me throw my 2 cents into the ring on the business layer. I believe the business logic should reside in the stored procedures. I know Republican vs. Democratic type of topic. As with anything, there are exceptions when this is the case, but for the most part I put the business logic in the stored procedures and returning appropriate results sets via SQL Reader is the fastest way to display data to the users. Users want data returned to them fast. We agree that logic is logic is logic and it needs to reside somewhere. Putting as close to the data as possible and just the reduction of text passed across the wire will speed it up. Go George Bush...oh yes...he is not running again ;)

RE: The CLR in SQL Server 2005

Wyatt Eurich — Fri, 25 Jan 2008 07:48:08 GMT

It feels to me that with 2005, Microsoft is moving SQL Server into the application server space. In some instances, perhaps a low transaction volume environment, it make sense that the application server and the database coexist. But then I don't think that a DBA is the right person to manage it.I do think that for the enterprise you still will want to separate out your database and your application servers. The nice thing is that the same software can serve both roles, capitalizing on your knowledge investment of the platform.Additionally, comparing SQL Server to other application servers, SQL Server has alot more to offer than most due to the integration of the CLR with the already strong database and XML support.

RE: The CLR in SQL Server 2005

rick-507511 — Fri, 25 Jan 2008 07:36:58 GMT

Grasshopper: I have to take issue with your statement that TSQL is a horrible language. From my 38 years first as a developer and then a DBA, I can vouch for the fact that for the languages I have used over the years, Assembler, Autocoder, Cobol, Basic, RPG, Fortran, and for the past 15 yeas SQL, including TSQL, there are many more bad developers than bad languages. SQL, IF you know how to use it, is an extremely powerful and fast tool. But it's just like woodworking. Having the best tools doesn't buy you a thing if you don't know how to use them. I have witnessed an appalling decline in the skill level of developers over the past ten years. Companies no longer want quality, they want quantity, and if the customers aren't yelling TOO loudly, that's OK, so lets push the next version out the door with more features that - sort of - work. I currently have a whole list of SQL processes created by our best developers, waiting to have me correct and improve logic for things as simple as reporting only 11 of the past 12 months, unless the process runs and succeeds in the last 10 minutes of the current month, with no possibility to rerun without code changes. I don't want these sincere folks dabbling in my databases!R Coffman, SQL Server DBA, with 38 active production servers.

RE: The CLR in SQL Server 2005

rick-507511 — Fri, 25 Jan 2008 07:23:14 GMT

[quote][b]Straegen (5/2/2005)[/b][hr]Not buying it. In many (read most) small companies, the dev is the SQL guy and the software developer. Most small company devs will leave TSQL quickly since working in one language is easier than two. It will take longer for large companies to switch as they move slower for good reason, but small companies are usually the first indicators of where the industry is headed. With the switch to dotNet and many apps being rewritten, it won't take long for TSQL to become CLR code in most shops. The great news is that TSQL developers willing to make the switch will likely find good jobs over the next decade porting code. Also, TSQL is horrible... let me say it again... HORRIBLE as a language. CLR is better and I speak from experience. This has been coming for years and all the major commercial SQLs will all switch sooner or later. I can see MySQL and smaller engines staying in SQL syntax for a while, but it wouldn't suprise me to see some PHP or similar engines being fitted for them as well.Just like COBOL had to die and VB Classic will soon be pronounced, it is time to stick a fork in a language that should have been dead years ago. I get that DBAs with little or no programming skill might beworried, but in my experience it isn't the TSQL that keeps a DBA around. It is the tuning and maintenance which software developers generally don't want to do or are incapable of doing.My last comment on the subject is don't be an ostrich on this one. If you do,youwill likely pull your head from the sand and realize that a part of your skillset has been rendered obsolete. I still remember my COBOL friends from the mid 90s saying there is just too much tested COBOL code to replace. COBOL will be around for my lifetime. That VB stuff isn't as solid and proven. Yada... yada... yada. Theyall eventually converted and unfortunately for most it was too late. Being the new kid on the block at age 50 just isn't pretty.[/quote]

RE: The CLR in SQL Server 2005

Mr. Phantomblot — Fri, 25 Jan 2008 06:14:55 GMT

My stance on SQLCLR is that it's just version 1.0. Its not too useful in set manipulation, but adds very convenient ways to fix a few irritating shortcommings in sqlserver 2005. i.e: local time to UTC time conversion, ISO8601 date management (which by the way is erroneous in the CLR also), hex numbers, web service consumption etc. These things takes a few lines of code when you have the CLR available, but is quirky and tedious in t-sql. For set manipulation and production, SQLCLR is far to immature. For instance, there is no quick way to make a SqlDataReader from a DataTable in SQLCLR, whats up with that? Maybe a hint of LINQ in SQLCLR will do wonders?But these are all lackings that ms should have fixed a long time anyway. And a major overhaul of T-SQL is long overdue. And I wish MS focused more on that:* Variable definitions. T-SQL need %TYPE and %ROWTYPE like in oracle, to simplify maintenance, development and perhaps make cursors useful in t-sql. * Implement ANSI SQL2003 analytical functions and windowing funcitions properly. RANK and ROW_NUMBER doesnt cut it. This can simplify queryies with complex predicates a lot!!!* Better namespacing and packaging of procedures, types, functions etc. Today we need to over-use schemas or encforce strict naming policies to avoid obscurity. Perhaps packages like in Oracle isn't a bad idea?* Functions for extracting complete DDL of objects. Its just silly that everyone has to make their own.* Make it possible to control user defined type's presentation when consumed by ODBC-clients. * Select * into @myTableVariable from MyTable. * Convert(DateTime,@Timstamp) and Convert(TimeStamp, @myDate)

RE: The CLR in SQL Server 2005

Marios Philippopoulos — Fri, 25 Jan 2008 03:58:43 GMT

xp_cmdshell is a big reason for enabling SQL CLR.In the company I work for we have started replacing functionality using xp_cmdshell with calls to specialized stored procedures and UDFs. So something like:xp_cmdshell 'dir ...' is replaced by a call to a CLR sproc that returns a result set with the information. And so on for other manipulations in the file system.You talked about security nightmares. Well, there is one right there. Through xp_cmdshell a developer has complete control over file system operations, including file deletions, additions etc. Aside of malicious intent, human error can end up causing havoc in the file system (replace 'dir' above with 'del'). Replacing these kinds of very non-specific calls to xp_cmdshell with calls to highly specialized SQLCLR objects that each do a specific task (file deletion etc.) gives the DBA some of the control back. One final example on this. If anyone has ever tried using xp_cmdshell to determine the free space of a drive programmatically will know how tedious that is. With the right SQLCLR object it takes 3 lines of code!We are entering an era where DBAs will have to start thinking of themselves as IT Professionals, not just DBAs. If we don't feel comfortable with a certain technology, all the more reason to roll up our sleeves and learn it. I would go one step further and say it is our duty as DBAs to point the right path to developers for all things pertaining to SQL Server, including the CLR. Eventually the responsibility for a smooth-running database system rests with us.One final point about performance. I don't think anyone objects to the fact that T-SQL is inadequate in certain situations. Operations, such as string manipulation and complex mathematical calculations are not what T-SQL was made for, and it is not always possible to split these components off to a separate business tier. The challenge for DBAs and developers is to exercise judgement on the most judicious use of SQLCLR. It's an exciting challenge that we all need to embrace moving forward.

RE: The CLR in SQL Server 2005

timothyawiseman — Fri, 25 Jan 2008 01:53:41 GMT

What is the best way to learn to develop CLRs and learn first hand what they are useful for and when they are useful?

RE: The CLR in SQL Server 2005

Paul Thornett — Fri, 25 Jan 2008 00:32:54 GMT

I know this is a very trivial point, but you do this so often that it's really setting my teeth on edge !"Since it's release" should be "Since its release". The apostrophe in "it's" is reserved exclusively for "It is", and NOT the possessive!

RE: The CLR in SQL Server 2005

MikeGale — Mon, 16 Jan 2006 13:05:00 GMT

Many people's worlds are shaped and limited by the execution environments and languages they know and work in.

Whether it's desktop app, web app, database, OO language, set language, matrix language... these things each have their power and awful weaknesses. They are both the way to progress and the biggest barrier to it.

Anything that provides useful additional power and breaks the mental barriers and mental poisoning is likely a step forward.

To me the CLR in the dB server is one of those beneficial ideas. It doesn't add much for set handling but it does a lot else that will be useful.

The point about operators is a good one, in my view. OO languages do just about everything with methods. Why? They are not designed for building decent operators easily. To appreciate what can be achieved look at a matrix language like APL or J. These things are a true joy to work with. Ties in very nicely with sets/dB but most programmers/dBA's are unaware that this power is already to hand.

RE: The CLR in SQL Server 2005

Chris Hedgate — Mon, 16 Jan 2006 10:42:00 GMT

I am just not so sure that the DBMS should include all the behavior of a type. A mutator operator such as the one you described might be necessary to move a point, but I am not sure that the DBMS needs to be able to do it. If the DBMS is able to represent the data (and validate it) then it might be enough to let the users move the points around. But then I would probably store the point as the floats for the latitude and longitude. But you are right that the possibility to enforce business rules fully is quite appealing.Anyway, what I wanted to say with my first post was that I do not think the fear shown by DBAs of 'bringing in the middle-tier into the database' is fully justified. With stored procedures and other constructs we have had (parts of) the middle-tier inside the database for a long time. However, on the other hand that does not mean that we should now implement everything in an application inside the database. For instance, just because we can have the database download files it does not mean that it is always the correct thing to do.

RE: The CLR in SQL Server 2005

Adam Machanic — Mon, 16 Jan 2006 00:07:00 GMT

Really? What if I define a point (latitude/longitude) datatype? Does SQL Server have an operator that will let me move my point 10 miles east? Why don't you like type-specific operators? Remember that a type is not defined only by its domain, but also by its operators--without operators, there really is no reason to define a type at all. Why define something that you can't work with?

RE: The CLR in SQL Server 2005

Chris Hedgate — Sun, 15 Jan 2006 23:57:00 GMT

Naturally, no. What I am saying is that I do not like adding more operations that are only used for specific types for manipulating data, when the existing set of operations are fully enough of handling it.

RE: The CLR in SQL Server 2005

Adam Machanic — Sun, 15 Jan 2006 11:43:00 GMT

So you're suggesting that the '+' operator be removed from SQL?

RE: The CLR in SQL Server 2005

Chris Hedgate — Sun, 15 Jan 2006 11:41:00 GMT

Don't get me wrong, I am very positive about CLR integration, and functions in particular is where I see myself using it the most. In the URL example I would probably use a function in a check constraint to enforce the logic.That said though, I agree that a better domain support would be great, and I might use UDTs to give me a little of it in this way. With UDTs however I am afraid that people will start adding externally visible functionality, e.g. not methods for internally enforcing the domain. Manipulating data in a database should not require any other operations than the normal DML.

RE: The CLR in SQL Server 2005

daninmanchester — Sat, 14 Jan 2006 10:59:00 GMT

A few of my thoughts for what they are worth.

Personally my opinion has always been that a database is for the data and thats about it. Obviously some business logic does creep in there to maintain data integrity but I do try to keep it pure.

Also I do find that when a business process can hit the database server a number of times it can be better to roll some of this functionlity into a stored procedure and hit the database once.

In a load balanced environment where there are multiple web servers and one DB server the database can often experience considerable load and yet the web farm is working well within limits. It's for this reason that I would be against putting to much logic into the database. It's much cheaper and less hassle for me to add webservers (or aditional application server) to handle the business side where as upgrading the database can often be more complex and costly.

Having said that the integration of the CLR is a welcomed addition as it solves some problems that I have previously 'fudged' in the business layer to get the data in form that I want. e.g. statistical calculations and string agregation

RE: The CLR in SQL Server 2005

Adam Machanic — Sat, 14 Jan 2006 07:41:00 GMT

Stored procedures, sure. But you're forgetting about functions and--even more importantly--user-defined types.Example: Assume you're coding an application that needs to store URLs in a few different tables. Can you write a T-SQL UDDT that can self-verify the format of the URLs in every place it's used? I can write a CLR function that does so. This means that my type is actually more than just an aliased T-SQL type--it enforces its domain, without need for additional constraints.

RE: The CLR in SQL Server 2005

Chris Hedgate — Sat, 14 Jan 2006 02:24:00 GMT

Adam, I agree fully that 'true' business logic as you define it has only one place and that is in the database. However, I also believe that the logic should be declaratively enforced (to 100%). Stored procedures, whether written in T-SQL or a .NET-language, are just not part of the database. They are a part of an application that use the database, a 'middle' tier that is placed inside the database server.On a higher level I have many issues with the tight coupling between databases and stored procedures (again, no difference between T-SQL and CLR), but I think that like you I am very interested in the integration of CLR in SQL Server and the new possibilities it brings to implementing database routines. It is a new tool, but like all tools it has it's uses and there are things it should not be used for.

RE: The CLR in SQL Server 2005

V.Kadal Amutham — Sat, 14 Jan 2006 02:05:00 GMT

I am quite new to SQL and Visual Studio 2005. For the past few monhts I am working on Sql Server 2005 and working on to develop ERP software for Textile Industy. I find T-SQL very convenient to enforce Business Logic. Data is stored in number of tables and each table has a small trigger to check and enforce business intelligence. So the whole process become very moduler, independent of one another. Visal Studio is used to design windows form and Crystal Report for taking out simple report. Out Clients are looking for software where developmental time will be small even if it is not tested 100%. People who are currently working SQL Server 200 and VB6 are approaching us for faster implementation of business logic and getting some quick resultV.Kadal Amutham

RE: The CLR in SQL Server 2005

Adam Machanic — Fri, 13 Jan 2006 15:08:00 GMT

The ultimate weapon against developers who say they want to re-write all of the stored procedures in CLR languages:"Sounds like a great idea to me! Let me know how it works out."-Guaranteed- after less than two days work the developer will give up on that idea once he or she understands what CLR integration truly brings to the table--and doesn't bring to the table.

RE: The CLR in SQL Server 2005

Francis Apel — Fri, 13 Jan 2006 15:03:00 GMT

A voice of reason, crying out in the dark...! Finally, someone with real-world experience speaks about the CLR...

Thanks for the great article, especially the line:

...And like MDX, there will be 7 people in their country that understand what they did and maybe a few more that care. But they'll be able to create code to perform complicated analyses that is impossible in SQL Server 2000 and when they have to explain the meaning to a business user, they'll wish they hadn't...

I believe the addition of the CLR into SQL Server is simply Microsoft keeping up with the Jonses. Will there be uses for it? Sure. Will it be over used, absolutely. I already have developers talking about rewriting all of their stored procedures in the CLR so that their procedures will run faster an be easier to manage... (insert loud scream and pulling out of hair here).

RE: The CLR in SQL Server 2005

currym — Fri, 13 Jan 2006 09:29:00 GMT

Wow, this is a hot post!

I'm open to the opportunities the CLR offers, but it does place more responsibility on the lowly DBA (and job security for those who figure it out).

I appreciate the realistic and sarcastic comments in the article. It was a fun read.

RE: The CLR in SQL Server 2005

Yelena Varshal — Fri, 13 Jan 2006 08:53:00 GMT

I agree with Adam Machanic that the business logic can be very well located in a database. I also think that a 3-tier design is OK. So in my opinion if the application requires a distributed design for performance or security purposes, the next Microsoft recommendation should be to have a middle tier as a database as opposed to the application layer. One database on one server is for data only and the second database on the same or another server with all business rules, state management, user management, CLR, Future CLR etc...

Yelena

RE: The CLR in SQL Server 2005

Keiths123 — Fri, 13 Jan 2006 06:01:00 GMT

I like it, but I have spent an equal amount of time being a developer as being a DBA so I have a reasonably idea of when it works and when it doesn't. I also fully understand the fear factor involved when you realise less experienced developers could by writing code directly on your database...It's use would be when you are using cursors to do work as the .Net code has proven to be quicker than T-SQL (I will try to find a link to back that statement up) and also when you need to access components to do work. Trying to do that in T-SQL is possible but horrible. It is much better IMHO to use .Net procedural code to access components than SQL, if only for the error handling capabilities! T-SQL may now have try catch statements but they are not as good as the .Net equivalent.

RE: The CLR in SQL Server 2005

ALZDBA — Fri, 13 Jan 2006 04:23:00 GMT

As always, there is no straight forward in a relational environment

If you use it in a well-considered way you'll gain from it, else it'll be a pain in the ... .

Remember the time when we first had triggers ? Sufferd them and then got a bit familiar and at ease because some major applications sufferd due to overuse and the dba finaly was no longer alone to "hold the horses" !

To most devs this is will be joyride until they hit something and finaly come to reason. The timegap for this event is what scares most dba's, because they will be in the line of fire !

I always like to read the comments on articles like this one