2

Inadvertently while I was finding a way for a quick test of my first SQLite user defined function without having to create a SQLite database, I've found this short SQLite connection string which helped me test the function quickly (happy with it):

SQLiteConnection con = new SQLiteConnection("Data Source = :memory:");

At least this is for testing purpose (I guess so). I wonder what else we can do with it? Can I create some database on fly and save as a file somewhere?

This is known as in-memory database and it's commonly used for volatile memory devices which there is no need to save the data after the device is off and it is suitable for real time processing via networks. That's all my knowledge about some usages of it, but I've really not experienced with any project relating to it.

If you have something to share (with your experience), please share it here, I would highly appreciate it. Thanks!

King King
  • 61,710
  • 16
  • 105
  • 130
  • This reminds me a bit of using temp tables in MSSQL. I would think that the speed of searching an in-memory table would be quite advantageous provided you have the memory/disk space to support it. – Brian Apr 23 '13 at 18:07
  • I don't know how temp tables work but I guess they are created some where on the disk not in-memory (RAM) table. This in-memory stuff of course outperforms the disk-based ones. – King King Apr 23 '13 at 18:24
  • I thought [this](http://www.sqlite.org/inmemorydb.html) was a pretty good read about this topic. And just an FYI, a [temp table](http://stackoverflow.com/questions/7560860/are-tables-created-with-create-temporary-table-in-memory-or-on-disk) can be created _either_ in memory or on the local disk. +1, by the way. – Brian Apr 23 '13 at 18:29
  • @Brian, for MsSql comparisson, these should resemble table variables as `DECLARE @myTable TABLE (...);`, since they will never be persisted in disk and temp tables as `CREATE TABLE #myTable(...)` eventually will. – Gerardo Lima Oct 24 '13 at 09:31

2 Answers2

1

For a project I had to compare two SQLite databases.

I used the :memory: data source as "main" (this is the default alias for a database) for temp tables that assisted in comparing the databases, and then attached the two databases by giving them an alias. This way I had access to both databases and a temp database all from the same SQLiteConnection.

I don't think SQLite offers any way to save the memory database as a file, but if you wanted to you could use the same approach: attach the empty database and copy the data using 

CREATE TABLE attachedDb.TableName AS SELECT * FROM main.TableName
C.Evenhuis
  • 25,996
  • 2
  • 58
  • 72
0

I find in-memory-databases very useful for running automatic testing against them. They are running faster than filebased databases, will automatically be discarded at the end of testing and don't clutter my harddrive.

Sam
  • 28,421
  • 49
  • 167
  • 247