SQL Server Storage units to Manage SQL Database

A SQL Server database makes use of two types of files

. Data is first stored in a transaction log, which is one type of file, and then subsequently written to the second type, the database file. When a user enters a transaction, the data she inserts, updates, or deletes is sent along to the RDBMS engine, which records that line of data in the transaction log.Primary Data FilesThe primary data file contains the startup information for the database and points to the other files in the database. User data and objects can be stored in this file or in secondary data files. Every database has one primary data file. The recommended file name extension for primary data files is .mdf. There can only be one primary data file.Secondary Data FilesSecondary data files are optional, user-defined, data files. Secondary files can be used to spread data across multiple disks by putting each file on a different disk drive. Additionally, if a database exceeds the maximum size for a single Windows file, you can use secondary data files so the database can continue to grow. The recommended extension for secondary data files is .ndf.Transaction LogsLog files hold all the log information that can be used to recover the database. There must be at least one log file for each database. The recommended extension for log files is .ldf.Let's understand, How is the Data Stored inside a SQL Database?Primary data files:The primary data file is the starting point of the database and points to the other files in the database.Secondary data files:Secondary data files make up all the data files, other than the primary data file.Log files:Log files hold all the log information that is used to recover the database.Data File PagesPages in a SQL Server data file are numbered sequentially, starting with zero (0) for the first page in the file. Each file in a database has a unique file ID number. To uniquely identify a page in a database, both the file ID and the page number are required.Pages in a SQL Server data file are numbered sequentially, starting with zero (0) for the first page in the file. Each file in a database has a unique file ID number. To uniquely identify a page in a database, both the file ID and the page number are required.In SQL Server, the page size is 8 KB. This means SQL Server databases have 128 pages per megabyte. Each page begins with a 96-byte header that is used to store system information about the page. This information includes the page number, page type, the amount of free space on the page, and the allocation unit ID of the object that owns the page.Now Lets' discuss about page types used in the data files of a SQL Server database.Page type ============= DataData rows with all data, except text, ntext, image, nvarchar(max), varchar(max), varbinary(max), and xml data, when text in row is set to ON. IndexIndex entries.Text/ImageLarge object data types: text, ntext, image, nvarchar(max), varchar(max), varbinary(max), and xml data, Variable length columns when the data row exceeds 8 KB: like varchar, nvarchar, varbinary, and sql_variantGlobal Allocation, Shared Global Allocation MapInformation about whether extents are allocated.Page Free SpaceInformation about page allocation and free space available on pages.Index Allocation MapInformation about extents used by a table or index per allocation unit.Bulk Changed MapInformation about extents modified by bulk operations since the last BACKUP LOG statement per allocation unit.Differential Changed MapInformation about extents that have changed since the last BACKUP DATABASE statement per allocation unit.ExtentsExtents are a collection of eight physically contiguous pages and are used to efficiently manage the pages. All pages are stored in extents.Extents are the basic unit in which space is managed. An extent is eight physically contiguous pages, or 64 KB. This means SQL Server databases have 16 extents per megabyte.To make its space allocation efficient, SQL Server does not allocate whole extents to tables with small amounts of data.SQL Server has two types of extents:Uniform extents are owned by a single object; all eight pages in the extent can only be used by the owning object.Mixed extents are shared by up to eight objects. Each of the eight pages in the extent can be owned by a different object.A new table or index is generally allocated pages from mixed extents. When the table or index grows to the point that it has eight pages, it then switches to use uniform extents for subsequent allocations. If you create an index on an existing table that has enough rows to generate eight pages in the index, all allocations to the index are in uniform extents.This is how SQL Server manages the data inside a database files.

SQL Server Storage units to Manage SQL Database

By: DBATAG

POST COMMENT

Exactly What To Search For In A Atlanta Rehab And Substance Abuse Recovery Center Mac Recovery - Solution For Finder Corruption Issue Analyzing Historical Data In Seo Part 3 By Data Recovery Overcome The Data Loss Problems How to Choose Data Entry Company? All The Info And Data You Need To Know About Forex Trading Economic Recovery Drag International Oil Prices Debt Recovery Solutions - How To Improve Credit Rating And Decrease Debt 7 Steps to Follow for Successful Mastectomy Recovery Disaster Recovery Plan Explained Advice on finding a Good Data Entry Job Applying For a Data Entry Job Critical Polar Data Flows Briskly to Researchers