Alex Rivera | Logout

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

Asked 2009-01-02T17:43:36.287
55

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

sql-server database visual-studio-2008 sql-server-ce
Edit
Report

3 Answers

5

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
answered 2011-05-20T14:45:32.850
2

I must also chime in here with VistaDB as an alternative to SQL CE.

VistaDB does support encryption (Blowfish), it also supports TEXT as well as NTEXT (including FTS indexes on them).

And yes the post above is correct in that you have to look at the SQL Server types to really match them up, VistaDB also uses the SQL Server types (we actually support more than SQL CE does; only missing XML).

To see other comparisons between VistaDB and SQL CE visit the comparison page. Also see the SO thread on Advantages of VistaDB for more information.

(Full disclosure - I am the owner of VistaDB so I may be biased)

answered 2009-05-16T05:32:27.790
1

There are constraints... Joel seems to have addressed the details. SQL CE is really geared for mobile development. Most of the "embedded" database solutions have similar constraints. Check out

answered 2009-01-02T17:49:53.810

Your Answer