Once you understand the role that SQLCE plays in a solution, it is important to understand its architecture and features. The diagram shown in Figure 5-2 illustrates the architecture of SQLCE and the various software components that make up a solution that uses it.
As you can see from Figure 5-2, SQLCE itself is implemented as a DLL and an OLE DB provider (OLE DB CE) that can be accessed from both managed code using the .NET Data Provider for SQL Server CE (SqlServerCe is discussed in the following section) and eVB using ADOCE and directly using eVC++. This provider encapsulates the SQL Server Client Agent that is responsible for replication and RDA, discussed further in Chapter 7, and the SQL Server CE Engine.
Unlike the server version, the SQLCE database engine is implemented in a DLL for performance reasons, even though the engine will be loaded in each process, using SQLCE on the device. However, because typically only one application using SQLCE will be active at any one time and because SQLCE supports only one concurrent connection, it is not a significant issue.
The database engine consists of two components: the storage engine that manages the data stored on the device in 4K pages and the query processor that processes (compiles, optimizes, and generates query plans) queries sent from applications. Together these two components support the following features, among others:
Query processor supports SQL, including SELECT, MAX, MIN, COUNT, SUM, AVG, INNER/OUTER JOIN, GROUP BY/HAVING, ORDER BY, UNION, and operators including ALL, AND, ANY, BETWEEN, EXISTS, NOT, SOME, OR, LIKE, IN, also Transact-SQL including DATEADD, DATEDIFF, GETDATE, COALESCE, SUBSTRING, and @@IDENTITY among others
 Both IDENTITY columns and uniqueidentifier can be used to create system-assigned primary key values in a SQLCE table. However, if all rows in the table are deleted and the database compacted, the identity counter is reset to its original value. This behavior can affect applications that need to synchronize with a back-end database.
249 indexes per table, multicolumn indexes
Databases of up to 2GB
BLOBs of up 1GB
255 columns per table
128 character identifiers
Unlimited nested subqueries
Support for NULL values
Data Manipulation Language (DML): INSERT, UPDATE, and DELETE
Data Definition Language (DDL): CREATE, DROP, ALTER on databases, tables, and indexes
17 data types, including Unicode (nchar, ntext) and GUID (uniqueidentifier)
PRIMARY KEY, UNIQUE, and FOREIGN KEY constraints
Replication tracking capability that tracks changed data on the device as discussed in Chapter 7
Although not shown in Figure 5-2, SQLCE also ships with an improved Query Analyzer that runs on the device and can be used to create databases, tables, and indexes; query data; insert and delete rows; compact and repair a database. It is generally used by developers to ensure that their local database is accessible.
When using the Compact Framework, Query Analyzer (Isqlw20.exe) is deployed to the device automatically using a .cab file if the application references the SqlServerCe provider, and a shortcut is placed in the Start menu on the device.
 eVB and eVC++ developers must manually copy the Query Analyzer to the device, along with the appropriate supporting files, as noted in the documentation.
To use the Query Analyzer, a developer need simply navigate to the database file (typically with an .sdf extension) and tap the green arrow on the bottom of the screen. The tables and their structures can then be navigated, as shown in Figure 5-3. Developers can then query the data in a table by tapping on the arrow or tapping the SQL pane and writing the SQL directly.