CREATE TABLE in SQL Server

CREATE TABLE in SQL Server

Introduction:
The CREATE TABLE statement in SQL Server is used to create a new table in a database. Tables are the fundamental building blocks of a database, and they store data in rows and columns. This documentation provides a step-by-step guide on how to use the CREATE TABLE statement along with coding examples and explanations.

Syntax:
The basic syntax of the CREATE TABLE statement is as follows:

sql
CREATE TABLE table_name (
    column1 datatype [constraints],
    column2 datatype [constraints],
    ...
    columnN datatype [constraints]
);

Explanation:

• CREATE TABLE: This is the SQL keyword to indicate that we are creating a new table.
• table_name: Replace this with the desired name of the table you want to create.
• ( ): Inside the parentheses, we define the columns that the table will have, separated by commas.
• column1, column2, ..., columnN: These are the names of the columns you want to create in the table.
• datatype: Specify the data type of each column. Examples include INT for integer, VARCHAR for variable-length character strings, DATE for dates, etc.
• [constraints]: You can apply optional constraints to each column, such as PRIMARY KEY, NOT NULL, DEFAULT, etc.

Examples:

Example 1: Creating a Simple Table
Let's create a simple table called Employees with three columns: EmployeeID, FirstName, and LastName.

sql
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL
);

Explanation:

• We define three columns: EmployeeID, FirstName, and LastName.
• EmployeeID is defined as an INT data type and is designated as the PRIMARY KEY, which means it will uniquely identify each row in the table.
• FirstName and LastName are defined as VARCHAR(50) data types, which can store up to 50 characters each.
• The NOT NULL constraint ensures that both FirstName and LastName must have a value for every row.

Example 2: Adding Default Values
Let's modify the Employees table to include a column for Department with a default value.

sql
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    Department VARCHAR(100) DEFAULT 'HR'
);

Explanation:

• We added a new column called Department with a VARCHAR(100) data type, allowing up to 100 characters.
• The DEFAULT 'HR' constraint ensures that if no value is specified for the Department, it will default to 'HR'.

Conclusion:
This documentation explained the CREATE TABLE statement in SQL Server, which is used to create a new table within a database. We covered the syntax, explained each component, and provided coding examples to demonstrate the usage of the statement with various scenarios. By following this guide, users can create their own tables and define the columns and constraints as needed for their specific database requirements.