Data Management

Why choosing SQL Server CE over Access CE makes sense

Regardless of whether the application is stand-alone or uses RDA or merged replication, SQL Server CE is a robust database. When developing handheld applications with a database like SQL Server CE, the only limitations are the device's available memory.

It's taken a little while, but I’ve finally come to terms with the idea of a relational database on a handheld device. This acceptance required two things: The first was the realization that a current generation Pocket PC has more storage than a 1980s minicomputer; the second was SQL Server CE.

SQL Server CE is available as a download for use with SQL Server 2000 from the Microsoft Web site and as part of Microsoft’s eMbedded Visual Tools 3.0. If you don’t have SQL Server 2000 and just want to take SQL Server CE out for a spin, use the eMbedded Visual Tools version. Both come with the Windows CE version of Query Analyzer (Figure A), which provides a way to create and administer databases on the Windows CE device. Those unfamiliar with Query Analyzer will find its GUI relatively intuitive.

Figure A
Query Analyzer for Windows CE


Installing Query Analyzer
The task of installing the Windows CE Query Analyzer comes down to first installing the software on the desktop and, second, seeing how well you know your handheld device. As with a lot of Windows CE applications, you'll need to know the processor type. Fortunately, multiple versions of the software for various processors are supplied when SQL Server CE is installed on the host machine. Unfortunately, finding the folders on the host machine is something of a treasure hunt, which is why Table A shows the default software locations for the various processors.
Table A
Processor SQL Server 2000
ARM C:\Program Files\Microsoft SQL Server CE 2.0\Device\Arm\Sa1100
MIPS - R3000 C:\Program Files\Microsoft SQL Server CE 2.0\Device\Mips\R3000
MIPS - R4100 C:\Program Files\Microsoft SQL Server CE 2.0\Device\Mips\R4100
SHx C:\Program Files\Microsoft SQL Server CE 2.0\Device\Shx\Sh3
Default Query Analyzer locations for different Windows CE microprocessors

You need to take the files from the appropriate folder and move them from the desktop to a folder on the handheld device. Then you need to execute dllregister.exe, which is included, to register the DLLs on the handheld device. The Query Analyzer is isqlw20.exe; I’d recommend creating a shortcut from your device’s Start menu.

SQL Server CE vs. Access CE
The first reason to use SQL Server CE instead of Access CE can be summed up in a single word: consistency. SQL Server is an enterprise database and, therefore, many companies use it. Odds are that most developers have at least encountered it before. Access, on the other hand, is used primarily for demonstrations or quick-and-dirty applications. And as an enterprise-class database, SQL Server CE is more robust than Access CE. Table B lists the data types supported by SQL Server CE.
Table B
SQL Server CE data
type
SQL Server data type Notes
bigint bigint Integer between -2^63 and 2^63-1, inclusive. Storage size is 8 bytes.
int int Integer between -2^31 and 2^31-1, inclusive.
numeric(p,s) numeric or decimal Fixed decimal numeric data between -10^38 and 10^38-1, inclusive.
smallint smallint Integer between -32,768 and 32,767, inclusive. Storage size is 2 bytes.
tinyint tinyint Integer between 0 and 255, inclusive. Size is 2 bytes.
bit bit Integer either 0 or 1. Size is 1 byte.
real real Floating point between -3.40E+38 and 3.40E+38, inclusive.
float float Floating point between -1.79E+308 and 1.79E+308, inclusive.
money money Currency data between -2^63 and 2^63-1, inclusive. Accuracy is to
ten-thousandths. Storage size is 8 bytes.
datetime datetime Date and time between Jan. 1, 1753, and Dec. 31, 9999. Accuracy is 3.33 milliseconds. Storage size is 8 bytes.
ntext ntext Variable length Unicode data. Maximum length is 536,870,911 characters. Storage size is two times the number of characters.
nchar(n) nchar Fixed length Unicode data. Maximum length is 255 characters. Storage size is two times the number of characters.
nvarchar(n) nvarchar Variable length Unicode data. Maximum length is 255 characters. Storage size is two times the number of characters.
binary(n) binary Fixed length binary data. Maximum length is 510 bytes.
varbinary varbinary Variable length binary data. Maximum length is 510 bytes.
image binary or image Variable length binary data. Maximum length is 2^31-1 bytes.
uniqueidentifier uniqueidentifier Globally unique identifier. Storage size is 16 bytes.
Data types supported by SQL Server CE

The second reason to use SQL Server CE is the aforementioned Query Analyzer. Personally, I find using a GUI much easier than writing SQL to create a table. For me, when given a choice between handling raw SQL like this:
CREATE TABLE new_table (new_column INT UNIQUE)

vs. the GUI approach in Figures B and C, the figures will always win. In addition, Query Analyzer provides a way to test queries to make sure that my SQL will work.

Figure B
Using Query Analyzer


Figure C
Defining a new field (column) in Query Analyzer


The final reason to use SQL Server CE instead of Access CE is RDA (Remote Data Access) and merged replication. These are both methods of connecting to and exchanging data with a SQL Server 2000 database using Microsoft IIS (Internet Information Services).

Remote Data Access
RDA provides a way for a Windows CE application to pull data from a remote SQL Server database table and store the information in a local SQL Server CE database table. The application can then process using the local copy of the data, which can later push the changes back to the remote SQL Server database table. For wireless applications or some other form of connected application, RDA can be used to submit SQL commands for execution against the remote SQL Server database table.

Merged replication
Merged replication is for applications where data can be updated on either the disconnected Windows CE device or the server. When the Windows CE device is connected again, the data is merged on both the device and SQL Server. Unlike RDA, merged replication requires that the data be “published” by SQL Server and that the Windows CE has a subscription before the device can access the data.

The better choice
Regardless of whether the application is stand-alone or uses RDA or merged replication, SQL Server CE is a robust database. When developing handheld applications with a database such as SQL Server CE, the only limitations are the device’s available memory and our imaginations.
0 comments