If you know anything about storing meaningful data for compact (mobile) applications, you’ve probably looked at SQL Server Compact Edition or SQL Server Mobile Edition (2005) in the past.
You also probably disregarded previous versions due to their inherent restrictions or lack of support for very mainstream SQL concepts and functionality. Enter SQL Server Compact Edition 3.5.
Despite the baffling return to the original naming "Compact Edition" or CE, SQL Server Compact Edition is almost upon us and not a moment too soon. When SQL Server CE goes released to manufacturing (RTM) sometime in late August/September (dates not set yet) we will get a far more mature database for compact devices (and desktops).
Unfortunately we will not see support for Stored Procedures (which I feel is a major failing) but we do get some very important changes. Since the changes are rather large, I thought I would publish what is listed in the RC0 version of SQL Books Online for SQL Server 2008:
SQL Server Compact 3.5
SQL Server Compact version 3.5 was released with Visual Studio 2008. Starting with the SQL Server Compact 3.5 release:
- SQL Server Compact supports local transaction scope on desktop computers.
- The SQL Server Compact Table Designer in Visual Studio 2008 has been enhanced to provide a user interface for creating primary key and foreign key relationships between tables.
- SQL Server Compact now supports the following Transact-SQL statements:
- Nested queries in a FROM clause
- CROSS APPLY and OUTER APPLY
- CAST
- TOP
- SET IDENTITY INSERT
- SQL Server Compact 3.5–based applications can be developed for desktop computers by using Visual Basic 2008 Express Edition and Visual C# 2008 Express Edition.
- You can administer a SQL Server Compact database stored on a smart device or on a desktop computer by using SQL Server Management Studio Express (SSMSE) in SQL Server 2008.
- SQL Server Compact implements the timestamp (rowversion) data type.
- SQL Server Compact supports LINQ to SQL. LINQ to SQL is a component of the language integrated query (LINQ) project. It provides a run-time infrastructure for managing relational data as objects without giving up the ability to query. It translates language-integrated queries into Transact-SQL for execution by SQL Server Compact, and then translates the tabular results back into the objects as defined by the application developer.
Source: SQL Server 2008 Books Online
There are some great inclusions in this release. Some of the important inclusions to single out are:
- Timestamp column type – allows optimistic concurrency patterns
- Proper admin support through SQL Management Studio
- Support for LINQ to SQL (which is pretty imperative given the lack of Stored Proc support).
- Obviously, the T-SQL statements. This undoubtedly reflects improvements in memory management
The question is: when will we see the great results that are possible with SQL Server Compact Edition? Hopefully soon!
Hopefully database synchronization has been fixed too – my experience with prior versions left me with the impression that synching a mobile database with a full-strength SQL Server database was the biggest headache of all.
If I get any spare time soon, I’ll try to investigate this further.
