A Look At SQL Server Compact Edition

  • I agree with steve and believe that it makes more sense to have support for stored procedures. Or atleast, there should be a provision to separete your TSQL code from the application code.

    Many of the MS docs that support the SQLCE version suggest to start with SQLCE and when the application, requirements and data grow, upgrade to other versions of SQL Servers. But this upgrade process would be a nightmare. It may not be just an upgrade, but a re-writing phase which includes rewriting all the TSQL code into Stored procedures.


  • Wait a minute.  Not having stored procedures is a very effective way to separate the application code from T-SQL.  Review the reasons for stored procedures.  Remember that we are talking about something that is single user.  Almost makes the security aspect moot.

    I would much rather have views than procedures.

    ATBCharles Kincaid

  • Charles,

    I am refering to the extra work involved while upgrading a single user SQLCE application to a multi-user SQL Server application. All the TSQL code that we write in our single-user SQLCE application will have to be converted into STORED PROCEDURES. Do you agree with this?

    I agree that views are very powerful. So are stored procedures or functions. The person responsible to design and write the database code might be deciding which part of the code/logic should go to a view, stored procedure or a function. They must have their own reasons for putting a certain part of the code to a stored procedure or going for a function to perform a certain operation.

    The point that I want to emphasis is the extra effort needed to create all the stored procedures, views and functions while upgrading to SQL Server Express, Enterprise or any other version.

    If there were support for stored procedures and views, the upgrade process would have been much easier. 


  • I agree with you, Jacob.  Originally this was device oriented.  The fact that they got it to run on the desktop is cool.  There had to some sacrifice to save space.  There is no CLR integration.

    I disagree with starting a desktop app with CE and migrating later.  I can't think of a business app that would use SQL that I would not write as multi-user from the ground up.  SQL Server Express to full version is much less of a tough upgrade.

    ATBCharles Kincaid

  • Nice article. Good for freshers also.

  • Robert O'Connell (7/10/2007)

    The biggest downside to using SQL Server Compact Edition is that it does not allow the creation of stored procedures. This seems to be Access with a different name and the same problems. If MS was serious about making this a viable product it should have been able to create stored procedures.

    Not having Stoed Procedures is a BIG show stopper. Too bad.

    As far as Access not having them... you can call Access Queries with ADO in the same way you would call a SS proc.

    Tom Garth
    Vertical Solutions[/url]

    "There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers
  • SQL CE is effective on Mobile devices. There you have less memory, smaller processors, and a cut down operating system.

    For desktop projects I would go with MDSE or SQL Server Express. This would address your scalability concerns. The fact that you can run CE on the desktop is very handy for debugging device applications.

    ATBCharles Kincaid

  • Not having SPs, views in CE means, I can not use them in a transaction, Is that right? May be not planning to update the existing SP but can I use it? Meaning a compiling code?

    What about Triggers?


  • Views and stored procedures have nothing to do with transactions. Then again transactions are not supported either. That is OK as the Compact Edition is single user anyway.

    ATBCharles Kincaid

  • I have some questions:

    1) The article promises a follow up article with answers for the questions raised at the end of the article. I don't find this follow up article. Is that article, or just the answers for the questions available somewhere/

    2) According to some documentation, SSCE yes supports transactions - simple, not distributed transactions. I saw some comments stating transactions are not supported. What is the case with transactions?

    3) I assume I can create multiple database in SSCE, maybe each database is just a separate files. Can I run a select with join between tables from different databases?

    4) According to the documentation, SSCE support multiple concurrent connections. the only limitation is connections must be local. Does SSCE support multiple local connections from different applications (aka .EXE)? I assume such multiple access might fail as multiple access to .MDB might fail (there is no real server to synchronize parallel access). Also what about multiple concurrent parallel access to an SSCE database from two different threads of the same executable?

    Thanks in advance!


Viewing 10 posts - 16 through 24 (of 24 total)

You must be logged in to reply to this topic. Login to reply