The CLR in SQL Server 2005

  • Comments posted to this topic are about the content posted at

  • I'm with you on this one Steve, I also think it will bring more head-aches than happy customers as developers (in general) love technology not business so they tend to use the latest technologies without fully understanding them as using them helps them fully understand them! The catch 22. It reminds me of XML in the database and how loads of developers started getting it and storing it in databases for no apparent reason to me than to help build their CV...




    Mark Baekdal

    +44 (0)208 241 1762

    Build, Comparison and Synchronization from Source Control = Database change management for SQL Server



  • Steve

    Totally agree and glad you brought this up. When I first heard about this I assumed that it might be part of an effort to reduce perceived TCO for SQL Server at smaller shops that rely on developers to support the db instead of a full time dba. I've worked at several clients lately, not so small ones too, that did not have what I consider to be a real dba ...sql server was so easy to administer that developers supported the db. That's off the subject and Iwon't go any further down that path  but my initial take on including the CLR was that maybe Microsoft sees the role of the DBA changing. Just my opinion ...Can anybody recommend a good book on dot net?


  • 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 be worried, 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, you will 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.  They all eventually converted and unfortunately for most it was too late.  Being the new kid on the block at age 50 just isn't pretty.

  • I cant believe that you are comparing T-SQL to COBOL and attempting to draw a valid conclusion.  The very fact that you would compare two such tools leads me to believe that you have not really developed for a modern RDBMS.  TSQL is a SET based language.  The only people I have ever heard of that complain about the language * do not understand how to use it* and try to use it in a procedural fashion.  There isn't even a mechanisim for performing set operations in COBAL.  The comparison is ridiculous.

    Regardless of that silly post, I AM looking forward to CLR integration.  I have had to write complex analysis functions in the past.  I wished for the day I wouldnt have to move large datasets into the client for processing.  Furthermore, I envision CLR integration bringing NOT the Logic Layer to the DB, but the Data Access Layer.  Using TSQL to hydrate datatables or custom business objects, and then passing these complete objects back to the caller via webservice would go a long way to help build an SOA based enterprise.  Developers could finally get objects out of the DB rather than untyped result sets. Just a thought...



  • Given that I develop currently in SQL Server (over 9 years now) including its predecessor Sybase and even spent a couple years on PL/SQL.  I am well aware of what TSQL is and is not. 

    That said, my comparisons are NOT language specific, but historical.  People rooted in the belief that TSQL will stay around IMO are using the same mentality used by COBOL programmers a decade ago.  COBOL fell fast when new tools came on the scene and I predict that TSQL will do the same.  It will go slow at first, but then the conversion from an older not so well built language like TSQL to a modern language will happen quickly. 

    I loath COBOL (save for the evaluate statement) as a language as it was restrictive and limited just like TSQL.  If you needed to do something out of the box, you had to take the long way around to solve it.  CLR will open up the database.  In some cases this will be bad, but for the most part it will be a wonderful addition to a developers arsenal.

    Personally, I know many DBAs and all are looking forward to ditching TSQL.  It has its place, but in truth when trying to do more delicate coding it is a pain in the rear.  Personally, I welcome the flexibility and power that a programming language brings to the SQL architecture rather than fear it.

  • I'd agree that MySQL and others will start to integrate other languages into the RDBMS engines, but I don't think SQL will die. For what it can be used for, manipulating large sets of data, it doesn't appear that any of the OOP or procedural languages to as clean a job. Personally I like T-SQL for set based things, hate it for stringmanipulation, complex business logic, etc.

    Somethings are better suited for the db, some are not. The CLR in the database is not a great idea. I understand the function writing, and that could really help in analysis or string type functions, but I'm not sold that writing general ad-hoc stored procedures is a good idea. The database is a limited resource. Better to move these things out to other boxes that aren't limited.

  • I agree queries will still be run, but that is what they will be... queries.  The logic I think will eventually (it will take years) be in a more progressive language such as C#.

  • What caught my attention in the article was the issue of where to place the business logic layer. This is somehting where I think Microsoft has misled their community by focusing on three layers in the n-tier approach, the name implies how we should look at it but way to often the focus has been an absolute three.

    The way I see it shops are going to go to their strength and create busienss layers where they make the most sense. The important idea is to create clear boundaries between the layers and rules for how the layers interact. If that means the dataset is cleand up before handing it off to the developers then so be it.

    Everett Wilson

  • I have worked on several n-tier apps and the logic has always ended up where it makes the most sense.  Everyone tries to keep it in the middle-tier, but often for performance reasons it ends up in a stored procedure or on the client side before it is posted to the server especially when running apps over the internet where latency is an issue.

    I would guess at this point MS would rather you just shove it in the DB.  Mainly because once it is there, you are tied to SQL Server.  As a developer, middle tier is usually best because it gives you long term flexibility.  However, design needs often push logic into places where we would rather they not be. 

    I wouldn't be suprised in the future if business objects move around between systems (database, app servers, etc) based on performance and other indicators.  Will a developer really care where something runs when he accesses the XYZ object on a server from the client.  It would be nice to setup a machine or machines as a business cluster and let the OS figure out the most optimal place to run business objects.  Sure would give a lot more power to grid computing.  Have a performance problem server side?  Slap another server in, run a quick config to add it to the grid and database/business tier performance would go up universally.  It never is that easy, but who knows what the future will bring.

  • I have yet to see a good example of what can be done with the SQLCLR that can't be accomplished with some T-SQL savvy.

    I think the idea of the CLR in SQL Server was one of those ideas someone brings up in a staff meeting, sounds great to management, and sounds even better to marketing.

    In addition, I believe the SQLCLR is a gross violation of the DRY principle.

    What's next? - the SMSCLR, the VSCLR, the POWERPOINTCLR, the COM+CLR, *8,.,

    While a developer myself, I have never blamed a DBA for being territorial about the Back End. However, since technology drives new features; and vendors decide how they implement them, chances are, in the end, we will be puting up with crap in the Back End (no pun intended =).


  • Straegen, I believe that you recieved the responses you did because your first few posts indicated that you believed that T-SQL was headed for the grave in it's entirety and rightfully so.  In the lats post you somehwat contreadicted your early posts by stating

    'I agree queries will still be run, but that is what they will be... queries'

    I'm not so sure that the rest of us understand exactly what your stance is, I know I don't and maybe it's just me.  In one post you say that it's time for T-SQL to head towards the grave and in another you state that it will still be used for queries.  So what exactly is your stance on this issue? Are you saying that it's time for T-SQL to disappear from the face of the earth or are you saying that it's time for T-SQL to be used strinctly for working directly with relational data?




    Kindest Regards,

    Just say No to Facebook!
  • Francisco,

    I haven't seen may good cases, but a few interesting ones were check constraints on tables. Trying to be sure that an IP address was valid, or that a phone number conformed to a valid format (either hyphens or dots), etc. These types of string manipulations are very complex in T-SQL and trivial in C# or VB.NET. I also suspect that some financial or complex Analysis services rollups will be much easier in a CLR languange than T-SQL or MDX.

  • No contridiction from my viewpoint.  SQL and T-SQL aren't the same thing.  I don't think SQL is going anywhere since it does what it does quite well, but the way we access and manage the results from queries, loops, variables, error handling, transactions, etc (i.e. T-SQL) will likely shift to CLR in the long term.  If you haven't programmed C# or, you will likely fall in love with them once you see how much easier data manipulation can be in a language that was designed for it.

    I don't have a problem with someone disagreeing with me since my points are only speculation, but that first poster who called me silly either didn't read my post well and missed the point or has a screw loose.  I am not being irrational.  My point is that anyone who uses both TSQL and some other next gen language will tell you that TSQL is weak in many areas and it is time to get something better.  It isn't marketing or some spitball idea (at least not completely).  Programmers have been asking for a real language in SQL for quite a while.

    The original author of the story made it sound like CLR wasn't "all bad" and I believe that CLR integration is great.  That is where our points divulge.  My additional thoughts about TSQL going the way of dino are just thoughts that reminded me of the old COBOL switch (back when more lines of code were written in COBOL than any other language).

  • This gets back to my COBOL arguement.  COBOL could do almost all the things that next gen languages could do, but it wasn't adept at it.  I believe T-SQL suffers from the same problem.  Complex loops, variable management, inline procedures, ability to create and manage objects can all be done or simulated in T-SQL.  It is just easier to do in other next gen languages.

    This does get into another discussion flying around.  When I refer to SQL, I am referring to the set based SELECT, UPDATE, INSERT, DELETE statements.  When I refer to T-SQL being replaced by CLR, I am referring to the logical part of SQL such as flow control, variables, etc that normally reside in stored procedures.  I know they are the same animal inside SQL Server and that seems to be leading to some confusion. 

Viewing 15 posts - 1 through 15 (of 64 total)

You must be logged in to reply to this topic. Login to reply