TFS Loses History when Branching/Merging

Both Microsoft Team Foundation Server (TFS) 2005 and 2008 appear lose your check-in comments and history when files a branched and merged back. Luckily someone has written a free plugin for the client that gathers all the history for a file and displays it in Visual studio.

Another way to get the full history is to install the TFS Power Tools from Microsoft and then use the history command with the /followbranches option.

Painting a Consistent Picture from Moving Data

Applies to: SQL Server 2005 and SQL Server 2008.

Introducing the Two types of Row Versioning

Row versioning was introduced in SQL Server 2005 which provides a way to implement a non-locking, non-blocking solution to getting a consistent picture of a moving database. This is useful to many applications which require an entirely consistent read of data such as an ETL or a reporting application.

Firstly to make this work, data needs to be committed in consistent blocks. So for example if you have an Order table and an OrderItem table, Order has a column called OrderItemCount – if the OrderItemCount is updated in one transaction and the OrderItem row is added in another transaction this is bad. You need to at least ensure that logical and consistent things are written to the database atomically within transactions.

There are two types of consistent picture which can be drawn using Row Versioning under SQL 2005 and above. Type 1 gives a consistent picture within a single query statement; Type 2 can give a consistent picture across multiple query statements.

To explain the difference between Type 1 and Type 2 a little more, look at this query:

SELECT OrderItemCount FROM Order WHERE OrderID = 100
SELECT COUNT (*) FROM OrderItem WHERE OrderID = 100

The above query is actually two statements which execute separately, but they should both always return the same value. Type 1 only guarantees a single statement is consistent. So it's possible for the data to change between statements, and you could be looking at a newer version of the data by the time the second statement runs (because an edit has been made to Order/OrderItem).

Type 2 makes sure that you are working off the same version of the data across all your statements. So even if an edit is committed to the database between statements, the statements will still be working off the old, but the same and consistent data.

Type 1 Row Versioning is called Read Committed Snapshot (READ_COMMITTED_SNAPSHOT) – This offers single statement level consistency
Type 2 Row Versioning is called Snapshot Isolation (SNAPSHOT_ISOLATION) – This offers multi-statement consistency

Implementation

Type 1 Row Versioning is very easy to implement. Simply run this statement once and no further implementation is required:

ALTER DATABASE <database> SET READ_COMMITTED_SNAPSHOT ON WITH <termination>

Type 2 Row Versioning needs a little more work. Firstly the following statement needs to be run once:

ALTER DATABASE <database> SET ALLOW_SNAPSHOT_ISOLATION ON;

It can take a while for the setting to come into effect, to know when it has taken effect check the snapshot_isolation_state column in sys.databases – when the value is '1' then the setting has taken effect.

Next, when querying the database and multi-statement (Type 2 row versioning) is required the following statement needs to be run on the connection:

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

Then, SQL Server needs to know which groups of statements need to all read a consistent version. This is done using a transaction (yes, this is correct, a transaction used when reading from the database). So our previous query would become:

SET TRANSACTION ISOLATION LEVEL SNAPSHOT
BEGIN TRAN -- Everything in this transaction will read the same version of the data.
SELECT OrderItemCount FROM Order WHERE OrderID = 100
SELECT COUNT (*) FROM OrderItem WHERE OrderID = 100
COMMIT TRAN

Finally

Row versioning can be used to resolve performance problems relating to locking and blocking, because data can be queried consistently (albeit perhaps slightly old data) whilst still allowing writes to the database. Having said this, a slight performance degrade could be experienced when querying a database which does not already suffer much from locking/blocking contention.

The old versioned rows are stored in tempdb, so this database will potentially grow in size. Also it should be placed on a disk partition with good performance.

See also: Micrsosoft White Paper

Dynamically Creating and Loading a SQL Server Compact Table

I needed to create a local SQL Server Compact (SQL CE) copy of a remote data source to work with, so I came up with some helper methods which will:

• Dynamically infer a CREATE TABLE statement for the local SQL CE database based upon a source schema (being one of; SQL Server, SQL Server Compact, or a DataTable).
• Load the newly created SQL CE table with data from the source.
• Convert between .NET types and SQL CE compatible types.

All this is encapsulated in some static methods in a class called SqlCeHelper.

Download Locations

• The SqlCeHelper class documentation can be found here
• The SqlCeHelper class source code can be found here

Code Example

The following code connects to a remote SQL server and runs a select statement. It will then use SqlCeHelper to create a local SQL CE table compatible with the results coming back from the select statement (correct column names, SQL CE data-types, correct column lengths, etc). Finally it will use SqlCeHelper to load the SQL CE table with the results from the local server.

private void button3_Click(object sender, EventArgs e)
{
// Open a Connection to our local SQL CE Database
string constring = "Data Source=Local.sdf";
SqlCeConnection ceCon = new SqlCeConnection(constring);
ceCon.Open();

// Open a connection to SQL Server
SqlConnection con = new SqlConnection(@"Data Source=MyRemoteServer;Initial Catalog=gib_dist;Integrated Security=True");
con.Open();

// Prepare to read some data in from our source SQL Server
SqlCommand cmd = new SqlCommand("SELECT * FROM MySourceTable", con);
using (SqlDataReader reader = cmd.ExecuteReader())
{
 // Get the schema of the result set coming from the source server
 DataTable schema = reader.GetSchemaTable();

 // Create the target table on our local SQL CE database
 string tableNameToInsertTo = "MyTargetCeTableName";
 string createTableText = SqlCeHelper.GetCreateTableStatement(tableNameToInsertTo, schema);
 SqlCeCommand createTableCommand = new SqlCeCommand(createTableText, ceCon);
 createTableCommand.ExecuteNonQuery();

 // Get a command ready which can insert data to our new SQL CE table from our
 // source reader.
 SqlCeCommand ceInsertCommand = SqlCeHelper.GenerateInsertCommand(ceCon, tableNameToInsertTo, schema);

 // Loop through each row and load the data into the SQL CE table
 while (reader.Read())
 {
     SqlCeHelper.SetSqlCeCommandParametersFromSqlReader(ceInsertCommand, reader);
     ceInsertCommand.ExecuteNonQuery();
 }
}
}