Lesson 5	Basic Elements of a Transact-SQL Statement
Objective	Use Transact-SQL to create a basic database..

Use "Transact-SQL" to create a Database in SQL Server 2022

To create a basic database in SQL Server 2022 using Transact-SQL (T-SQL), you can use the `CREATE DATABASE` statement. Here’s a simple example and explanation:
✅ Basic T-SQL Syntax:

CREATE DATABASE MySampleDB;

This command creates a new database named `MySampleDB` with default settings.
🛠️ Example with File Specifications:
If you want more control (e.g. file locations, sizes), you can use:

CREATE DATABASE MySampleDB
ON 
( 
    NAME = MySampleDB_data,
    FILENAME = 'C:\SQLData\MySampleDB_data.mdf',
    SIZE = 10MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
)
LOG ON
(
    NAME = MySampleDB_log,
    FILENAME = 'C:\SQLData\MySampleDB_log.ldf',
    SIZE = 5MB,
    MAXSIZE = 25MB,
    FILEGROWTH = 5MB
);

🧠 Key Clauses Explained:

ON: Defines the primary data file and its attributes.
LOG ON: Specifies the transaction log file details.
FILENAME: The physical path where the file is stored.
SIZE, MAXSIZE, FILEGROWTH: Control file growth and storage.

📌 Notes for SQL Server 2022:

Ensure the file paths (C:\SQLData\) exist and the SQL Server service account has write permissions.
You can run this code inside SQL Server Management Studio (SSMS) or any T-SQL-compatible tool.
Consider using FOR ATTACH if you are attaching an existing .mdf file (e.g. from a backup).

Transact-SQL CREATE DATABASE Statement

To create a database, use the Transact-SQL statement CREATE DATABASE. The only thing that is required along with the statement is a name for the database, such as "employee." You can create a database with all the default values using a Transact-SQL statement as simple as:

CREATE DATABASE employee

Without specifying any additional parameters, the following defaults are created:

The filename is employee_Data.MDF and it is located in the SQL Server data directory.
The initial size of the database is equal to the size of primary file in themodel database. If you have not changed this value since you installed SQL Server 2022, the size is one megabyte.
The database size will grow automatically in 10% increments with no maximum.
The file location of the transaction log is employee_Log.LDF (in your SQL Server data directory).
The initial size of the transaction log is one megabyte.
The transaction log size will grow automatically in 10% increments with no maximum.

The series of images below reviews the available syntax for the CREATE DATABASE statement:

create-database1 — The diagram above contains Transact-SQL (T-SQL) syntax for creating a database in SQL Server. Below is the extracted T-SQL code:

CREATE DATABASE database_name [ ON [PRIMARY] [ <filespec> [,...n] ] [, <filegroup> [,...n] ] ] [ LOG ON { <filespec> } ] [ FOR LOAD | FOR ATTACH ]

**Explanation of the Code:**

**`CREATE DATABASE database_name`**

This command creates a new database.

Replace **`database_name`** with the actual name of your database.

**`ON [PRIMARY]`**

Specifies that the primary data file should be created.

`[ <filespec> [,...n] ]` allows defining multiple files for the database.

`[ <filegroup> [,...n] ]` lets you assign database files to different filegroups.

**`LOG ON { <filespec> }`**

Specifies where the transaction log files will be stored.

**`FOR LOAD | FOR ATTACH`**

`FOR LOAD`: Used when restoring a database from a backup.

`FOR ATTACH`: Used to attach an existing MDF file to SQL Server.

**Example: Creating a Database in SQL Server**
CREATE DATABASE SalesDB ON PRIMARY ( NAME = SalesData, FILENAME = 'C:\SQLData\SalesDB.mdf', SIZE = 10MB, MAXSIZE = 50MB, FILEGROWTH = 5MB ) LOG ON ( NAME = SalesLog, FILENAME = 'C:\SQLData\SalesDB_log.ldf', SIZE = 5MB, MAXSIZE = 25MB, FILEGROWTH = 5MB );

Creates a **SalesDB** database.

Stores the **data file** (`.mdf`) in `C:\SQLData\`.

Stores the **log file** (`.ldf`) in `C:\SQLData\`.

Specifies **initial size, max size, and file growth**.

create-database2 — Transact-SQL (T-SQL) syntax for creating a database in SQL Server. Here is the transcribed T-SQL code:

CREATE DATABASE database_name ON [PRIMARY] [ <filespec> [,...n] ] [, <filegroup> [,...n] ] [ LOG ON { <filespec> } ] [ FOR LOAD | FOR ATTACH ]

**Additional Explanation:**

`ON [PRIMARY]`: Indicates that you are specifying the primary filegroup and its associated files.

`<filespec>`: Refers to the logical name, filename, size, max size, and file growth properties.

`LOG ON`: Specifies the log file details.

`FOR LOAD | FOR ATTACH`: Used when loading or attaching an existing database.

3. PRIMARY indicates that the files are stored in the primary filegroup.

create-database4 — 4. filespec is a group of properties that indicates attributes of the files that make up a database. A filespec contains these arguments.

create-database5 — 5. filegroup is an administrative grouping of filespecs. A filegroup contains these arguments(note that for file spec refers to the attributes above)

FILEGROUP filegroup_name <filespec>

create-database6 — 6. LOG ON specifies the filespec attributes for the transaction log

Transact-SQL Code in SQL Server 2022

The Transact-SQL code is valid and runs in SQL Server 2022.
✅ Explanation:
The syntax:

CREATE DATABASE database_name
ON [PRIMARY]
[ <filespec> [,...n] ]
[, <filegroup> [,...n] ]
[ LOG ON { <filespec> } ]
[ FOR LOAD | FOR ATTACH ]

is still supported in SQL Server 2022.
Key points:

`ON [PRIMARY]` — used to specify the primary data file and any additional secondary files.
`FOR ATTACH` — still a supported option in SQL Server 2022 for attaching existing `.mdf` and `.ldf` files.
`FOR LOAD` — used when restoring a database with RESTORE DATABASE ... WITH NORECOVERY. Although rarely used today, it’s still part of the syntax.

Example of a working `FOR ATTACH` in SQL Server 2022:

CREATE DATABASE MyDatabase
ON (FILENAME = 'C:\SQLData\MyDatabase.mdf')
FOR ATTACH;

This would successfully attach an existing `.mdf` file in SQL Server 2022, provided that:

The file exists and is not corrupted.
SQL Server has the proper file system permissions.

In the next lesson, we will take a look at control-of-flow logic, which enables you to test the value of a variable and take an action based on that value.