I would say the top notch feature of SQL Server 2014 is In Memory OLTP concept. Do you know how we view an image or video stored in our hard disk? The image to be viewed is brought to the Main Memory from the hard disk through the software service and it helps us to see the image. The same happens in case of SQL Server too. Generally, the data stored in SQL Server is in the form of data tables called disk based tables (The disk based tables are our regular tables which we were using from 2000 till now). Consider if we run a query against the disk based tables using the SQL Server Management Studio, the query gets executed and the result set is produced to us through the main memory. In other words, the result set lies in the memory as compared to the original data from a disk based table stored in the secondary storage device. Technically there is no storage happens at main memory level. Hope this reminds us all of a concept we studied in our school days that Main memory is meant for faster processing and secondary storage is for storing the data, am glad it didn’t change. One obvious question hits our mind now; with all the advancement in DB applications is it possible to store data in main memory? Yes we can. It’s called Memory Optimized tables, a new feature built by Microsoft for SQL Server 2014 version

Hence we define Memory Optimized tables as the tables which store the data in the memory all time. Microsoft has introduced a new In Memory OLTP engine to work with Memory optimized tables. One awesome feature of memory optimized tables is there can be no locks issued to a memory optimized table which ensures no waiting because of blocking and data is available at all time. Having the data staged in the memory and no blocking of data through any locks significantly ensures faster processing and easier accessing of data by applications.

Did I not realize one thing that data in a memory will be lost in the event of power failure, so what happens if the server gets shut down or a system crash happens? Yes of course the data stored in the memory will be lost but it can be recovered when the server is up and running through Checkpoints which keeps a local backup of the data in memory optimized table as a file group in the hard disk. Memory optimized table does comes with lot of pros and few cons which I wouldn’t be able to summarize in a single post. I would recommend you to go through the White Paper Microsoft released about In Memory OLTP over this weekend and remember to utilize this feature when we get to work with 2014.Here is the link for the White paper.

I would like to finish up this post with the syntax to create a Memory Optimized table as below,

Create Table TableName(

Column1 Datatype,

Column2 Datatype,

Column3 Datatype

) (WITH MEMORY_OPTIMIZED=ON)

                                                                                                                      -Junaith Haja