5

Is there a way to make SQL Server to store a table with 10 attributes and 10 rows like this on memory?

a1   a2   a3  a4  a5  a6  a7  a8  a9 a10
----------------------------------------
138 498  365 345 500 473 498 125 134 800
448 498  362 348 500 463 498 625 165 700
468 498  625 329 500 435 498 625 345 600
437 701  365 326 500 453 498 625 645 500
438 498  326 329 500 438 498 625 745 400
439 499  626 329 500 438 498 525 685 300
440 500  327 328 500 423 498 627 655 200
444 214  331 334 500 428 498 125 615 100
448 498  362 348 500 463 498 225 165 700
468 498  625 329 500 435 498 425 345 600

I was thinking to make somekind of vector and make the table a single row (instead of 10 attributes by 10 rows make a vector with size 100. Is there a way to do this?

You may ask why I want to do this, It is because I would like to make some calculus all in memory avoiding writing or using disk, and then access the memory or vector in C++ or .NET (maybe C#?)

I was thinking in creating a UDF that can convert the table into an array, but would like this process be all internal.

So I want the table in memory to avoid exporting and importing, also avoiding writing to disk...

  • Is there a way to execute a UDF, Stored proc that would do this?.
  • Is there a way to store this table in contiguous memory?
edgarmtze
  • 219
  • 1
  • 6

1 Answers1

6

In prior versions before SQL Server 2005, DBCC PINTABLE was used to plug the table in memory but it had some inherent issues with data corruption. While the command DBCC PINTABLE still exists, it simply does nothing.

I think you are making it too complicated. SQL Server will read the data from disk only if it doesn't already exist in memory. Once it is in memory, it will stay there until it is NOT being used for sometime or it gives way for reading something else. If you have enough memory (this is completely relative) then your table may already be in memory. I have simplified this a bit here. It uses LRU-k algorithm to keep the pages in cache. 10 rows and 10 columns of smallint/int data will fit in one page i.e 8KB and you should stop pursuing your initial plan.

Ref: http://msdn.microsoft.com/en-us/library/ms191475.aspx

One the page is in cache, even if you update the data it won't be written to disk immediately and there is an asynchronous process (CHECKPOINT, Lazy writer) to flush this data to disk later on. You could update the data like 100 times and it is possible that SQL Server writes to disk only once. It depends on if you have messed with the recovery internal flag, amount of data changes and stuff like that. I guess at this time its too much info.

Sankar Reddy
  • 1,374
  • 8
  • 8
  • Ok, I understand, One question... Is it possible to exec a stored proc, or an UDF and then put this table in memory so let's say c++ or any language within .NET: 1.-reads that memory 2.- Does some computing with dada 3.-The data is stored again in that memory... Of course that memory will be in one page as you suggest... – edgarmtze Jun 05 '11 at 14:58
  • How would be the code to run the SQL command and pull it into memory as a Datatable object or something equivalent??. – edgarmtze Jun 05 '11 at 15:07
  • select a1, a2, a3, a4, a5, a6, a7, a8, a9, a10 from TableName – Sankar Reddy Jun 05 '11 at 15:10
  • When u execute a stored proc, the SQL Engine will work for you and put that table in memory if it is NOT already there. Again when u make an update, SQL server will put that data in memory first and will stay there until a CHECKPOINT happens/lazy writer kicks in. – Sankar Reddy Jun 05 '11 at 15:12
  • yeah but when I was thinking in pull it in memory, I meant to know in which area, or in which page, the table is, is it possible to know the adrres or pass that to .NET language so (c++,c#) read the table in memory? – edgarmtze Jun 05 '11 at 15:13
  • something like: INSERT INTO TableE ( a1,...,a10) Values(@a1,..,@a10, 0x0).... so the initial value 0x0 would allow to recognize where the table is? – edgarmtze Jun 05 '11 at 15:18