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.
Month: June 2012
Locking Behavior – Foreign Keys
With Snapshot (read committed snapshot and snapshot isolation level) enabled, when data reading on a table takes place, if any records are being modified at the same time, the data will be read from the rows in version store rather than wait until data modification complete. Such behavior lets prgrammers think that reader process will NEVER ever be blocked by writer process. In most of the scenario, this is correct, however, when reader process is indirectly issued and running against a table, the isolation level for that reader process is always read committed lock isolation regardless the transacitoin isolation level of the parent statement.
Bug? Can’t Pass TVP to SQL Function Twice
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.
Microsoft Roslyn Rocks
Recently I was looking for a solution for dynamic code compiling. I came cross Microsoft Roslyn and had a test. It’s perfect solution for dynamic code compilation and execution. You will have to have .net 4.0, Visual Studio 2010 sp1, Visual Studio 2010 SDK sp1, and Microsoft Roslyn installed in order to make this happen.