Thursday, February 11, 2010

SQL Server Compact vs. SQLLite

Been working on SQL Server compact 3.5 sp1 for quite some time. Here are a couple of limitations I came across:
  • 4GB ... well I knew of it to begin with
  • Linq To SQL No designer support. You can use sqlmetal to generate the dbml file for you though so not a big issue. 
  • Poor Designer. Really this is what i found most annoying. You cannot even reorder columns (lets not get into the "Its relational and doesn't matter debate... I like my key's on top... enough said). SDF Viewer exists ... but why buy another software?
  • Deployment failed. I do not know why. Even when I put all the dll files from the installation into the client machine it failed to work. Even a simple test application purely for connecting does not work. I have no idea why. So I uninstalled my Sql Server compact 3.5 sp1 and installed it again on the development machine. Now even SSMS will not connect to it. Error : "Could not load file or assembly 'Microsoft.SqlServerCe.Client, PublicKeyToken .... ". After trying to install everything from http://www.microsoft.com/Sqlserver/2005/en/us/compact-downloads.aspx it still will not work. Ah well
UPDATE:
  • didn't know back then ... right now I think it was probably because of not configuring the DbProviderFactories ... I did it for sqllite. There might be something similar required for SQL Server Compact

    Enter SQLite for the rescue : http://sqlite.phxsoftware.com/  Especially check out their video : http://sqlite.phxsoftware.com/sqlite.wmv

    One word : AWESOME . Script tables , modify keys, Reorder columns and a whole lot more! Even if the designer does not work in the next version of VS (for a while it will not ... but I will upgrade VS as soon as it comes out stable!) .. I know (and love) python ... so creating my own little script lib to fix it will not be a problem.
    Update : An awesome third party open source tool for modification of DB Files: http://sqlitebrowser.sourceforge.net/ Instead of going thorough the headache of providing a data migration tool with EVERY new iteration of my software I can just point my users to this to selectively migrate the data from the old db file as per their demand :) 

    Not to mention indices / triggers / 2TB official tested.

    Maybe : SQL Listening http://www.sqlitening.com/ (But i will hold on to my dear SQL Server knowledge)

    Enjoy!