0

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.

Improve this question

1 Answer 1

Reset to default
1

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.
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.