How does sql store data

So, in an IAM Page you'll get the number of pages used in your database. Do you want to see what pages are used for your table in which you've stored your record? Run the following DBCC command. Now let's check where our record Abhishek is stored in the Data Page For that run the following DBCC command. Let's move further. So, let's execute the following statement to see the content in our Data Page. So we've got our contents of the Data Page.

Page Header : This gives you the details of the type of page, nextpage and previous page id, free space and so on as marked in the following image.

Actual Data : The Actual Data that we insert into our object is stored in this section. If you remember, we inserted 1 record with employee named "Abhishek".

It contains information such as how much space is free in the body, how many records are stored in the body, the object to which the page belongs and, in an index, the pages that precede and succeed it. At the very end of the body is a section known as the record offset array , which is an array of two-byte values that SQL Server reads in reverse from the very end of the page.

The header contains a field that defines the number of slots that are present in the record offset array, and thus how many two-byte values SQL Server can read. Each slot in the record offset array points to a byte index in the page where a record begins. The record offset array dictates the physical order of the records.

As such, the very last record on the page, logically, may very well be the first record, physically. The file is just one big array of pages, with the very first page starting at byte index 0, the next one at byte index , the third one at byte index , and so on.

Having read the bytes of the page, SQL Server can then read entry Z in the record offset array to find out where the record bytes are stored in the body. The trace flag activates at the connection level, so enabling it in one connection will not affect any other connections to the server.

Likewise, as soon as the connection closes, the trace flag will no longer have any effect. Database is the name of the database whose page we want to examine. Next, the FileID of the file we want to examine; for most databases this will be 1 , as there will only be a single data file.

Execute Listing 2 within a specific database to reveal a list of all data files for that database, including their FileID s. Next, the PageID of the page we wish to examine. This can be any valid PageID in the database.

After the header, we see each record listed, one by one. The output of each record consists of the raw bytes Memory Dump , followed by each of the column values Slot 0 Column Note that the column values also detail how many physical bytes they take up on disk, making it easier for you to correlate the value with the raw byte output.

We specify the name of the database and the name of the object for which we wish to view the pages. Finally, we can filter the output to just a certain index; 0 indicates a heap, while 1 is the clustered index.

If we use -1 for the IndexID , we get a list of all pages belonging to any index related to the specified object. Listing 4 examines the Person. Person table in the SQL Server R2 AdventureWorks database, and is followed by the first five rows of the results your output may differ, depending on the release.

There are a couple of interesting columns here. The PageType column details the type of page. PageType 2 is an index page and PageType 1 is a data page. The first two columns show the file ID as well as the page ID of each of those pages.

Person table. If a table contains a clustered index, then that table is stored in the same way as an index.

A special type of page called an index allocation map IAM tracks which pages belong to which object. One important fact to remember is that a heap guarantees no order for the records within each page. SQL Server inserts a new record wherever it wants, usually on an existing page with plenty of space, or on a newly allocated page.

Compared to indexes, heaps are rather simple in terms of maintenance, as there is no physical order to maintain. In order to understand why, we need to discuss forwarded records. Unlike in an index, a heap has no key that uniquely identifies a given record. For example points to the third slot in the 17th page both starting at index 0 in the first file which starts at index 1. Imagine that the pointer to record exists in 20 different places but that, due perhaps to an update to a column value, SQL Server has to move the record from page 16 as there is no longer space for it.

This presents an interesting performance problem. If SQL Server simply moves the record to a new physical location, it will have to update that physical pointer in 20 different locations, which is a lot of work. Instead, it copies the record to a new page and converts the original record into a forwarding stub , a small record taking up just 9 bytes storing a physical pointer to the new record.

The existing 20 physical pointers will read the forwarding stub, allowing SQL Server to find the wanted data. This technique makes updates simpler and faster, at the considerable cost of an extra lookup for reads. Listing 6 shows how to query the sys. Listing 6: Using sys. However, indexes are fundamentally different from heaps in terms of their organization and structure. Indexes, clustered as well as non-clustered, store data pages in a guaranteed logical order, according to the defined index key physically, SQL Server may store the pages out of order.

Structurally, non-clustered and clustered indexes are the same. Both store index pages in a structure known as a b-tree. It's exactly what I was looking for honestly. Not every post needs to be code you can test. Add a comment. Active Oldest Votes. Here is a subset of it, relating to Data Storage: Data storage The main unit of data storage is a database, which is a collection of tables with typed columns. Improve this answer.

Rakib 3 3 silver badges 14 14 bronze badges. Erich Mirabal Erich Mirabal 9, 3 3 gold badges 31 31 silver badges 38 38 bronze badges. Not sure what else you're looking for. Hope that helps. Garrett Garrett 1, 2 2 gold badges 16 16 silver badges 23 23 bronze badges. Just noticed your "sqlserver" tag, so some of the stuff above can be ignored. Andrew Hare Andrew Hare k 68 68 gold badges silver badges bronze badges. Ali Serapth Serapth 6, 3 3 gold badges 28 28 silver badges 38 38 bronze badges.

The Overflow Blog. Podcast Explaining the semiconductor shortage, and how it might end. Does ES6 make JavaScript frameworks obsolete? Featured on Meta. Now live: A fully responsive profile. Visit chat. Linked Off we go:. We now have a table.

We just met. You can start by buying us a drink first. This command is totally safe to run — it just lists out where SQL Server is storing your data. These pages are the smallest unit of storage both in memory and on disk. When we write the very first row into a table, SQL Server allocates an 8KB page to store that row — and maybe a few more rows, depending on the size of our data.

If we had bigger rows, they might take up multiple pages even just to store one row. Each page is dedicated to just one table. SQL Server would check to see what page the dbo.

Friends table is on, then read our entire 8KB page from disk, and cache that 8KB page in memory. What happens if we change a data page? For example, if we issue the following command, what happens:.

The log file is a sequential record of what we did to the data. The log file exists for SQL Server, not for you. The log file must get hardened to disk before SQL Server says the transaction is committed. SQL Server can keep the same data page in memory for a while, and then flush it out to disk later — as long as the log file was written.

SQL uses the log file to reconcile the state of the data file, deciding which transactions should be applied to the data file and which ones should be rolled back. Log files are written to sequentially, start to finish. However, these are the exception rather than the norm.

Data files, on the other hand, are a jumbled mess of stuff. The more memory your server has, the less data file reads happen — SQL Server will cache the data pages in memory and just work off that cache rather than reading over and over. Want to learn more? Sorry about that guys! Brent does a great job explaining the basics of data storage in the video. I recommend all developers with any concern about the security of data in SQL Server check out this video.

Well done Brent! If writing to logs is done in a sequential manner is there much benefit to be had in using SSDs for log files? How many databases do you have on the server, and are they all active at the same time? I am grateful to Brent for trying and featuring a preview version of this tool — and happy to announce the first public version is out!