Microsoft SQL Server: Difference between revisions

Revision as of 11:27, 31 August 2023

Revision as of 11:33, 31 August 2023

Line 102:

[[Computer data storage|Data storage]] is a [[database]], which is a collection of tables with [[type system|typed]] columns. SQL Server supports different data types, including [[primitive type]]s such as ''Integer'', ''Float'', ''Decimal'', ''Char'' (including character strings), ''Varchar'' (variable length character strings), binary (for unstructured [[Binary large object|blobs]] of data), ''Text'' (for textual data) among others. The [[rounding]] of floats to integers uses either Symmetric Arithmetic Rounding or Symmetric Round Down (''fix'') depending on arguments: <code>SELECT Round(2.5, 0)</code> gives 3.

Microsoft SQL Server also allows user-defined composite types (UDTs) to be defined and used. It also makes server statistics available as virtual tables and views (called Dynamic Management Views or DMVs). In addition to tables, a database can also contain other objects including [[view (database)|views]], [[stored procedure]]s, [[index (database)|indexes]] and [[constraint (database)|constraints]], along with a transaction log. A SQL Server database can contain a maximum of 2³¹ objects, and can span multiple OS-level files with a maximum file size of 2⁶⁰ bytes (1 exabyte).<ref name="storageengine" /> The data in the database are stored in primary data files with an extension <code>.mdf</code><ref>{{Cite web |url=https://file-types.com/docs/mdf |title=Master Database File |date=2016-10-10 |access-date=2022-02-07 |url-status=live }}</ref>. Secondary data files, identified with a <code>.ndf</code> extension, are used to allow the data of a single database to be spread across more than one file, and optionally across more than one file system. Log files are identified with the <code>.ldf</code> extension.<ref name="storageengine" />

Storage space allocated to a database is divided into sequentially numbered ''pages'', each 8&nbsp;KB in size. A ''page'' is the basic unit of [[Input/output|I/O]] for SQL Server operations. A page is marked with a 96-byte header which stores metadata about the page including the page number, page type, free space on the page and the ID of the object that owns it. The page type defines the data contained in the page. This data includes: data stored in the database, an index, an allocation map, which holds information about how pages are allocated to tables and indexes; and a change map which holds information about the changes made to other pages since last backup or logging, or contain large data types such as image or text. While a page is the basic unit of an I/O operation, space is actually managed in terms of an ''extent'' which consists of 8 pages. A database object can either span all 8 pages in an extent ("uniform extent") or share an extent with up to 7 more objects ("mixed extent"). A row in a database table cannot span more than one page, so is limited to 8&nbsp;KB in size. However, if the data exceeds 8&nbsp;KB and the row contains ''varchar'' or ''varbinary'' data, the data in those columns are moved to a new page (or possibly a sequence of pages, called an ''allocation unit'') and replaced with a pointer to the data.<ref name="pages" />