How is data stored in SQL server?

Question

How is data stored in SQL server?

Answer 1

What is RDBMS?

RDBMS stands for Relational Database Management System. RDBMS data is structured in database tables, fields and records. Each RDBMS table consists of database table rows. Each database table row consists of one or more database table fields.

RDBMS store the data into collection of tables, which might be related by common fields (database table columns). RDBMS also provide relational operators to manipulate the data stored into the database tables. Most RDBMS use SQL as database query language.

Edgar Codd introduced the relational database model. Many modern DBMS do not conform to the Codd’s definition of a RDBMS, but nonetheless they are still considered to be RDBMS.

The most popular RDBMS are MS SQL Server, DB2, Oracle and MySQL. Source

Answer 2

This Wikipedia article describes it rather well.

Here is a subset of it, relating to Data Storage:

[edit] Data storage The main unit of data storage is a database, which is a collection of tables with typed columns. SQL Server supports different data types, including primary types such as Integer, Float, Decimal, Char (including character strings), Varchar (variable length character strings), binary (for unstructured blobs of data), Text (for textual data) among others. It also allows user-defined composite types (UDTs) to be defined and used. SQL Server also makes server statistics available as virtual tables and views (called Dynamic Management Views or DMVs). A database can also contain other objects including views, stored procedures, indexes and constraints, in addition to tables, along with a transaction log. A SQL Server database can contain a maximum of 231 objects, and can span multiple OS-level files with a maximum file size of 220 TB.[18] The data in the database are stored in primary data files with an extension .mdf. Secondary data files, identified with an .ndf extension, are used to store optional metadata. Log files are identified with the .ldf extension.[18]

Storage space allocated to a database is divided into sequentially numbered pages, each 8 KB in size. A page is the basic unit of 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. Page type defines the data contained in the page - data stored in the database, index, allocation map which holds information about how pages are allocated to tables and indexes, 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 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 KB in size. However, if the data exceeds 8 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.[22]

For physical storage of a table, its rows are divided into a series of partitions (numbered 1 to n). The partition size is user defined; by default all rows are in a single partition. A table is split into multiple partitions in order to spread a database over a cluster. Rows in each partition are stored in either B-tree or heap structure. If the table has an associated index to allow fast retrieval of rows, the rows are stored in-order according to their index values, with a B-tree providing the index. The data is in the leaf node of the leaves, and other nodes storing the index values for the leaf data reachable from the respective nodes. If the index is non-clustered, the rows are not sorted according to the index keys. An indexed view has the same storage structure as an indexed table. A table without an index is stored in an unordered heap structure. Both heaps and B-trees can span multiple allocation units.[23]

Answer 3

SQL Server is a Relational Database Management System:

A Relational database management system (RDBMS) is a database management system (DBMS) that is based on the relational model as introduced by E. F. Codd. Most popular commercial and open source databases currently in use are based on the relational model.

A short definition of an RDBMS may be a DBMS in which data is stored in the form of tables and the relationship among the data is also stored in the form of tables.

Answer 4

You can take this just about as deep as you want to go, but for SQL Server 2008 here is a good overview of basic database architecture.

The MSDN site will be a valuable resource if you need even more detailed specifics on how SQL Server 2008 stores data.

Answer 5

This might help:

Planning and Architecture (Database Engine) (SQL Server 2008 Books Online)

Answer 6

SQL Server data is stored in data files that, by default, have an .MDF extension. The log (.LDF) files are sequential files used by SQL Server to log transactions executed against the SQL Server instance (more on instances in a moment). The log files (.LDF files) are truncated automatically when using the SIMPLE recovery model, but not when using BULK LOGGED or FULL recovery.

Instances allow for more than one installation of SQL Server on a single machine. If the instance is nameless, it is the default instance. Named instances are possible as well. For eg:

MACHINENAME <-- the default instance is just the machine name MACHINENAME\Test <-- this is the "Test" instance on this machine

You can use tools like SQL Server Management Studio (as of SQL Server 2005) or Enterprise Manager (SQL Server 2000 and before) to interact with the instance & the databases under the instance.

All instances (as of SQL Server 2005) will have a hidden resource database, as well as a master, model, msdb, and temp database. These databases are "system" databases.

Not sure what else you're looking for. Hope that helps.

EDIT: Oh yeah, physically, data in the "data files" (.MDF files, by default) is structured in what are known as "pages" in SQL Server. Data in the log files (.LDF files) is stored sequentially. In the enterprise, the data and log files are sometimes split on different physical hard drives for better disk I/O. Or hardware RAID is used for this purpose.

EDIT2: Forgot to mention file groups. Using file groups, you can design your logical database schema such that elements of that schema are physically separated, typically to disburse the physical database across different hard drives. For example, you could have a data file group, an indexes file group, and an images file group (for binary images).

Answer 7

I recommend the book 'Microsoft SQL Server 2008 Internals' -- in fact anything by Kalen Delaney on internals is good, IMO.