SQL Server CE

Topics

• What's New in SQL Server Compact Edition
• Feature Improvements to Enhance Reliability and Performance
• Synchronization
• Storage Engine
• Query Processor
• Integration with SQL Server 2005
• Integration with Microsoft Visual Studio 2005
• Advantage of SQL SERVER CE
• Disadvantage of SQL SERVER CE
• Connection strings for SQL Server Compact Edition
• Difference Between SQL Server Compact Edition (CE) and SQL Server Express Edition

What's New in SQL Server Compact Edition

Microsoft SQL Server 2005 Compact Edition (formerly Microsoft SQL Server 2005 Mobile Edition and Microsoft SQL Server 2000 Windows CE 2.0), is a compact database that can be deployed on desktop computers, smart devices, and Tablet PCs.

For desktop computers and Tablet PCs, SQL Server Compact Edition has a separate installer (SQLServerEv31-EN.msi). You can access SQL Server Compact Edition databases stored on a smart device or on the desktop computer by using SQL Server Management Studio in SQL Server 2005 or SQL Server Management Studio Express (SSMSE)

Feature Improvements to Enhance Reliability and Performance

SQL Server Compact Edition has many new and updated features that enhance the database reliability and performance. These improvements can be divided into general feature categories: synchronization, the storage engine, and the query processor.

Synchronization

Several enhancements are available to improve the process of replicating and synchronizing data with SQL Server Compact Edition. The enhancements include the following:

• Support for multiple subscriptions

  In earlier versions of SQL Server Compact Edition, only one subscription could be created for each database. In an environment where the published data derives from multiple publications, a developer had to create a separate subscription database for each publication, and then specify which database the application needed to open. With SQL Server Compact Edition, multiple subscriptions can be contained in a single database, reducing the amount of programming required. For more information about multiple subscriptions, see "Supporting Multiple Subscriptions" in the SQL Server Compact Edition Books Online.

• Multi-user support and synchronization

  To support multiple applications accessing the same database at the same time, SQL Server Compact Edition provides multi-user support. Multi-user support enables multiple users of a database to synchronize data without having to disconnect the database before they use merge replication or remote data access (RDA). For more information about multi-user synchronization, see "Multi-user Access and Remote Data Access (RDA)" and "Multi-user Access and Synchronization" in the SQL Server Compact Edition Books Online.

• Synchronization progress status

  Developers can use the managed APIs to provide synchronization status feedback, which can be used to inform users of synchronization progress. For more information about the status bar, see "Asynchronous Data Synchronization" in the SQL Server Compact Edition Books Online.

• Column-level tracking

  In earlier versions of SQL Server Compact Edition, a whole row was synchronized with Microsoft SQL Server, even if only one column in the row had been modified. This frequently resulted in longer synchronization times if the unmodified columns in the row contained lots of data. In SQL Server Compact Edition, only the modified columns are synchronized. For more information about column-level tracking, see "Using Row-Level and Column-Level Tracking" in the SQL Server Compact Edition Books Online.

Storage Engine

The new and improved features of the storage engine in SQL Server Compact Edition enhance the reliability and performance of mobile applications. These features include the following:

• Revised storage engine

  The storage engine architecture has been rewritten to optimize for a mobile architecture in which each application shares a common memory pool. Better data reliability is now ensured through true atomicity, consistency, isolation, and durability (ACID) support, and when devices experience battery power issues and connectivity disruptions. For more information, see "Overview of Database Engine (SQL Server Compact Edition)" in the SQL Server Compact Edition Books Online.

• Multi-user support

  To support multiple applications accessing the same database at the same time, SQL Server Compact Edition provides row-level locking of data pages, page-level locking, and isolation levels to help ensure data integrity during concurrency. For more information about locking, see "Locking (SQL Server Compact Edition)" and "Lock Escalation" in the SQL Server Compact Edition Books Online.

• Auto re-use of empty pages

  SQL Server Compact Edition supports the auto shrink feature, which automatically reclaims unused data pages and saves device storage space. For more information about auto shrink, see "Maintaining Databases" in the SQL Server Compact Edition Books Online.

Query Processor

Improvements to the SQL Server Compact Edition query processor include the following:

• Cost-based optimization

  The query processor takes advantage of statistics support in the storage engine to create significantly better cost-based query plans. For more information about statistics, see "Overview of Database Engine (SQL Server Compact Edition)" in the SQL Server Compact Edition Books Online.

• Execution plan and query hints

  With SQL Server Compact Edition, developers can view the query plan and then refine the queries as appropriate. For example, you can refine the query plan based on your database design, access time of the media, or CPU speed for your specific supported devices.

• A new SqlCeResultSet object

  The new SqlCeResultSet object reveals an updateable, scrollable cursor so that developers can directly access a SQL Server Compact Edition database without double-buffering data on the device with a DataSet object. This class provides significant performance improvements over using the DataSet object while reducing the amount of code required accomplishing the same task. It also supports Informs data binding interfaces and can be bound to UI controls such as DataGrid, Textbox, and List Box. For more information about cursors, see "Cursors (SQL Server Compact Edition)" in the SQL Server Compact Edition Books Online, and System.Data.SqlServerCe.

Integration with SQL Server 2005

You can manage a SQL Server Compact Edition database on a desktop computer or a device by using SQL Server Management Studio, a SQL Server 2005 management tool that replaces SQL Server Enterprise Manager and SQL Server Query Analyzer. This integrated tool provides the same experience for users whether they connect to SQL Server 2005 or SQL Server Compact Edition. SQL Server Compact Edition databases can be also managed by using SQL Server Management Studio Express.

Benefits of using Management Studio include the following:

• Graphical query execution plans

  Graphical query execution plans enable developers to easily see query execution plans and so better understand potential query performance issues. A separate feature, query hints, is available to control some aspects of the execution plan.

• New Subscription Wizard

  The New Subscription Wizard requires minimal effort by users when they create, and subsequently synchronize, a subscription database. For more information, see "New Subscription Wizard" in the SQL Server Compact Edition Books Online.

• Improved Configure Web Synchronization Wizard

  SQL Server Compact Edition supports improved versions of the Configure Web Synchronization Wizard. Improvements in the Configure Web Synchronization Wizard include enhanced usability and support for HTTPS virtual directories. For more information, see "Configure Web Synchronization Wizard" in the SQL Server Compact Edition Books Online.

• Integration with SQL Server Integration Services (SSIS)

  SSIS lets developers transfer data between a SQL Server Compact Edition database and a variety of data sources, including Microsoft Access, Oracle, and IBM databases. SSIS includes a destination adapter and connection manager for SQL Server Compact Edition.

• SQL Editor

  The SQL Editor component of Management Studio is the primary tool for interactively designing and testing Transact-SQL statements, queries, and scripts. SQL Editor has several features that simplify the process of writing and editing queries and code. You can also edit scripts that are created from files or from Object Explorer.

• Database Deployment

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

Some of the enhancements made in SQL Server 2005 for merge replication provide the following benefits for SQL Server Compact Edition applications:

• Managed API access to synchronize business logic

  SQL Server 2005 reveals a managed API that lets developers modify business logic data before that data is stored in the SQL Server database. For more information, see "Executing Business Logic during Merge Synchronization" in SQL Server 2005 Books Online.

• Support for download-only articles (tables)

  This feature reduces the amount of metadata transferred during initial synchronization and reduces the processing time on SQL Server when developers perform subsequent synchronizations. For more information, see "Introducing Merge Replication" and "Creating the Publication" in the SQL Server Compact Edition Books Online

• Support for partitioned (filtered) articles

  Many mobile applications filter data so that users do not access the same data across devices. With support for partitioned articles, developers can significantly reduce synchronization time and increase scalability. This feature is especially useful for deployments of many smart devices. For more information about partitioned articles, see "Creating the Publication" in the SQL Server Compact Edition Books Online.

Integration with Microsoft Visual Studio 2005

There are two major enhancements to the integration of SQL Server Compact Edition with Microsoft Visual Studio 2005:

• Database Management

  Developers can manage a SQL Server Compact Edition database on a desktop computer or supported devices by using Visual Studio 2005. With this feature, developers can drag a SQL Server Compact Edition table to a control in the application to enable that control to automatically bind to the table.

• Automatic Installation of SQL Server Compact Edition

  When you use Visual Studio to build a Microsoft .NET application that uses SQL Server Compact Edition, the SQL Server Compact Edition engine is automatically installed on the device the first time that you deploy the application. If you are building a native application with Visual Studio 2005, you will have to manually install SQL Server Compact Edition. For more information, see "Deploying Smart Device Applications" in the SQL Server Compact Edition Books Online.

• Data Directory Support

  Data Directory is a substitution string that indicates the path of the database. Data Directory makes it easy to share a project and to deploy an application by eliminating the need to hard-code the full path. For example, instead of having the following connection string:
• "Data Source= c:\program files\MyApp\Mydb.sdf"
  By using |Data Directory| (enclosed in pipe symbols as shown), you can have the following connection string:
  "Data Source = |DataDirectory|\Mydb.sdf"
  You set the Data Directory property on the AppDomain by calling AppDomain.SetData.

• Click Once Deployment for Managed Applications

  Click once is a new software installation technology that is supported by SQL Server Compact Edition to deploy managed applications on a desktop and a Tablet PC. Click once simplifies deploying a Windows-based application to a Web server or network file share. For administrators, deploying or updating an application consists of updating files on a server. You do not have to update each client individually. Microsoft Visual Studio 2005 provides full support for publishing and updating applications that are deployed with Click Once. Click once deployment is available for projects that are created by using Visual Basic, Visual C#, and Visual J#, but not for Visual C++.

Advantage of SQL SERVER CE

1. SQL Server Compact Edition has over SQL Express is the lightweight footprint of 2 MB that it places on the installed platform. This compares very favorably with SQL Express footprint of a 36MB download and whopping 185MB when installed on the desktop.
2.  It is possible to set up the Compact Edition to install without users needing administrator privileges on their PC. This may not be so important if your target users will be using your application on their home PC. In a corporate environment, however, installing your application with administrator privileges may need intervention from I.T. support - as many enterprises don't give administrator privileges to their non-I.T. employees for their work PCs. Be aware that by not deploying the database as a Windows Service, this means that Windows Update is not aware of the presence of the database engine in this setup so the developer would have to think about how to ensure the engine was maintained and updated with patches.
3. SQL CE does not require you to run a setup or install a database server in order to use it.  You can simply copy the SQL CE binaries into the \bin directory of your ASP.NET application, and then your web application can use it as a database engine.  No setup or extra security permissions are required for it to run.
4. SQL CE runs in-memory within your ASP.NET application and will start-up when you first access a SQL CE database, and will automatically shut down when your application is unloaded.  SQL CE databases are stored as files that live within the \App_Data folder of your ASP.NET Application

Disadvantage of SQL SERVER CE

1. It does not support features Procedural T-SQL Select Case, If, features, transactions, and the ability to implement a complex security model which available in the SQL Server Express.
2. It does not support view, stored procedure and trigger.
3. It does not Runs as a service
4. “IS NULL” is not supported in SQL Server CE, instead of “IS NULL” function we can use the “COLLASEC” function
5. It not support the @Scope Identity function to fetch the identity value, instead of the @Scope Identity function we can use the @@Identity function to fetch the identity value return from the database.
6. Datatype varchar not available, we should use nvarchar.

Connection strings for SQL Server Compact Edition

Standard

Data Source=MyData.sdf; Persist Security Info=False;

How to specify the location of the SDF file

Often times the .SDF database is not running in the current directory so it becomes necessary to programmatically set the path to the SDF file. This is an example (.net C#) on how to do this when the SDF file is located in the same directory as the executing application.
Data Source=" + (System.IO.Path.GetDirectoryName (System.Reflection.Assembly.GetExecutingAssembly ().GetName ().CodeBase) + "\\MyData.sdf;Persist Security Info=False;

Specifying the maximum database size

The maximum size of the database is by default 128 MB. Override this by using the following connection string.
Data Source=MyData.sdf; Max Database Size=256; Persist Security Info=False;

Specifying the maximum buffer size

The largest amount of memory that can be in use before the server starts flushing changes to disk is by default 640 kB. Override this by using the following connection string.
Data Source=MyData.sdf; Max Buffer Size=1024; Persist Security Info=False;

Encryption enabled

Use this connection string to enable encryption on the database.


Data Source=MyData.sdf; Encrypt Database=True; Password=myPassword; File Mode=shared read; Persist Security Info=False;
The Encrypt Database="True" pair is really not necessary as the presence of the Password-parameter itself turns on encryption for the connection.

Exclusive access

Use this one to disallow other processes from opening or modifying the database while you have it open.
Data Source=MyData.sdf; File Mode=Exclusive; Persist Security Info=False;

Read only access

Use this one to open a read-only copy of the database.
Data Source=MyData.sdf; File Mode=Read Only; Persist Security Info=False;

Exclusive but shared for reading

Use this one to allow other processes to read, but not modify, the database while you have it open.
Data Source=MyData.sdf; File Mode=Shared Read; Persist Security Info=False;

Specifying the maximum temp file size

The maximum size of the temporary database file is by default 128 MB. Override this by using the following connection string.
Data Source=MyData.sdf; Temp File Max Size=256; Persist Security Info=False;

Microsoft.SQLSERVER.CE.OLEDB.3.5

Type:    OLE DB Provider
Usage:  Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5
Manufacturer:  Microsoft
Standard : Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5; Data Source=myPath\myData.sdf;

Microsoft.SQLSERVER.MOBILE.OLEDB.3.0

Type:    OLE DB Provider
Usage:  Provider=Microsoft.SQLSERVER.MOBILE.OLEDB.3.0
Manufacturer:  Microsoft
Standard : Provider=Microsoft.SQLSERVER.MOBILE.OLEDB.3.0; Data Source=myPath\myData.sdf;

.NET Framework Data Provider for OLE DB

Type:    .NET Framework Wrapper Class Library
Usage:  System.Data.OleDb.OleDbConnection
Manufacturer:  Microsoft

Bridging to Microsoft.SQLSERVER.CE.OLEDB.3.5

This is just one connection string sample for the wrapping OleDbConnection class that calls the underlying OLEDB provider.
Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5; Data Source=myPath\myData.sdf;

Difference Between SQL Server Compact Edition (CE) and SQL Server Express Edition

Feature	SQL Server Compact Edition	SQL Server Express Edition
ClickOnce deployment	Yes	Yes
Privately installed, embedded, with the application	Yes	No
Non-admin installation option	Yes	No
Runs on Windows Mobile platform	Yes	No
Installed centrally with an MSI	Yes	Yes
Runs in-process with application	Yes	No
Runs as a service	No	Yes
File format	Single file	Multiple files
Data file storage on a network share	Yes	No
Support for different file extensions	Yes	No
Database size support	4GB	4GB
XML storage	Yes (stored as nText)	Yes
Transact-SQL Common Query Features	Yes	Yes
Procedural T-SQLSelect Case, If, features	No	Yes
Remote Data Access (RDA)	Yes	No
Distributed transactions	No	Yes
Stored procedures, views, triggers		Yes
Role-based security	No	Yes
Number of concurrent connections	256	Unlimited