Posted on

TSQL vs CLR (Common Language Runtime) stored procedures

The traditional method for scripting the SQL Server database was to use T-SQL.

The T-SQL approach is the same as Oracle PL/SQL or PostgreSQL’s Pl/pgSql.

In later versions of SQL Server Microsoft enabled the creation of CLR stored procedures which are .NET Assemblies written using .NET languages like C#. These Assemblies are deployed into the database and accessed by T-SQL function/procedures wrappers.

This is similar to the way Oracle allows developers to create Java Stored Procedures which are deployed into Java Virtual Machine that runs inside the database; these procedures are accessed by PL/SQL function/procedure wrappers (as in T-SQL).

Other database vendors do similar things.

CLR Advantages and Disadvantages.

There are many benefits for developing CLR stored procedures some of which include the ability to use modern development tools such as Visual Studio.

However a downside is the need to deploy into the database, which in enterprise environments involves seeking the relevant permissions to deploy and support (multiple database version) from database administrators (DBA: You want to deploy that in my database!! Never mind that it is Open Source!!!); additional (T-SQL) wrappers are needed to access the .NET assembly. bug identification, correction and re-deployment can be a long winded process.

T-SQL Advantages and Disadvantages.

The benefits for T-SQL are that it has been around since the database was created with probably millions of developers all around the world being able to create and modify the code. The code is also open in that any user can access the source code for a function and modify it. Additionally while T-SQL also must be deployed into the database it does not require two installs: the .NET assembly and the stored procedure.

Downsides include the fact that a DBA is still required in an enterprise deployment but there are less dependencies that with deploying a .NET Assembly and the T-SQL wrappers;
And that T-SQL is not the most elegant and modern of languages.

Why T-SQL.

While I have chosen to develop in both PL/SQL (Packages/Objects) and Java (SC4O) for Oracle my preference is to create PL/SQL Objects. SC4O works really well, but there are very few situations where SC4O has been deployed into a production database instance.

While I can program in .NET, I chose to develop the SQL Server Spatial functions available on this site using T-SQL; this decision is a reflection on experience of developing for Oracle.