Features (SQL Server Compact)
SQL Server Compact 4.0 is a compact database that can be deployed on computers and embedded in ASP.NET Web applications. SQL Server Compact 4.0 supports the same SQL syntax and ADO.NET programming model as other editions of SQL Server. SQL Server Compact 4.0 can be deployed on computers either by using the desktop installer (SSCERuntime_x86-ENU.exe and SSCERuntime_x64-ENU.exe) or through Microsoft Visual Studio 2010 SP1.
Development support for SQL Server Compact 4.0 is provided by Visual Studio 2010 SP1 or Microsoft Visual Web Developer 2010 SP1 Express for desktop applications and Web applications. You can access SQL Server Compact databases on the desktop computer by using Visual Studio 2010 SP1 or Microsoft Visual Web Developer 2010 SP1 Express.
Some of the distinct features of SQL Server Compact are as follows:
Integration with Visual Studio
The application development support for SQL Server Compact 4.0 is provided in the Professional, Premium and Ultimate editions of Visual Studio 2010 SP1. In addition, SQL Server Compact 4.0-based ASP.NET Web applications can also be developed in the Visual Web Developer 2010 SP1 Express. The SQL Server Compact 4.0 design time components have to be installed from the Web for developing applications in Visual Studio 2010 SP1. The design time components available for download on the Web are Microsoft Visual Studio 2010 SP1 Tools for SQL Server Compact 4.0. These components should be installed after installing Visual Studio 2010 SP1. All the SQL Server Compact components that need to be installed to enable SQL Server Compact 4.0 to work with Visual Studio 2010 SP1 can be installed with one click by using the Microsoft Web Platform Installer 3. The different designers and project systems that can be used for developing applications for SQL Server Compact are detailed below:.
Server Explorer and Query Designer
After installing the SQL Server Compact 4.0 components for Visual Studio 2010 SP1, there is a new option to connect to SQL Server Compact 4.0 databases in the Change Data Source dialog. This option is in addition to the existing option to connect to the SQL Server Compact 3.5 databases. After connecting to a SQL Server Compact 4.0 database, you can use Server Explorer to navigate through objects in Server Explorer and use Query Designer to run queries. For more information, see How To: Browse the database using Server Explorer in Visual Studio.
Transact-SQL Editor
SQL Server Compact 4.0 supports a rich subset of Transact-SQL syntax supported by the other editions of SQL Server. You can access Transact-SQL editor in the Data menu of Visual Studio 2010 and connect to a SQL Server Compact 4.0 database in the Connect to Server dialog. You can connect to the database either by specifying the path to the database file or by selecting the New Database option in the Database file dropdown field. For more information, see Visual Studio Transact-SQL Editor.
Note
When the path to the database file is specified, the version of file is auto-detected, and depending on the version of the file, either SQL Server Compact 3.5 or SQL Server Compact 4.0 runtime engine is used to connect to the file. This behavior is different from that in the Add Connection and Change Data Source dialogs of Server Explorer where the data source has to be explicitly set to SQL Server Compact 3.5 or SQL Server Compact 4.0. The Create New SQL Server Compact Database dialog provides options for creating either SQL Server Compact 3.5 or SQL Server Compact 4.0 database file.
Designers in the VB and C# ASP.NET Web application or web site projects
Designers can be used in the VB and C# based ASP.NET Web application or Web site projects to generate Datasets or Entities from a SQL Server Compact database and these can be bound to controls on the ASP.NET Web page. The workflow is same as that used for SQL Server. For more information about the controls, see SqlDataSource and the EntityDataSource Web control pages. In the Add New Item dialog box, a new item template for SQL Server Compact 4.0 Local Database is available to add a new empty SQL Server Compact 4.0 database file to the project.
ClickOnce Deployment for Managed Applications
ClickOnce is a software installation technology that is supported by SQL Server Compact to deploy managed desktop applications on computers. For administrators, deploying or updating an application consists of updating files on a deployment server. You do not have to update each client individually. Microsoft Visual Studio 2010 SP1 provides full support for publishing and updating applications that are deployed with ClickOnce. ClickOnce deployment is available for Windows desktop projects that are created by using Visual Basic, Visual C#, and Visual J#, but not for Visual C++. For information about 64-bit ClickOnce deployments, see Managing 64-bit Database Applications.
Support for WebMatrix projects in Visual Studio 2010 SP1
A WebMatrix project can be opened in Visual Studio 2010 SP1 or in Visual Web Developer 2010 SP1 Express by clicking the Visual Studio option in the WebMatrix Editor. In addition, if the required SQL Server Compact components needed for the design time support in Visual Studio 2010 SP1 are not installed on the machine, a message will pop-up to start the Web Platform Installer, and the Web Platform Installer will install all the required components on the machine.
Integration with WebMatrix
Microsoft WebMatrix is a free Web development stack that integrates a Web server with database and programming frameworks to create a single, integrated experience for developing ASP.NET Web sites. SQL Server Compact 4.0 is the default database for Microsoft WebMatrix. In addition to this, WebMatrix encapsulates all the technologies such as ASP.NET, IIS Express, Editor that are needed to develop, test and deploy ASP.NET Web sites to third-party Web site hosting providers. With growing requirements for larger database or high-volume Web traffic, you can also migrate the data and schema from SQL Server Compact to the other editions of SQL Server. For more information, see Data Migration from SQL Server Compact
Database for ASP.NET Web Applications and Web Sites
SQL Server Compact 4.0 has been optimized and tuned for use for ASP.NET Web applications. SQL Server Compact has been field tested to ensure that SQL Server Compact 4.0 can handle the load of starter websites.
Rapid Web site development with free, open source Web applications
Popular open source Web applications like mojoPortal, or Orchard, Umbraco support SQL Server Compact 4.0 and can be used to rapidly develop, test and deploy Web sites.
Partial trust and virtual memory
SQL Server Compact 4.0 works in partial trust that is the mainline scenario in which the ASP.NET Web applications are deployed. The virtual memory used by SQL Server Compact 4.0 has been optimized to ensure that the maximum allowed 256 connections can be opened for the database.
One click migration to SQL Server
As the requirements grow to the level of enterprise databases, the schema and data can be migrated from SQL Server Compact to SQL Server using the Migrate option in the WebMatrix Editor. This also adds a web.config xml file to the project that contains the connection string for the SQL Server. After the migration completes, the Web site project seamlessly switches from using SQL Server Compact to SQL Server.
Removes the soft block for use in ASP.NET web applications
In the earlier versions of SQL Server Compact a flag, SQLServerCompactEditionUnderWebHosting had to be set to use SQL Server Compact in ASP.NET Web applications. The flag is removed in SQL Server Compact 4.0.
Other Features
SQL Server Compact 4.0 has a strong base to ensure that it can be installed without any problems, and can be deployed easily, and works reliably while providing the highest level of security for data.
Consistent setup and small download
There are separate MSIs for x86 and x64 platforms, and the x64 MSI installs the SQL Server Compact components in both the WOW mode at the location %Program Files (x86)%\Microsoft SQL Server Compact Edition\v4.0 and the native mode in %Program Files%\Microsoft SQL Server Compact Edition\v4.0. The x86 MSI is blocked from installing SQL Server Compact components on an x64 platform, and similarly x64 MSI is blocked from installing on x86 platform.
The size of the executable file of SQL Server Compact 4.0 is the same as that of the earlier versions at 2.5 MB.
Easy Private Deployment
All the x86 and x64 SQL Server Compact binaries and DLLs that need to be privately deployed reside in the folder %Program Files%\Microsoft SQL Server Compact Edition\v4.0\Private. The contents of the folder should be copied in the application directory for private deployment of SQL Server Compact. Both the x86 and x64 MSIs install the x86 and x64 SQL Server Compact binaries and DLLs in the Private folder. There is no need to install the x64 MSI on an x86 platform to get the x64 DLLs.
Important
The native DLLs of SQL Server Compact need the Microsoft Visual C++ 2008 Runtime Libraries (x86 and x64) SP1 to function properly. The binaries for the Visual C++ 2008 runtime are present in the Private folder Deploying all the DLLs and folders in the Private folder deploys all the files needed for SQL Server Compact to work properly.
ADO.NET Entity Framework 4 (.NET Framework 4)
SQL Server Compact 4.0 works with the Code-First Programming Model of ADO.NET Entity Framework 4 (.NET Framework 4). In addition, the columns that have server generated keys like identity or rowguid are also supported in SQL Server Compact 4.0 when used with ADO.NET Entity Framework 4.0 (the version of ADO.NET Entity Framework that released with .NET Framework 4). Support for the code-first and server-generated keys completes the SQL Server Compact support for ADO.NET Entity Framework.
Higher reliability
The ASP.NET Web applications have a different workload than the desktop applications. SQL Server Compact 4.0 is capable of handling the workload of starter Web sites, which has made the product more reliable than the earlier versions.
Greater security
SQL Server Compact 4.0 uses the SHA2 algorithm to secure data and provide a high level of security.
T-SQL syntax for OFFSET & FETCH
SQL Server Compact 4.0 supports the T-SQL syntax for OFFSET & FETCH, which enables users to run paging queries against the database file.
API enhancements
SQL Server Compact 4.0 adds in the support for two new APIs.
System.Data.SqlServerCe.SqlCeConnection.GetSchema()
It can be used to get the schema from the SQL Server Compact database file. The API is also used to provide the support for System.Data.Common.DbConnection.GetSchema. For more information, see GetSchema Method.
System.Data.SqlServerCe.SqlCeConnectionStringBuilder().
This API helps developers to programmatically create correct connection string for SQL Server Compact 4.0, and to parse & rebuild existing connection strings. The API is also used to provide the support for System.Data.Common.DbConnectionStringBuilder. For more information, see SqlCeConnectionStringBuilder Class.
See Also
Reference
ORDER BY Clause (SQL Server Compact)
Concepts
Installing a Development Environment
Private Deployment vs. Central Deployment (SQL Server Compact)
Transactions (SQL Server Compact)