Transactions, Chatty or Chunky?

by

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.

Read more

@@DBTS vs MIN_ACTIVE_ROWVERSION

by

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.

Read more

Name MCSE is Back

by

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

by

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).

Read more

Query Plan (12) – Stream Aggregate

by

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.

Read more

Query Plan (11) – Hash (1)

by

In my last post, I described Hash Joins which is showing as Hash Match physical operator in the graphical execution plan. Another place hash match is used is for aggregation. When columns in group by clause do have have indexes or SQL Server cannot determine whether the rows are sorted or not, SQL Server will perform a Hash match to get aggregates. If distinct keywork appears on the select list, but there is no indexes on the selected columns, a hash match will be used as well. This operation is called hash aggregate.

Read more

Query Plan (10) – Hash

by

Hash is used in 2 most commonly seen physical operators, Hash Join and Hash Aggregate. Those 2 happen when there are no any other alternatives (merge, nested loop, or stream aggregate) which can be used for more efficient operation. For instance, when SQL Server joins 2 tables together but none of them has an index. SQL Server has no idea whether the joining keys are sorted or not.  In most of the case for such scenarios, hash join will take place.  As its name, hash join uses hash algorithm to encode the joining keys from both side, compares the hashed values, and produce the result. This sounds very complex — yes, it is a very heavy operator.

Read more

Proud of Being A MVP

by

This morning, as a routine of my regular day, I checked my emails and found a very special one which is from MVP Program. It says Dear John Huang Congratulations! We are pleased to present you with the 2012 Microsoft® MVP Award! This award is given to exceptional technical community leaders who actively share their … Read more

Modification Count of Statistics

by

When the size of a database is large or number of rows in a table become more, the built-in auto stats update usually can’t keep the statistics up-to-date. Out dated statistics can’t serve the query optimizer to generate better query planes. Consequentially, performance gets impacted negatively. Maintaining statistcs in a database is a one of the biggests challenge for DBAs. Because they need to know when the statistics updated last time and when they should perform stats update again. However, that’s not sufficient. They also need to know how many accumulated changes have not been reflected in the stats. It’s a simple task back to SQL Server 2000. They can just query column rowmodctr in sys.sysindexes. Unfortunately, this column is broken after SQL Server version 2000. It’s no longer accurate. Even worse, SQL Server 2005 and 2008 do not expose such information at all even though they have it internally.

Read more