Data Engineering Technical Blog

What is RDBMS? Relational Data Base Management Systems (RDBMS) are database management systems that maintain data records and indices in tables. Relationships may be created and maintained across and among the data and tables. In a relational database, relationships between data items are expressed by means of tables. Interdependencies among these tables are expressed by data values rather than by pointers. This allows a high degree of data independence. An RDBMS has the capability to recombine the data items from different files, providing powerful tools for data usage. What is normalization? Database normalization is a data design and organization process applied to data structures based on rules that help build relational databases. In relational database design, the process of organizing data to minimize redundancy. Normalization usually involves dividing a database into two or more tables and defining relationships between the tables. The objective is to isolate data so that a...

Introduction Memory optimized tables allow you to keep the data in memory and return the result fast whenever required. Since the data is stored on RAM so the disk io is 0 for memory optimized tables. Steps to create memory optimized tables Step1  Create a database and add a filegroup and file and set memory optimize data on as below. CREATE DATABASE imoDB   GO   --------------------------------------  -- create database with a memory-optimized filegroup and a container.  ALTER DATABASE imoDB   ADD FILEGROUP imoDB _mod CONTAINS MEMORY_OPTIMIZED_DATA   ALTER DATABASE imoDB   ADD FILE ( name = 'memoltp_mod1' , filename= 'c:\data\imoltp_mod1' ) TO FILEGROUP imoDB _mod    ALTER DATABASE imoDB   SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT= ON   GO Step 2  Using above database you can use the below syntax to create a memory optimized table. USE imoDB GO CREATE ...

The memory optimized tables were first introduced with SQL 2014. The basic concept behind the introduction of memory optimized tables was to equip the sql server with 100x processing capabilities for some specific requirements (tables). Before recommending memory optimized table you must understand about the memory optimized tables and how the data is stored, what type of indexes can be created on IM (In Memory) tables. There are basically two types of IM tables i.e schema durable and schema and data durable. To learn more about In Memory OLTP you can go to the below MSDN Link. https://docs.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/memory-optimized-tables I have done few test with the processing capabilities of the IM tables and compared the same with disk based tables which are mentioned below. I had loaded around 18000000 rows and tried to query data against that. to return 10000 rows based on some parameter condition the disk based table took approxim...