Come and join SQL PASS Victoria User Group in Victoria, BC and let’s talk about SQL Server. This will be a great opportunity to improve your SQL skills by learning from others and build your professional network. Meetings will be held 3rd or 4th Thursday every month at campus of Camosun College in Victoria, BC. Membership and attendance of sessions are absolutely free. The schedule for each meeting will be
5:00PM – 6:00PM – Networking.
6:00PM – 8:30PM – Presentation(s)
8:30PM – 9:00PM – Door prizes and Question & Answer session
The first meeting will be on Oct 18, 2012. Visit http://victoria.sqlpass.org/ for more details.
Get Dependent Databases, Tables, Columns…
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.
Continue reading “Get Dependent Databases, Tables, Columns…”
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.
Continue reading “Bug? Can’t Pass TVP to SQL Function Twice”
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.
Use Expression in CLR
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.
Collect Changed Data (1) – Overview
Changed data collecting/capturing is a topic frequently discussed as part of the design of auditing and ETL systems. In auditing system, changed data collection processes are usually just build on the system to be audited. In very few cases, the system needs to audit over the collected historical data, But this is not the case in ETL system. Especially in complex ETL systems, in order to get the desired data shape at target, data from different sources need to be processed into a series of format and saved in the staging area and one or more level posted processes might also be needed.
Collation Of Temp Tables
Temp tables are used frequently while coding. It might cause issues when the collation of user database is different from system default collation. The message you will see is
Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “Latin1_General_CS_AS” in the equal to operation.
Great Place to Learn SQL Server 2012
Here is the place I found for the people who are willing to learn new features of SQL Server 2012.
http://blogs.msdn.com/b/oneclickbi/archive/2011/12/27/more-demos-of-power-view-available.aspx
Deploy CLR in SQL Server 2012
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…