An all too familiar challenge of today’s applications is to support the mobile user.  A mobile user is not always guaranteed to have a connection to the home office.  They need an application that can support both offline and online connection states.  An application that is occasionally connected is often referred to as an “Occasionally Connected Application” (OCA).  OCAs will often work offline and when brought online will execute synchronization processes.  In this blog, I’ll briefly introduce some nice features included with SQL Server 2008 Change Tracking.

SQL Server has had replication features for quite some time now.  I’m by no means an expert when it comes to SQL Server Transactional replication, but personally I’ve found it difficult to setup, troubleshoot, customize, and unable to easily communicate across N-Tier application environments.  In the past, the next option from replication was to create our own SQL synchronization engines utilizing triggers, timestamp columns, tombstone tables, etc to track database changes on the client and server and then synchronize up the differences.  There are some problems with this solution.  First glaring issue to me is the use of triggers.  Triggers cause transactions to take longer to commit and cause blocking issues.  So basically performance and storage issues result from this solution prior to SQL 2008 Change Tracking.  SQL 2008 now provides a new feature which is available in all versions: SQL Change Tracking. 

SQL Server Change Tracking Advantages/Features:

1)  Easy to Setup.  It does not require timestamp columns, tombstone tables, triggers, etc.  You can simply script or use SQL Management Studio to turn on SQL Change Tracking for a database and then what tables you want to track changes on.

2)  Better Performance.  Changes are tracked at commit time rather than when DML operations occur.  What this basically means is transactions run quicker and this also helps with blocking issues.

3)  Minimal Disk Space Costs.  Change Tracking stores changes in SQL system tables and the disk space cost is minimal.

4)  Integrates with the .NET Sync Framework.  If you are using the .NET Sync Framework to develop your OCA, then .NET/Visual Studio has some nice features that are at the developer’s disposal.

5)  Synchronize with other DB Platforms.  SQL Change Tracking runs independently from other databases.  So if your server is running an Oracle DB and you want to run SQL Compact on your client with Change Tracking enabled, it will support that just fine.  Changes on the client are tracked independently of the server and obviously vice versa.

6)  Packaged Functions.  SQL Server comes with packaged functions that are used to query the SQL Change Tracking system tables to acquire incremental changes.

7)  Auto Clean Up.  When SQL Change Tracking is enabled for a database, you can specify when change history will be purged automatically for you.

8)  Column or Entire Row.  You can enable change tracking to record that an entire row/record had something changed in it or you can even track down to what column was changed to limit the amount of data changes returned when querying for incremental changes.

Example 1 - Turn on SQL Change Tracking for a Database:

   1:  ALTER DATABASE [AdventureWorks2008] SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 365 DAYS, AUTO_CLEANUP = ON);

   2:  GO

Example 2 – Turn on SQL Change Tracking for a Table:

   1:  ALTER TABLE HumanResources.[Department] ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = OFF);

   2:  GO

Example 3 – Return Incremental Table Inserts:

   1:  DECLARE @sync_last_received_anchor BIGINT, @sync_new_received_anchor BIGINT;

   2:   

   3:  SELECT @sync_last_received_anchor = CHANGE_TRACKING_CURRENT_VERSION();

   4:   

   5:  INSERT INTO [HumanResources].[Department] (Name, GroupName, ModifiedDate) VALUES ('Test1', 'My Group', GETDATE())

   6:  INSERT INTO [HumanResources].[Department] (Name, GroupName, ModifiedDate) VALUES ('Test2', 'My Group', GETDATE())

   7:  INSERT INTO [HumanResources].[Department] (Name, GroupName, ModifiedDate) VALUES ('Test3', 'My Group', GETDATE())

   8:   

   9:  SELECT @sync_new_received_anchor = CHANGE_TRACKING_CURRENT_VERSION();

  10:   

  11:  --return inserts

  12:  SELECT dept.*

  13:  FROM [HumanResources].[Department] AS dept 

  14:      INNER JOIN CHANGETABLE(CHANGES [HumanResources].[Department], @sync_last_received_anchor) CT ON CT.[DepartmentID] = dept.[DepartmentID]

  15:  WHERE (CT.SYS_CHANGE_OPERATION = 'I' AND CT.SYS_CHANGE_CREATION_VERSION <= @sync_new_received_anchor)

If you’ve ever tried to run a collection of SQL batches via the .NET DbCommand.ExecuteNonQuery method, you probably received an error similar to the following: “Incorrect Syntax near ‘GO’ …”.  DbCommand.ExecuteNonQuery is intended to execute a single SQL batch statement against a database.  The “GO” separator is a command that is used/understood by SQL Server Query Analyzer and Query Editor utilities to separate out SQL batches when ran.

There has been some workarounds that I’ve seen that involve regular expressions and string manipulation/splitting to break out the script with “GO” separators into a collection of SQL statements and run them each individually.  This can work, but you will run into situations where commented areas will contain the “GO” statement and other problems can/will arise.  Such situations would cause the workaround to fail.

The proper way to run scripts with the “GO” separator is to employ SQL Server Management Objects (SMOs).  Microsoft’s SQL Server 2008 Feature Pack contains links to download/install the SMO assemblies (.NET Framework object model) .  Developers can use SMOs to perform SQL Server management routines through their .NET application code.

Steps to Use SMOs:

1) Download and Install SMOs - Download the SQL Server Management Objects found on this page.  Run the installation package.  If you are running SQL Server 2005, you may need to search for the 2005 version as the link I gave is for 2008.

2) Add references - After the SMOs are installed,  we’ll need to add references to them in our project.  The SMO assemblies should appear under your .NET tab when adding references to your project.  If you can’t  find them there, the assemblies were installed to “C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies” for me.  To run scripts with “GO” statement, we’ll need references to the following DLLs:
  a) Microsoft.SqlServer.ConnectionInfo.dll
  b) Microsoft.SqlServer.Management.Sdk.Sfc.dll
  c)  Microsoft.SqlServer.Smo.dll

3) Import the required namespaces and execute your SQL script with “GO” statements like below:

   1:  Imports System.Data.SqlClient

   2:  Imports Microsoft.SqlServer.Management.Smo

   3:  Imports Microsoft.SqlServer.Management.Common

   4:   

   5:  Class Window1

   6:   

   7:      Public Sub ExecuteSqlStatements(ByVal connectionString As String, ByVal sqlStatements As String)

   8:          If Not String.IsNullOrEmpty(sqlStatements) Then

   9:              Using mySqlConnection As SqlConnection = New SqlConnection(connectionString)

  10:                  Dim mySqlServer As Server = New Server(New ServerConnection(mySqlConnection))

  11:                  mySqlServer.ConnectionContext.ExecuteNonQuery(sqlStatements)

  12:              End Using

  13:          End If

  14:      End Sub

  15:   

  16:  End Class

SqlBatchExecNonQuery_Soln.zip (965.09 kb)

Microsoft SQL 2005 introduced a new construct called CTEs (common table expressions).  CTEs provide a way to break down complex queries into simpler chunks and offer more readable queries.  UDFs, derived tables, and temp tables are all constructs that can be employed to help break up complex queries, however they often muddy up your SQL script.  They also exist beyond the context of the one SQL statement you may have desired to use it for.  CTEs also provide the ability for recursion without the need for recursive stored procedures.

Therefore, at a high level CTEs are used for:

1) Improving readability of your SQL scripts.

2)  Simplifying complex queries.

3)  Recursion.

A Simple CTE Example:

   1:  WITH OrderTotals (OrderID, OrderTotal) AS (

   2:      SELECT OrderDetails.OrderID, SUM(((OrderDetails.UnitPrice * OrderDetails.Quantity) - OrderDetails.Discount)) AS OrderTotal 

   3:      FROM dbo.[Order Details] AS OrderDetails WITH (NOLOCK)

   4:      GROUP BY OrderDetails.OrderID

   5:  )

   6:  SELECT * FROM dbo.Orders WITH (NOLOCK)

   7:      INNER JOIN OrderTotals ON OrderTotals.OrderID = Orders.OrderID

   8:  WHERE OrderTotals.OrderTotal > 100

In this first simple example, I used the Northwind database to find all orders which exceeded $100.  The first step is to use the keyword WITH and then name my CTE, define my columns, then enclose what SQL makes up the CTE within parenthesis.  I then proceed to use the CTE in the final query.  Notice how the final query is much simpler to read without the GROUP BY, SUM, etc logic in the CTE.

Two CTEs Example:

   1:  WITH OrderTotals (OrderID, OrderTotal) AS (

   2:      SELECT OrderDetails.OrderID, SUM(((OrderDetails.UnitPrice * OrderDetails.Quantity) - OrderDetails.Discount)) AS OrderTotal 

   3:      FROM dbo.[Order Details] AS OrderDetails WITH (NOLOCK)

   4:      GROUP BY OrderDetails.OrderID

   5:  ), OrdersMoreThan100 (OrderID, OrderTotal) AS (

   6:      SELECT OrderTotals.* FROM OrderTotals WHERE OrderTotals.OrderTotal > 100

   7:  )

   8:  SELECT * FROM dbo.Orders WITH (NOLOCK)

   9:      INNER JOIN OrdersMoreThan100 ON OrdersMoreThan100.OrderID = Orders.OrderID;

In this example, we get a little more complex with our CTEs.  Here we can see that you can comma delimit as many CTEs as you want.  I also reference the first CTE within my second CTE and simplify our final SQL statement even further.

CTE Gotchas/Notes:

1) Always terminate SQL with a “;” before you declare a CTE.

2) You can use CTEs with SELECT, UPDATE, INSERT, DELETE queries.

3) CTEs can only be used by the final SQL statement (the statement following the CTE definitions).  Subsequent SQL queries will not be able to use your CTE definitions.