CODEWITHSUNDEEP
c#sql-serverdatabasestored-procedures
Martin Larsson
Martin Larsson

Reputation: 720

Writing stored procedures in C# pros and cons

I just learned that you can write user defined functions in C# and use them as stored procedures in SQL Server.

Stored procedures that are written in managed code are called SQL Server Common Language Run-Time stored procedures or SQL CLR stored procedures

msdn - Walkthrough: Creating a Stored Procedure in Managed Code

It does not seem so widely used when Googeling it. Is there anybody familiar with this and can tell me the pros and cons of using SQL CLR stored procedures. When to use them and when not to? Perfomance etc.

I am a newbie to t-SQL and prefer writing code in C#.

marc_s answer to a similar question has some answers, is his opinion shared among all?

Stackoverflow - stored procedure in sql clr

Upvotes: 0

Views: 937

Answers (1)

Jodrell
Jodrell

Reputation: 35716

CLR extensions to SQL Server are good if you need your server to do something its not been programmed to do. Adding elegant string manipulation is an obvious example.

To use Managed Code in SQL Server you need to switch some server settings and upload and register your assemblies with the server.

If you want to do anything that effects external resources, you need to relax the security settings to allow this.

This makes deployment and maintenance more awkward and may raise security concerns.

Now, if you are developing a solution, its unlikey that you'd expect your users to interact directly with SQL Server. Probably, you have a Web Service or Site or Client Application that performs communication with the database. In my experience, its nearly always simpler and in fact desirable to make the other code in your solution do the things SQL Server is not good at.

Back in SQL Server 2005 when we didn't have Table Valued Parameters, I used to use a CLR funtion for splitting delimited strings into sets. This was faster than the TSQL alternative but, since 2008 I've found SQL Servers native functionality sufficient.

Simply put

Using Native TSQL for set based operations is always going to be better.

Using CLR to do something that is awkward or impossible in TSQL may be good but is probably best achieved in another tier of your solution.

Upvotes: 1

Related Questions