Advantages and Disadvantages of SQLite.NET and SQL

Question

I have used SQLite.NET many times. It always worked fine but I have a friend that is really pestering me that I should use instead SQL Server Compact so I stayed fully in Microsoft environment.

Now, I never worked with Compact, and he tells me it works fine for him, but seeing that .MDF extension gives me the creeps. No kidding. Last thing I want is my application relying on an Access database.

Since I have never really worked with it, I am asking if someone here knows it to vouche for it, and if someone can tell me the main differences between them, mostly speed, file size, reliability, and features. I know it is a lot to ask but I'd appreciate if someone could help me.

Answer 1

Access was MDB (I believe it's changed for recent versions) - SQL Server full version defaults to MDF.

I confess I decided though that compact was just too much hassle from the docs so went with SQLite.

Answer 2

One thing is that the SQL Server Compact can only have one process accessing the MDF at a time.

Answer 3

I do not SQL Server compact edition. It is a accessible by single thread , does not have any good features. you have to write query every time for everything. moreover as far as i know it is not indexed. correct me, if i am wrong on this point. But, at the end it is very slow for any desktop task.

SQL Server Compact will run under medium trust under ASP.NET 4, and supports both x64 and x86 platforms. It is limited to max 256 concurrent connections. It is file based, and not quite as robust as SQL Server, and does not support recovery to a point in time.