What are the limitations to SQL Server Compact? (Or - how does one choose a database to use on MS platforms?)

The application I want to build using MS Visual C# Express (I'm willing to upgrade to Standard if that becomes required) that needs a database.

I was all psyched about the SQL Server Compact - because I don't want the folks who would be installing my application on their computers to have to install the whole of SQL Server or something like that. I want this to be as easy as possible for the end user to install.

So I was all psyched until it seems that there are limitations to what I can do with the columns in my tables. I created a new database, created a table and when I went to create columns it seems that there isn't a "text" datatype - just something called "ntext" that seems to be limited to 255 characters. "int" seems to be limited to 4 (I wanted 11). And there doesn't seem to be an "auto_increment" feature.

Are these the real limitations I would have to live with? (Or is it because I'm using "Express" and not "Standard"). If these are the real limitations, what are my other database options that meet my requirements? (easy installation for user being the biggie - I'm assuming that my end user is just an average user of computers and if it's complicated would get frustrated with my application)

-Adeena

PS: I also want my database data to be encrypted to the end user. I don't want them to be able to access the database tables directly.

PPS. I did read: http://www.microsoft.com/Sqlserver/2005/en/us/compact.aspx and didn't see a discussion on these particular limitations

I'm not sure about encryption, but you'll probably find this link helpful:
http://msdn.microsoft.com/en-us/library/ms171955.aspx

As for the rest of it:
"Text" and "auto_increment" remind me of Access. SQL Server Compact is supposed to be upgrade compatible to the server editions of SQL Server, in that queries and tables used in your compact database should transfer to a full database without modification. With that in mind, you should first look at the SQL Server types and names rather than Access names: in this case namely varchar(max), bigint, and identity columns.

Unfortunately, you'll notice this fails with respect to varchar(max), because Compact Edition doesn't yet have the varchar(max) type. Hopefully they'll fix that soon. However, the ntext type you were looking at supports many more than 255 bytes: 2³⁰ in fact, which amounts to more than 500 million characters.

Finally, bigint uses 8 bytes for storage. You asked for 11. However, I think you may be confused here that the storage size indicates the number of decimal digits available. This is definitely NOT the case. 8 bytes of storage allows for values up to 2⁶⁴, which will accomodate many more than 11 digits. If you have that many items you probably want a server-class database anyway. If you really want to think in terms of digits, there is a numeric type provided as well.

A few, hopefully helpful comments:

1st - do not use SQLite unless you like having to have the entire database locked during writes (http://www.sqlite.org/faq.html#q6) and perhaps more importantly in a .Net application it is NOT thread safe or more to the point it must be recompiled to support threads (http://www.sqlite.org/faq.html#q6)

As an alternate for my current project I looked at Scimore DB (they have an embedded version with ADO.Net provider: http://www.scimore.com/products/embedded.aspx) but I needed to use LINQ To SQL as an O/RM so I had to use Sql Server CE.

The auto increment (if you are referring to automatic key incrementing) is what it always has been - example table:

-- Table Users

CREATE TABLE Tests (
    Id       **int IDENTITY(1,1) PRIMARY KEY NOT NULL,**
    TestName     nvarchar(100) NOT NULL,
    TimeStamp    datetime NOT NULL
)
GO

As far as the text size I think that was answered.

Here is a link to information on encryption from microsoft technet: (http://technet.microsoft.com/en-us/library/ms171955.aspx)

Hope this helps a bit....

Had to chime in on two factors:

1. I use Sql Compact a lot and its great for what it works for -- a single user, embedded, database, with a single file data store. It has all the SQL goodness and transactions. It hadles parallellism well enough for me. Notice that few of the naysayers on this page use the product regularly. Don't use it on a server, that's not what its for. Many of my customers don't even know the file is a "database", that is just an implementation issue.
2. You want to encrypt the data from your users -- presumably so they can only view it from your program. This simply isn't going to happen. If your program can decrypt the data, then you have to store the key somewhere, and a sufficently dedicated attacker will find it, period.

You may be able to hide the key well enough that the effort to recover it isn't worth the value of the information. Windows has some neat machine and user local encryption routines to help. But if your design has a strong requirement that a user never find data you have hidden on their computer (but your program will) you need to redesign -- that guarentee simply cannot be accomplished.

SQL CE is a puzzle to me. Did we really need yet another different SQL database platform? And it's the third in the last several years targeted at mobile platforms from MS ... I wouldn't have a lot of confidence that it will be the final one. It doesn't share much if any technology with SQL Server - it's a new one from scratch as far as I can tell.

I've tried it, and then been more successful with both SQLite and Codebase.

EDIT: Here is a list of the (many) differences.

ntext supports very large text data (see MSDN - this is for Compact 4.0, but the same applies to 3.5 for the data types you are mentioning).

int is a numeric data type, so the size of 4 means 4 bytes/32 bits of storage (–2,147,483,648 to 2,147,483,647). If you intend to store 11 bytes of data in a single column, use the varbinary type with a size of 11.

Automatically incrementing columns in the SQL Server world are done using the IDENTITY keyword. This causes the value of the column to be automatically determined by SQL Server when inserting data into a row, preventing collisions with any other rows.

You can also set a password or encrypt the database when creating it in SQL Compact to prevent users from directly accessing your application. See Securing Databases on MSDN.

All of the items you mention above are not really limitations, so much as they are understanding how to use SQL Server.

Having said that, there are some limitations to SQL Compact.

• No support for NVARCHAR(MAX)
  • NTEXT works just fine for this
• No support for VIEWs or PROCEDUREs
  • This is what I see as the primary limitation