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:

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.

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