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…
Month: April 2012
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?
Installing Online Document for SQL Server 2012
Prior SQL Server 2012, you have the option to install Book Online for SQL Server. In SQl Server 2012, the option is still there, however, selecting this option will only install the document viewer rather than the library. You will have to take more steps to have the document downloaded. Here are the steps you … Read more
Transactions, Chatty or Chunky?
Chatty or Chunky? What do you mean? Running code block with a transaction can ensure the atomicity of the code, all done or all undone. In defult programming mode, select statement will never start a transaction automatically. Data modification language, such insert, delete, update, merge, send, receive, etc, will automatically start a transaction as the command starts if there isn’t any transactions and will commit
automatically (if there isn’t any transactions). I call this kind of strategy Chatty.
@@DBTS vs MIN_ACTIVE_ROWVERSION
Both @@DBTS and Min_active_RowVersion() are used to get the current Row Version in a database. Row version, is also called timestamp formerly, is an unsigned bigint data type of a column stored and presented as a binary(8). This data type is like a identity value of a table in which every table can only have one RowVersion column and the value of the row version is managed by SQL Server rather than uses, it’s read-only. When a new record is inserted into a table with RowVersion column, a row version will be assigned to the row. When update happens to the table, the row version of updated row will be increased. The values of the row version from tables within a database is always unique.
Name MCSE is Back
New certifications are available very soon. They are MCSA – Microsoft Certified Solution Associate, MCSE – Microsoft Certified Solution Expert, MCSM – Microsoft Certified Solution Master, and MCA – Microsoft Certified Architect (stay the same). Overview: https://www.microsoft.com/learning/en/us/certification/cert-overview.aspx Overview SQL: http://www.microsoft.com/learning/en/us/certification/cert-sql-server.aspx
Change Default DML Behavior
DML, Data Manipulation Language, is used to add data to table and modify existing rows in tables. There are 3 commands
- Insert : insert records to a table
- Delete: remove records to a table
- Update: modify records in a table
In SQL Server, you are allowed to change the default behaviors of those 3 commands. For instance, while inserting a record, the new record can be applied to a table as an update(can be a delete as well).