In Microsoft SQL Server, when you create a new database without custom file settings, does the engine by default create one primary data file (.mdf) and one transaction log file (.ldf)?

Introduction / Context:
Understanding how Microsoft SQL Server lays out files for a newly created database is essential for capacity planning, backup/restore strategies, and performance tuning. By default, SQL Server bases new databases on the model database and creates a single primary data file (.mdf) and a single transaction log file (.ldf) unless you explicitly specify additional files or a different layout.

Given Data / Assumptions:

• No custom file specifications supplied in the CREATE DATABASE statement.
• Default behavior inherited from the model database.
• Standard on-premises SQL Server configuration.

Concept / Approach:
SQL Server databases have at least one data file in the PRIMARY filegroup (commonly .mdf) and at least one log file (.ldf). Additional secondary data files (.ndf) or extra log files are optional and must be explicitly defined. The default simplifies initial setup while allowing later growth or reconfiguration.

Step-by-Step Solution:
Create a database with: CREATE DATABASE MyDB;SQL Server allocates PRIMARY data file and log file paths from server defaults or model template.Confirm with: SELECT name, type_desc FROM sys.database_files WHERE database_id = DB_ID(\'MyDB\');You will see one ROWS (data) file and one LOG (transaction log) file.

Verification / Alternative check:
Script out a default-created database in SSMS; the file definitions show one .mdf and one .ldf. Adding files requires explicit syntax (e.g., FILEGROUP definitions or ADD FILE).

Why Other Options Are Wrong:
“Incorrect” conflicts with default behavior. Tying it to Express edition, AUTO_CLOSE, or model size misunderstands the default creation mechanism; those settings affect other behaviors, not the basic count of files created by default.

Common Pitfalls:
Assuming multiple log files improve performance (they usually do not). Confusing secondary .ndf files (optional) with mandatory .mdf/.ldf defaults.

Final Answer:
Correct