Thanks again to Jim Wilson for stepping in and sharing some great information on SQL Server Compact. Here are Jim's notes from the show and a link to the recording.
Links
Questions
What is the size of SQL Compact DLLs
Approximately 2.3MB
Just starting work on SSCE and looking to mimic a SQL Server DB and use Sync Services to sync the two. What is your recommendation on how to sync key fields on the server DB to those in SSCE? I need IDs (or GUIDs) on the server to ensure uniqueness, But I'm not sure how they will sync. If possible, I'd like to avoid GUIDs as keys.
These two articles together provide guidelines for doing just what you’re asking…
http://msdn.microsoft.com/en-us/library/cc305322(SQL.100).aspx
http://msdn.microsoft.com/en-us/library/cc305973(SQL.100).aspx
Does it have XQuery?
There is no XQuery support for SQL Server Compact. The reason is that SQL Server Compact is designed to be a low overhead local database. The goal is not necessarily to have every feature of SQL Server but rather to have an upwardly compatible path while minimizing the resource costs. Implementing XQuery features over a relational database is very resource intensive both in terms of the increased size of the runtime and the very high CPU overhead to execute the queries.
Are you able to dynamically create a database from within you application or at least drop & create tables?
Absolutely! To create a new database use SqlCeDataEngine.CreateDatabase; to create, modify, or drop a table simply use SqlCeCommand.ExecuteNonQuery to execute the appropriate SQL to do the table creation, modification, or drop.
How does SQL Server Compact support storing files like images? Will it be the same as 2008?
For retrieving images (and other BLOB data) use SqlCeDataReader.GetBytes . This returns a Byte array which you can then create a Bitmap from through the use of a MemorySteream like the following:
const int imageColIndex = …
const int maxImageSize = …
Bitmap GetBitmapFromReader(SqlCeDataReader reader)
{
Byte[] imageBytes = new Byte[maxImageSize];
int bytesRead = reader.GetBytes(imageColIndex, 0, imageBytes, 0, maxImageSize);
MemoryStream imageStream = new MemoryStream(imageBytes, 0, bytesRead);
return new Bitmap(imageStream);
}
To put an image in the database use SqlCeResultSet.SetBytes…
In terms of “will it be the same as 2008?” … If the question refers to the FileStream attribute or the Remote Blob Store API, those are not currently supported for the current version of SQL Server Compact. To my knowledge, there is no support planned for these features in the next release either.
A couple of notes on working with images (Blobs in general)…
When you’re reading images from the database, be sure to set the SqlCeCommand.CommandBehavior to SequentialAccess to improve the efficiency of retrieving such large data records.
Be very careful when choosing to store images (or other Blobs) directly in a SQL Server Compact database. Like most databases, SQL Server Compact manages data at the page level. The more densely populated a page, the better the overall performance. If a record’s page is already in memory, you’ll access the data faster (more records on each page increases the probability that the desired page is already in memory).
In the resource constrained world of mobile devices, how well a particular database is able to do these 2 things (lots of records on each page/page likely to be already loaded) can dramatically impact your applications performance. Images can take up a great deal of storage therefore storing a large number of images or very large images directly in the database can undermine the goal of densely populated pages. In cases such as this, you would get much better performance by storing the image on the file system and simply storing the image filename in the database – the trade off here is that with the image in the database the database automatically manages it (transactional consistency between the data and the corresponding image, the image is automatically synched to the server with the rest of the data when you’re using Sync Services, Merge Rep, etc.) – you need to decide which of these issues is most important for your particular application.
Jim mentioned RDA - I think I've read that RDA is going away (not supported) in latest version or maybe future version of compact framework? and compatibility issues with SQL 2008
RDA is not receiving any further enhancements because, as you noted, the long term plan is to shift all synchronization to Sync Services. I don’t suspect that RDA will go away anytime soon though; there are a lot of apps in the world that rely on it. In my opinion, RDA will continue to exist for several more years. The beauty of RDA is that it’s super easy to use and setup. It’s also very mature. Currently Sync Services tend to involve more work on a developer’s part to setup than RDA.
One thing that’s very important to keep in mind is that Sync Services for mobile devices is still in beta. If you’re in a situation that allows you to use beta software and your delivery schedule allows you to take the time to learn, setup, and understand Sync Services you should definitely go that route. However, if you’re in a situation where you need to turnaround a stable, reliable solution quickly I believe that RDA is still a very good choice. That said, I’d suggest keeping a watch on Sync Services. It’s the long-term data synchronization plan and will be evolving for years to come.
RDA definitely works with SQL Server 2008; it may not support every new feature of SQL Server 2008 but RDA’s capability with SQL Server 2005 is very good and RDA should work just as well with the same and similar features of SQL Server 2008. RDA’s coupling with SQL Server is relatively lightweight making it surprisingly adaptable to internal changes/enhancements to the database engine.
When a program is loaded on a Windows Mobile device there is an option to install on RAM or a storage card if one is present. If you install the Compact Components to storage card does it get transferred to RAM before it runs so you end up using twice as much total space?
Windows Mobile supports Execute in Place (XIP) for Flash memory; therefore, the device is able to use the .NET Compact Framework runtime without copying the components to RAM – their executable code runs directly from the storage card.
I'm not able to access the sample Northwind.sdf from Visual C# 2008 Express edition; are Express editions supported? i'm able to open that file in Notepad though without any issues
If you’re not finding the Northwind.sdf database file included with the Visual C# 2008 Express Edition, you can download and install SQL Server Compact explicitly which should include the Northwind.sdf file.
Do you recommend or have you ever changed where SQLCE locates its temp database? (on storage card as opposed to the Windows\Temp folder?) we have encountered limitations in device memory, so we are exercising options to use external memory (SD cards, etc)
It’s perfectly legitimate to position the temp db on a storage card when you’re running into resource issues. You can specify the location of the temp db by including “Temp File Directory = location” in the connection string. If you need to change the size of the temp db you can include “Temp File Max Size = location” – The size is specified in megabytes
Example:
SqlCeConnection conn = new SqlCeConnection();
conn.ConnectionString = @"Data Source = \My Documents\Northwind.sdf; Temp File Directory = \Storage Card\TempDB; Temp File Max = 512";
conn.Open();
