Quite often people need to write a new T-SQL application to replace an existing one for various reasons. For instance, Existing report is generated by a complex T-SQL procedure which pulls data from OLTP directly. The performance of the procedre is poor since everything is calculated while report is running. Company decided to pre-calculate and save the result in a denormalized table, and the report runs off that denormalized table. Obviously, the original code need to be re-written. Let’s say you are new to the company. Before starting your work, you may want to know what databases, tables, and columns are referenced by the procedure. It will not be hard if the procedure code block only has few lines of code. However, if the procedure includes few or tens thousand lines, accomplishing such work might takes days. The worth thing is you might miss some refereces and finally you might abandon your work result at the end due to the inaccuracy.
I run into a bug recently with SQL Server 2012. I believe this exists in SQL Server 2008 as well. When a TVP is passed to a SQL function/procedure/script through an CLR function, the second execution will cause error which will be written to SQL Server log — Process x generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process. It has been filed to Microsoft Connect. If you think it’s important, please visit this bug on Microsoft Connect and vote it as important. Please download the code from here.
Expression class under System.Linq.Expressions is a very useful class which includes many static members helping you to create expression tree and execute the tree. Whenever you need to execute a dynamically generated logic in SQL Server through an CLR, you might consider using the classes in Expressions name space. In this post, I am going to give you an very simple example to explain how to create and execute an expression tree.
After SQL Client of SQL Server 2012 is installed to the client computer which also have Visual Studio 2010 installed, I am excited about deploying CLR with .NET 4.0. I launched Visual studio, started a Visual C# CLR database Project, created a simple CLR project, deploy, and then failed. Tried again, and failed again…
Functions are the most restrictive code blocks you can define in SQL Server. They perform just like stored procedures in which it can return set and output parameters. But you cannot create or modify objects and the data within the object in a function except the objects are table variables and table value type variables. Actually any transaction depended objects are not allowed to run in a function, such as
RECEIVE. You can run
exec proc within a function but only some extended procedures are allowed. One is not allowed but most developers(including me) desire is to run sp_executesql in a function. You can get around it by creating CLR functions.