In SQL Server, the concept of database storage is divided into physical and logical representations.

1. Physical Representation

The physical representation refers to how data is stored on the disk in files that make up the database. There are two primary types of files:

• MDF (Primary Data File):
  • Contains the main data of the database (tables, indexes, etc.).
  • There is only one primary data file for each database.
  • Default extension is .mdf.
• NDF (Secondary Data File):
  • Optional files used if you want to split the data across multiple files or disks.
  • The database can have multiple NDF files.
  • Default extension is .ndf.
• LDF (Transaction Log File):
  • Stores a record of all transactions and modifications made to the database.
  • This is essential for database recovery in case of failure.
  • Default extension is .ldf.

2. Logical Representation

The logical structure of a database refers to how the data is organized internally in the database, regardless of how it's physically stored on disk.

• Filegroup:
  • A filegroup is a logical container for database files. It is a grouping of one or more data files (MDF and NDF) that stores data in the database.
  • Every database has a primary filegroup, which includes the primary data file (MDF) by default.
  • You can create additional filegroups to organize data into different locations (e.g., on different disks) for performance reasons or data separation.
  • When creating tables or indexes, you can specify which filegroup they should be stored in.
• Primary Filegroup:
  • Automatically created when the database is created.
  • Contains the main database objects and by default includes the primary data file (MDF).
  • If no other filegroup is specified, all objects are created in the primary filegroup.

Example of a Logical to Physical Mapping:

1. Logical Representation: A table is created in the database, and the data is logically organized within a specific filegroup.
2. Physical Representation: The data for that table is then written to one or more physical data files (MDF or NDF) on the disk, depending on the filegroup configuration.

SQL Server File Structure Example:

-- Example of specifying a table to use a different filegroup
CREATE DATABASE MyDatabase
ON PRIMARY
( NAME = MyPrimaryFile,
  FILENAME = 'C:\\\\MyData\\\\MyPrimaryFile.mdf',
  SIZE = 5MB,
  MAXSIZE = 100MB,
  FILEGROWTH = 5MB )
LOG ON
( NAME = MyLogFile,
  FILENAME = 'C:\\\\MyData\\\\MyLogFile.ldf',
  SIZE = 1MB,
  MAXSIZE = 50MB,
  FILEGROWTH = 1MB );

Data pages

• Data pages are 8 KB blocks that store the actual data for tables and indexes.
• SQL Server uses a variety of page types to manage different kinds of data, including data pages, index pages, and pages for large objects.
• Pages are grouped into extents, and SQL Server uses allocation maps to track which extents and pages are in use or available.