John H

Apr 232012

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.

Continue reading »

Apr 212012

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 concurrency control is the key to make your design success. Most of SQL Server internal concurrency controls are implemented through locks. We can utilized the same mechanism to customize the concurrency control for your processes. I am going to cover all of these during the session. At the end, we will talk about concurrency control with combination of different components of SQL Server.

Apr 202012

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?

Continue reading »

Apr 182012

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 can take to install the library.

By Default, libraries will be downloaded and installed in C drive. If this is not the location you want, you may want to follow steps here to change the path.

  1. Open SSMS, go to Help, and select Manage Help Settings. Help Library Manager will be opened
  2. Choose Install Content From On Line. The Help Library Manager will retrieve available libraries online.
  3. Scroll down to SQL Server 2012 and click on “add” for items Books OnLine, Developer Reference, and Installation.
  4. Click on Update.
Apr 162012

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.

Continue reading »

Apr 132012

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.

Continue reading »

Apr 112012

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 SQL:

Apr 112012

DML, Data Manipulation Language, is used to add data to table and modify existing rows in tables. There are 3 commands

  1. Insert : insert records to a table
  2. Delete: remove records to a table
  3. 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).

Continue reading »

Apr 092012

Stream Aggregate is the most efficient physical operator for value aggregation and generating distict rows. Stream here refers to record stream. Give you a typical example here. When you use SqlDataReader to retrieve reuslt from a query, the rows are read one by one by calling SqlDataReader.Read() method. You can say that you are streaming records from SQL Server to your client. If you perform aggregates in the stream, for instance, you are asked to count number of rows in the stream. In this example, you only need to increase the value with 1 to a variable in your application whenever Read() is involked with true returned. This is called Stream Aggregate.

Continue reading »