Development support for SQL Server Compact is provided by Visual Studio. Database administration support is provided by SQL Server. You can access SQL Server Compact databases stored on a smart device or a desktop computer by using SQL Server Management Studio in SQL Server. You can create managed applications by using either Microsoft Visual Basic or Visual C#, or you can use Visual C++ to create native applications.

Note

SQL Server Compact 3.5 Service Pack 1 (SP1) is included with SQL Server 2008 and Visual Studio 2008 SP1. It is also available as a download from the Microsoft SQL Server Compact Web site.

SQL Server Compact provides the following features when you are using it as the local data source for your applications.

SQL Server Compact is file-based, which means that the connection string is a file path to the database (.sdf) file.

SQL Server Compact does not run as a service. This is one of the main differences between using SQL Server Compact 3.5 and using SQL Server or SQL Server Express.

SQL Server Compact supports multiple connections up to the 256 connection limit. Opening connections on different processes is also supported.

SQL Server Compact supports database files up to 4 GB.

SQL Server Compact is a file-based database that consists of DLLs that are approximately 1.4 MB. The following list provides some scenarios in which you might want to use SQL Server Compact in your applications:

In applications intended for desktop computers and mobile devices.

In applications that will be used in occasionally connected scenarios.

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.

SQL Server Compact 3.5 SP1

SQL Server Compact 3.5 Service Pack 1 (SP1) is included with SQL Server 2008 and Visual Studio 2008 SP1. SQL Server Compact 3.5 SP1 provides several enhancements and new features for software developers. The following list includes some new features among several others:

SQL Server Compact supports the ADO.NET Entity Framework. The Entity Framework enables you to work with data in the form of domain-specific objects and properties, such as customers and customer addresses, without having to concern themselves with the underlying database tables and columns where this data is stored.

SQL Server Compact supports LINQ to Entities. LINQ to Entities allows developers to create flexible, strongly-typed queries against the Entity Framework object context by using LINQ expressions and the LINQ standard query operators directly from the development environment.

SQL Server Compact can run natively in a 64-bit environment.

SQL Server Compact provides support for replicating the new data types in SQL Server 2008, such as date, time, datetime2, datetimeoffset, geography, and geometry. For more information about data types in SQL Server 2008, see Data Types in SQL Server 2008 Books Online.

You can manage a SQL Server Compact database on a desktop computer or mobile device by using SQL Server Management Studio, which is a SQL Server management tool. This tool provides the same experience for users whether they connect to SQL Server or SQL Server Compact. SQL Server Compact databases can also be managed by using SQL Server Management Studio Express. You can download SQL Server Management Studio Express for free from the Download Center.

You can create SQL Server Compact databases on your local computer from Management Studio and Management Studio Express. You can configure these databases, populate them with data, and then deploy the databases to multiple devices. This saves significant development and deployment time.