A Look At SQL Server Compact Edition

  • Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/jSebastian/3040.asp


  • I'm interested in some of the questions raised about SQL Server Compact Edition, I've been using SQL Server Express edition to process a subset of data from our main dataware house server to the local desktop. 

    However its not easy copy data (unlike previous versions which contained DTS - MSDE) , at the moment its a matter or creating a linked server and using SELECT/INSERT method, not a great way.

    Plus the restricted 4Gbyte of data, thou this doesnt apply to tempdb (does this exist in SQL Server CE??), thou you can create several databases splitting the data into each database and then create a view joining all the required data as a way around this.

    I do like the sound of SQL Server CE, great for those offline client applications.





  • hi friends after reading this topic i search one topic which may give yr ans and some new feature of sql server Mobile edition ( search on microsoft)

  • re: "does this exist in SQL Server CE??"

    Hi Ron,

    SQL Server CE does not allow splitting a database into multiple disk files. You can have only one database file per database.



  • oops, sorry I didnt explain. 

    create 2-3 databases, e.g. DatabaseA, DatabaseB & DatabaseC.

    partition the required data into 3 tables splitting the data equally into DatabaseA, DatabaseB & DatabaseC.  Create a View in DatabaseA which contains the tables created in DatabaseA, DatabaseB & DatabaseC.  This should solve the 4Gigabyte limit, a bit messy to say the least....bill remove that 4Gigabyte limit !!!




  • Hi Ron,

    that would not work with SQLCE.

    It does not support views.


  • 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.

  • One of the biggest difference between MS Access and SQLCE is that ACCESS is a standalone application and SQLCE is a set of IN-PROCESS dlls that will get embedded to your application. Further, it supports Mobile and Desktop platforms.

    Regarding Stored procedures: Steve Lasker, Program Manager of SQLCE at Microsoft has a detailed explanation in his blog (http://blogs.msdn.com/stevelasker/archive/2007/05/14/empowering-your-users-with-reference-data-and-knowledge.aspx)




  • Good article; looking forward to the answers to your ending questions. I really appreciate and see the potential for using it in the disconnected scenario.

  • I'll agree that stored procedures should be supported. Even if they need to be compiled in process and executed, they are important have building a n appplication, especially one that might need to scale to a larger version or even share some code.

  • Thanks for a very helpful article. I've been working with SQL CE for several months now, having been assigned the task of creating handheld apps for my company. I've read a lot about it, but I've never seen such a good summary in one place. Yes, the questions you've asked are important - I'm looking forward to your answers.

    My quick thoughts: 1) It just needs stored procedures to be perfect for it's niche (spelling?). They don't have have to be pre-compiled; it would just be nice to not have to generate queries every time you run something. 2) You wind up writing just a few classes to process connections and commands; once you do that, you're set. When you've created a SQL command, having a method that accepts the string and an open connection handles most activity.

    Looking forward to future articles.


    “Politicians are like diapers. They both need changing regularly and for the same reason.”

  • Personally, I understand the tradeoff of not supporting stored procs (coming from someone who loves TSQL and has been writing SPs for 20 years). Front-end code can be written in its place. What I would find hard to do without is views.

  • I've been using versions of this since Pocket PC 2.0.  Right now we make SQL CE 2.0 sit up, beg, roll over, and fetch.  (Good dog.)

    Not having stored procedures and views has really bugged most of the SQL developers around here.  Worse, not all of the SQL 2005 data types are supported.  This gave our data synchronizer fits at first but we fixed that.  Good design got us around not having procs and views.

    ATBCharles Kincaid

  • Charles, could you share more about good design with CE?

    I will have to rewrite a stand-alone, data entry heavy, laptop app for very occasionally connected users. This sounds like a good possibility for the back-end but where would all the data management be done?

  • Sure.

    Once you know that you are working with a subset of SQL you figure out what is NOT supported.  You make a list of those things and distribute that list.  Soon you come up with a list of "SHALL NOT"s for your developers and you put them up as posters.

    Next you test everything.  Over, and over, and over.  You hand the device to your six year old who had a bad day at school and say, "have fun".  You go insult your testing staff, turn off their air conditioning, and tell them how proud the developers are of the new release candidate.  My wife is blind, we hand her a device and she pokes things at random.

    Now that you know the testing environment you design with a small memory foot print in mind.  "In order to perform this function what is the least amout of data that is required?"  We are so used to SQL handling thousands of transactions per second it takes a new mind set to adjust to a version of SQL that only handles a few hundred transactions per minute.  Don't think of the device as a small computer but, rather, an advanced calculator.

    It's all mind set.  On the device you are single user, not multi-user.  In a traditional app we are always aware that the contents of the tables are ever changing.  If I'm not live (working on local database and then sync.) then there is no one else changing my data.  If I asked a question I remember the result and don't keep asking the same question over again.

    Solid design starts with "paper testing".  Chart and diagram your processes.  Then KISS.  Keep It So Simple.  It's not your code that will impress the customer.  It's how fast your app is out in the field.

    By the way its fun taking a sales order on a device that looks like a side arm for the Borg.  "Mr deli manager are you sure you only want two cases of this product that's on special?"  Then you point right at his chest and pull the trigger a couple of times.  Watch his expression as he sees the beam across his heart.  "Six cases did you say?  According to the database you get a quantity discount starting at eight cases."  Click click.   "An even dozen then?  Very well."

    ATBCharles Kincaid

Viewing 15 posts - 1 through 15 (of 24 total)

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