CODEWITHSUNDEEP
sql-servert-sqlstored-proceduresclr
Sako73
Sako73

Reputation: 10137

CLR Stored Procedure v regular SQL Stored Procedure

I have been writing a CLR stored procedure that moves data from one database to another. I went with the CLR stored procedure because I like the .NET framework's ability to connect to remote servers better than I like linked servers, or openrowset, but I now find that my class is mostly embedded SQL strings. I was considering just using the CLR stored procedures to retrieve the data onto the local SQL Server, and then using a regular SQL stored procedure for the actual inserts and updates.

I'm not worried about pre-compilation of the procedure or performance, and I do like that the CLR procedure allows me to see all of the logic in one place, read from top to bottom.

Are there any reasons I should consider moving to a TSQL solution instead of CLR?

Thanks.

Upvotes: 0

Views: 216

Answers (1)

Pieter
Pieter

Reputation: 420

There are multiple reasons why you would stick to a regular stored procedure. I'll try to give you an overview of the ones that I know of:

  • Performance.
  • Memory issues. SQL Server only operates with its own max memory settings. CLR's go out of this bound. This could comprimise other applications (and the OS) running on this server.
  • Updatebility. You can update a Stored procedure with a simple script. CLR's are more complicated to update
  • Security. CLR's often require more security settings than regular t-sql.

As a general rule you only want to use CLR for:

  1. interaction with the OS, such as reading from a file or dropping a message in MSMQ
  2. performing complex calculations, especially when you already have the code written in a .NET language to do the calculation.

Upvotes: 1

Related Questions