Memory optimized OLTP database engine for SQL Server 2014 CPT2

Memory optimized OLTP database engine for SQL Server

Memory-optimized OLTP database engine (In-Memory OLTP) support tables which reside in memory ( Memory-Optimized Tables) and Natively Compiled Stored Procedures ( Create Procedure code translated into native machine code and compiled into a DLL and loaded into memory )

SQL Server 2014

Advantages:

Memory-Optimized Tables

· High Performance increase: Unlike the usual Disk-based tables, all the data is stored in memory, all the time. Pages do not need to be read into cache from disk when the memory-optimized tables are accessed.

· For memory-optimized tables, there are no locks acquired, and thus no waiting because of blocking.

· Concurrency improvements: When accessing memory-optimized tables, SQL Server implements an optimistic multi-version concurrency control.

· Hash Indexes on memory-optimized tables uses Bw-tree structure and resides in memory.

Native stored procedures:

· Procedure loaded into memory in native code consisting of processor instructions that can be executed directly by the CPU, without the need for further compilation or interpretation. Syntax:

Eg:

Memory-Optimized Tables

Create table dbo.T1 (
C1 int not null primary key NONCLUSTERED HASH WITH (BUCKET_COUNT=2000000)
) with (MEMORY_OPTIMIZED=ON)

Natively Compiled Stored Procedures

Create procedure dbo.p1 WITH NATIVE_COMPIALATION, SCHEMABINDING, EXECUTE AS OWNER
AS
BEGIN ATOMIC with (TRANSACTION ISOLATION LEVEL=snapshot, LANGUAGE=N'us_english')
Select 1
END

Comments

SQL Server script Error: Insufficient result space to convert uniqueidentifier value to char.

Error: G etting below error while trying to copy data from one table to another: Msg 8170, Level 16, State 2, Line 1004 Insufficient result space to convert uniqueidentifier value to char. Fix: UserID column which would be having Unique identifier data require more space (36 character). Size is specified as 15 here. To fix it just specify 36 as varchar length or specify Uniqueidentifier as data type for UserID column.

While executing Select query to pull data from a different server, SQL Server is showing following error:

Msg 7202, Level 11, State 2, Line 1 Could not find server 'LAPTOP-09O6NE3K\SQLEXPRESS03' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers. Completion time: 2024-02-05T17:18:24.6869756+05:30 Solution: Step1: Check if the server is available in the sys.servers table SELECT name from sys.servers Step2: If no, add the new server to the sys.servers table EXEC sp_addlinkedserver @server = 'server name' Eg" EXEC sp_addlinkedserver @server = 'LAPTOP-09O6NE3K\SQLEXP RESS03' Step3: Recheck if the new server name is added to the sys.servers table. If yes, try then rerunning the select query which throwed the error. Now it should pull data from the table in the new server successfully.

Top 10 essential SQL skills for data analysts, data engineers, and data scientists Photo by Carlos Muza on Unsplash It doesn’t matter how good you are at creating visualizations, extracting insights, or building complicated models; you need data in order to do those things. SQL enters the picture when it comes to extracting data from the database in order to work with it. Mastering the commonly used SQL concepts is essential to work effectively as a data analyst, data engineer, and data scientist in today's world. The following are the top 10 skills for mastering SQL: 1. Knowing SQL Fundamentals: Understanding the following keywords is essential to write common DML queries: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, WINDOW\RANK FUNCTIONS. 2. Understanding different real-world scenarios: Analyzing and understanding multiple domains and how to use SQL, like writing complex queries, manipulating data, pulling reports, and identifying patterns and t...

TSQL Masters

Search This Blog