Is it possible to create fast (in-memory, non-ACID, etc) tables/databases in SQL Server?

In Sqlite, there's an option to create an in-memory database, and another to not wait for things to be written to the filesystem, and to p开发者_如何学运维ut the journal in memory or disable it. Are there any settings like this for SQL Server?

My use case is storage for data that should persist for about a day in normal use, but wouldn't be a big deal if it was lost. I would use something like memcached for it, but I want to be able to control the cache time, not just hope I have enough memory.

---

No.

tempdb has a bit less logging than regular databases as it doesn't have to support the "D" in acid and redo of transactions but that's about it.

---

Yes as of MSSQL 2014.

There is a new feature in MSSQL 2014 named In-Memory OLTP.

For a detailed feature introduction: http://technet.microsoft.com/en-us/library/dn133186(v=sql.120).aspx

---

Not really. You can do something like this through SQL Server by implementing a custom solution through the SQLCLR. You can use temp tables or table variables too, but these will still write to disk. You can improve performance (by reducing blocking) but break consistency by using different ISOLATION LEVEL such as READ_UNCOMMITTED.

In brief if you really want what you ask, SQLCLR is the solution.

---

You could store the table on a ramdisk. That way it would always be in memory.

However, I would first try a normal table. SQL Server does a pretty good job about caching tables in memory.

---

Table variables:

DECLARE @name TABLE (id int identity(1,1), ...);

Table variables are kept in memory and not logged. Under memory pressure, they can spill to tempdb. However, because they are restricted to the scope of a batch execution, it would be hard (but not impossible) to store data in them for 'about a day'. I would definitely not recommend a in-memory non-acid solution based on SQL Server table variables. But, as Martin already pointed out, real tables in tempdb are a viable alternative to improve latency. You can achieve similar results on durable DBs too, with proper transaction management (batch commit) and file placement (dedicated high throughput log disk).