How SQL Server stores data?

SQL Server stores data in heaps or b-tree structures. Heaps are unordered set and balanced trees are ordered by their keys. Heaps and b-tree use collection of pages within the structure and it's called allocation units. IN_ROW_DATA -> contains all data. LOB_DATA -> structure for large objects used to stored in xml, varchar(max), nvarchar(max), varbinary(max) … Continue reading How SQL Server stores data?

Memory-optimized Logging

In a previous post I talked about transaction log works, and what about using a memory-optimized table? SQL Server has the feature Memory-Optimized Objects to improve performance. In-memory nonclustered indexes are implemented using a data structure called a Bw-Tree. A Bw-Tree is a lock and latch-free variation of a B-Tree. In-memory architecture: To enable an … Continue reading Memory-optimized Logging

Data types

Data types and Precedence of convert types SQL Server associates columns, expressions, variables, and parameters with data types. Data types determine what kind of data can be stored in the field: Integers, characters, dates, money, binary strings, etc. SQL Server supplies several built-in data types but you can also define custom types Built-in data types … Continue reading Data types

Storage – Part II

Logical Structures How, where, when you need to think in data storage? Well, this is the first step after you modeling your database and most companies do not think about it, I have saw companies with a large data into one disk or small data separated in the wrong way, but what is the best to do? We have … Continue reading Storage – Part II

Storage – Part I

Pages and Extends Architecture The page is the fundamental unit of data storage in SQL Server. An extent is a collection of eight physically contiguous pages. Extents help efficiently manage pages. Understanding the architecture of pages and extents is important for designing and developing databases that perform efficiently. The fundamental unit of data storage in SQL … Continue reading Storage – Part I