what happen internally when we are creating a table in SQL server

When a CREATE TABLE statement is executed in SQL Server, it performs several internal operations involving parsing, metadata updates, permission checks, and physical storage preparation.

Below are the internal steps of table creation

Ø  Parsing and Validation
Ø  Permission verification
Ø  Metadata updates
Ø  Lock Management
Ø  Storage allocation
Ø  Constraint and index creation
Ø  Transaction logging 

Ø  Completion

Architecture Diagram

 Parsing and Validation

The SQL Server Database Engine first parses the CREATE TABLE statement to ensure correct syntax and structure. It checks if the specified table name is unique within the database/schema and if the column names, data types, and constraints are valid. If any error during the parsing and validation then it will throw and error and table is not created.

Permission verification

After parsing and validation phase pass then go to the next step and check with permission where that user which has necessary CREATE TABLE permission in the database and ALTER permission on the target schema.

Metadata updates

Upon validation, the database's system catalog (also known as the data dictionary) is updated. New entries are added to system tables and catalog views (such as sys.objects, sys.columns, sys.indexes, and sys.partitions) to record the new table's name, structure, columns, data types, and constraints.

System table	Description
sys.objects	Table object definition
sys.tables	Table type entry
sys.columns	Column definitions
sys.types	Data types reference
sys.schemas	Schema mapping
sys.filegroups	Filegroup reference
sys.key_constraints sys.check_constraints	Constraints metadata

 Object ID Gets Generated then SQL Server assigns object_id , table_id and schema_id.

Lock Management

During the operation, the database management system acquires necessary locks to prevent other concurrent operations from conflicting with the table creation process.

Storage Allocation

The table is assigned to a specific filegroup (e.g., the default filegroup, or a user-specified one) which determines its physical location on disk.

Constraint and index creation

If PRIMARY KEY, UNIQUE, FOREIGN KEY, CHECK, or DEFAULT constraints are specified, SQL Server internally sets up the corresponding objects. Primary and unique keys automatically result in the creation of an index (clustered by default for a primary key, non-clustered otherwise).

Transaction Logging

The entire DDL operation is part of a transaction. All changes to the system catalog and initial storage allocations are logged in the transaction log to ensure the operation is atomic and can be rolled back in case of an error.

Completion:

Once all steps are successfully completed and committed, the locks are released, and the new table is ready to store data using INSERT statements.