SQL Server Compact 4.0 CTP1 released

The SQL Server Compact team has just released of CTP of version 4.0 of SQL Server Compact Edition.

Ambrish Mishra has a detailed blog about the new features. Here is a short summary:

ASP.NET support

The main feature of version 4.0 is support for using SQL Compact as a file based database engine with ASP.NET websites.

The work done to enable ASP.NET includes:

Higher Reliability – can handle load of starter websites

Setup Enhancements – easier private deployment and a single x64 installer.

Support for ASP.NET – removal of the ASP.NET blocker

Virtual Memory Reduction – allows up to 256 concurrent connections (max) without virtual memory errors.

Medium Trust – enables web hosting with .NET 4.0

It is a part of the recently announced WebMatrix tool for starter websites as a starter database engine, which also includes a tool to design the database used on the website:

T-SQL changes

Support for paging queries:

SELECT * FROM Customers ORDER BY [Customer ID] OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

ADO.NET Provider enhancements

Seamless Integration with ADO.NET Entity Framework 4.0 (.NET FX 4) – server generated keys and code first support coming soon.

System.Data.SqlServerCe.SqlCeConnection.GetSchema() – allows you to get schema information for use in scripting tools etc.

System.Data.SqlServerCe.SqlCeConnectionStringBuilder() –allows you to build a strongly typed connection string.

Migration

The WebMatrix tools includes a tool to migrate a SQL Compact database to SQL Server 2006/2088/Azure. The tool does not generate a script, but simply creates a new database on the server selected.

For migration from SQL Server to SQL Compact, (and script based migration from SQL Compact to SQL Server), you can continue to use my ExportSqlCe tools. I have described the procedure to migrate from SQL Server to SQL Server Compact here. In addition, I have just released SQL Server Compact 4.0 versions of ExportSqlCe and SqlCeCmd.

Visual Studio

Later, a Visual Studio update will enable you to use SQL Server Compact from the Visual Studio Data/Server Explorer, including from Visual Web Developer Express. I assume a similar update for SQL Server Management Studio (SSMS) is not in the cards.