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://social.technet.microsoft.com/wiki/contents/articles/6982.sql-server-2012-developer-training-kit-bom-en-us.aspx#Module_4_SQL_Server_2012_Database_Server_Programmability 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…
Row Level Security (3)
In my last post, Row Level Security (2) , I talked about how to improve the performance and prevent records from being removed from the view after it’s modified. The issue was arisen in last post is how to define default values to RoleID, which is also the topic of this post.
Row Level Security (2)
In my last post, I talked about the concept of row level security impelementation. Performance issue will gradually arise while number of rows in MyData increases. This is because is_member function evaluate every row in MyData table and check whether it’s the row should be returned. In order to make index becomes Search-able, we will have to change the structure of the view.
Vancouver Tech Fest 2012 is Coming – April 28, 2012
Vancouver Tech Fest 2012 is Coming – April 28, 2012 It will be a great place to learn different programming technologies. I will be presenting Locking and Concurrency Considerations in DB Design. Concurrency is one of the most important things to be seriously considered while designing a complex database system. Fully understanding different level of … Read more
Row Level Security (1)
In bigger organizations, the data in a table might be sensitive to few departments but not the others. Some people may need to rows from the table that belongs to few departments where another group of people may need to access the data belongs to another few departments in one table. Those 2 groups of people might share the data from one or few departments. Implementing such logics is not a big deal for customized applications, for instance use procedures to filter rows out. What if users access data using very genaric tools, such as SSMS, in which they can arbitrarily issue queries against table. How would you selectively return rows from a table without asking uses putting filters in their queries?