CLR or TSQL - hard to decide for me in SQL 2005
Honestly I have had such decision time to choose whether to go to CLR (Common Language Runtime) or TSQL (Transact-SQL) to design on one aspect of component design at our place.
Obviously TSQL was first choice as I have been experienced mostly, but don't want to miss the advantages in using CLR, with TSQL fundamentals its easy to achieve as it is a base for other programming languages such as Visual Basic. Well, with SQL 2005 & CLR the power is at higher-level to do more with stored procedures & assemblies and having knowledge of .NET is a bonus. From the documentation I feel like it’s just another part of search to make things easier and more familiar to administrators and programmers when developing and managing projects with SQL 2005. I have read somewhere or (had a little discussion with one of the MS dev. team) that most important reason to promote CLR stored procedures is security! The explanation I have got was stored procedures written with .NET managed code are not only type safe but also more protected from unwanted code manipulation. I have seen such instances where the CLR SPs are equipped with good performance-wise as T-SQL stored procedures are.
So, I begin to learn more about CLR & its exsitence in SQL 2005, out of curiousity. As usual my internal friend BOL gave me a direction to get first hand information on CLR, phew. You can create, modify, and drop database objects that reference common language runtime modules; however, you cannot execute these references in SQL Server until you enable the clr enabled option. To enable the option, use sp_configure. Also I would like to share few blogs on web that will get you more in-hand help about CLR:
CLR_IntegrationTeam blog by MSDN team (well I haven't seen any post there after Nov.2006)
SAMS article on CLR by Jesse Smith
Learning-Steps in writing CLR procedures in SQL 2005
SimpleTalk-CLR with a example on simple benchmark between CLR & TSQL
Assemblies introduction in SQL 2005 for CLR
From my journey path on CLR I have learned that:
- CLR enables a set of rich modern programming languages such as C#, VB.NET, native C++.
- Also integration with APIs such as.NET framework library
- Built-in classes with tools such as Visual Studio.NET to write programming logic in the database.
- Specifically, you will be able to write computation-intensive logic that is faster in CLR than in T-SQL.
- The advantage of the .NET framework APIs to solve some common programming tasks.
- You need Visual Studio 2005 to develop such assemblies or code.
- It is a bridge to fillup the gap between db programming and application programming (DBA & Developers)
CLR has internal core technology that works as a managed code for extension mechanism, this will have advantage of some system stored procedures written in managed code, and some of the technologies layered on top of the core, such as http support, make use of it as well.
So what for you are waiting, take advantage of CLR in SQL Server 2005.