SQL Server Database Storage Structure

Page: 8kb
The basic storage unit of SQL Server is the page which can store up to 8KB of
data. there are 128 pages per megabyte

The page has a 96 bytes header which contains information such as object id and free space.

The storage engine won't allow a record expand to multiple pages. this means maximum length of a row is limited to a little bit over 8000.

Extent:
A set of 8 contiguous pages or 64KB or 16 extents per MB.


Uniform extents are owned by a single database object and all of the 8 pages can only be used by the owning object. Mixed extents can be used by up to eight objects. A new table or index is usually allocated to a mixed extent but when it grows to eight pages will be switched to a uniform extent.


Physical File:
data file, log file etc. They can automatically grow according to the setting. 



A database will typically be mapped over a number of operating system files. Files can only be used by one database and data and log information is never mixed in the same files.



A database will start with a primary file which in turn points to any subsequent files used by a database. Conventionally these files have .mdf as their filename extension. Secondary data files then contain all of the other associated data for a database and come with the extension .ndf. Log files are used to recover the 
database and there will always be at least one log file per database with the extension .ldf. These file extensions are only conventions and can be customised, but it is not recommended as it may cause you some support issues later on.



A file group is a number of database files brought together for easier allocation and administration.. If there are a group of files in a file group then all of the files will need to be full before the file sizes autogrow using a “round robin” algorithm. Log data is never managed as part of a file group as it has a different role to the other data file types.