Sql Server Creating And Altering Tables Complete Guide

Last Update:2025-06-23T00:00:00 .NET School AI Teacher - SELECT ANY TEXT TO EXPLANATION. 7 mins read Difficulty-Level: beginner

Understanding the Core Concepts of SQL Server Creating and Altering Tables

SQL Server: Creating and Altering Tables

Creating Tables

Basic Syntax: The CREATE TABLE command is used to create a new table. Here is the basic syntax:
```
CREATE TABLE table_name (
    column1_name data_type column_constraints,
    column2_name data_type column_constraints,
    ...
    table_constraints
);
```
- table_name: Specifies the name of the table to be created.
- column_name: Specifies the name of the column.
- data_type: Specifies the data type for the column such as INT, VARCHAR, DATE, etc.
- column_constraints: Optional constraints like PRIMARY KEY, FOREIGN KEY, NOT NULL, UNIQUE, CHECK, DEFAULT.
- table_constraints: Optional constraints like PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK.

Example: Here is an example of creating a table named Employees:

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    DepartmentID INT NOT NULL,
    Salary DECIMAL(10, 2) NOT NULL,
    HireDate DATE DEFAULT GETDATE()
);

Constraints:
- PRIMARY KEY: Ensures that the column is uniquely identified and not NULL. Only one primary key per table is allowed.
- FOREIGN KEY: Establishes a link between the data in two tables.
- NOT NULL: Forces a column to always contain a value; it cannot have NULL entries.
- UNIQUE: Ensures all values in a column are different.
- CHECK: Ensures that all the values in a column satisfy a specific condition.
- DEFAULT: Provides a default value when no value is specified.

Altering Tables

Basic Syntax: The ALTER TABLE command is used to modify an existing table. Here are some common ALTER TABLE operations:
- Add Column: Adds a new column to the table.
```
ALTER TABLE table_name
ADD column_name data_type column_constraints;
```
- Drop Column: Removes a column from the table.
```
ALTER TABLE table_name
DROP COLUMN column_name;
```
- Modify Column: Changes the data type or constraints of an existing column.
```
ALTER TABLE table_name
ALTER COLUMN column_name new_data_type column_constraints;
```
- Add Constraint: Adds a constraint to the table.
```
ALTER TABLE table_name
ADD CONSTRAINT constraint_name constraint_definition;
```
- Drop Constraint: Removes a constraint from the table.
```
ALTER TABLE table_name
DROP CONSTRAINT constraint_name;
```

Example: Here are some examples using the Employees table:

Adding a column:

ALTER TABLE Employees
ADD Phone VARCHAR(15);

Dropping a column:

ALTER TABLE Employees
DROP COLUMN HireDate;

Modifying a column:

ALTER TABLE Employees
ALTER COLUMN FirstName VARCHAR(30);

Adding a constraint:

ALTER TABLE Employees
ADD CONSTRAINT chk_Salary CHECK (Salary >= 50000);

Dropping a constraint:

ALTER TABLE Employees
DROP CONSTRAINT chk_Salary;

Considerations:
- Backups: Always ensure you have backups before making structural changes to tables.
- Performance Impact: Modifying tables can impact performance, especially large tables. Plan changes during off-peak hours if possible.
- Testing Environment: Test changes in a development or staging environment before applying them in production.

Important Information

Normalization: Understanding database normalization is crucial for creating efficient and robust tables.
Data Integrity: Proper use of constraints ensures data integrity and reduces errors.
Security: Ensure that changes to tables are authorized and implemented according to security guidelines.
Indexing: Consider indexing columns that are frequently used in WHERE clauses to improve query performance.
Relationships: Well-defined relationships between tables can simplify queries and data retrieval.

Online Code run

🔔 Note: Select your programming language to check or run code at

💻 Run Code Compiler

Step-by-Step Guide: How to Implement SQL Server Creating and Altering Tables

Step-by-Step Example: Creating a Table in SQL Server

Scenario:

We want to create a table named Employees that includes columns for an employee's ID, first name, last name, date of birth, job title, salary, and hire date.

Step 1: Open SQL Server Management Studio (SSMS)

Launch SSMS.
Connect to your SQL Server instance.

Step 2: Create a New Query Editor Window

In the Object Explorer, right-click on any database (e.g., master) and select New Query.

Step 3: Write the SQL Statement to Create the Table

-- Create a new table named Employees
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY IDENTITY(1,1),  -- Unique identifier for each employee
    FirstName NVARCHAR(50) NOT NULL,          -- First name of the employee
    LastName NVARCHAR(50) NOT NULL,           -- Last name of the employee
    DateOfBirth DATE,                         -- Date of birth of the employee
    JobTitle NVARCHAR(100),                   -- Job title of the employee
    Salary DECIMAL(18,2),                     -- Salary of the employee
    HireDate DATE DEFAULT GETDATE()             -- Date when the employee was hired, default value is current date
);

Step 4: Execute the SQL Statement

Highlight the entire SQL statement.
Click the Execute button (or press F5) to run the statement.

Step 5: Verify the Table Creation

Expand the database node in the Object Explorer.
Navigate to the Tables folder and find the Employees table.
Right-click on the Employees table and select Script Table as > CREATE To > New Query Editor Window. This will generate the SQL script used to create the table, allowing you to verify it.

Step-by-Step Example: Altering a Table in SQL Server

Scenario:

We need to make some changes to the Employees table created in the previous example. Specifically, we want to:

Add a new column Email with the data type NVARCHAR(100).
Modify the Salary column to use DECIMAL(19,4) instead of DECIMAL(18,2).
Add a unique constraint to the Email column.
Remove the JobTitle column.

Step 1: Open a New Query Editor Window

Again, in the Object Explorer, navigate to your database.
Right-click and select New Query.

Step 2: Write the SQL Statements to Alter the Table

-- Step 2.1: Add a new column Email
ALTER TABLE Employees
ADD Email NVARCHAR(100);

-- Step 2.2: Modify the Salary column to have more precision
ALTER TABLE Employees
ALTER COLUMN Salary DECIMAL(19,4);

-- Step 2.3: Add a unique constraint to the Email column
ALTER TABLE Employees
ADD CONSTRAINT UQ_Email UNIQUE (Email);

-- Step 2.4: Remove the JobTitle column
ALTER TABLE Employees
DROP COLUMN JobTitle;

Alternatively, you can combine some steps using multiple alterations in one statement:

Top 10 Interview Questions & Answers on SQL Server Creating and Altering Tables

Top 10 Questions and Answers on SQL Server: Creating and Altering Tables

1. How do you create a table in SQL Server?

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    HireDate DATE,
    Salary DECIMAL(10, 2)
);

This command creates a table named Employees with columns for EmployeeID, FirstName, LastName, HireDate, and Salary.

2. What is the difference between `NVARCHAR` and `VARCHAR`?

Answer: Both NVARCHAR and VARCHAR are used to store variable-length character strings, but NVARCHAR stores Unicode characters (2 bytes per character) and can store a wider range of international characters, while VARCHAR stores non-Unicode characters (1 byte per character). For tables that require storage of international or special characters, NVARCHAR is preferred.

3. How do you add a new column to an existing table?

Answer: You can add a new column to an existing table using the ALTER TABLE statement with the ADD clause. For example, to add an Email column to the Employees table:

ALTER TABLE Employees
ADD Email NVARCHAR(100);

4. How do you modify the data type of a column in an existing table?

Answer: To change the data type of a column, use the ALTER TABLE statement with the ALTER COLUMN clause. For example, if you want to change the data type of the Email column to NVARCHAR(200):

ALTER TABLE Employees
ALTER COLUMN Email NVARCHAR(200);

5. How can you add a primary key to an existing table?

Answer: If you forgot to include a primary key when creating a table, you can add one later:

ALTER TABLE Employees
ADD CONSTRAINT PK_EmployeeID PRIMARY KEY (EmployeeID);

Here, PK_EmployeeID is a name given to the primary key constraint. EmployeeID will be the column used as the primary key.

6. How do you delete a column from a table?

Answer: You can remove a column using the ALTER TABLE statement with the DROP COLUMN clause. For example, to remove the HireDate column from the Employees table:

ALTER TABLE Employees
DROP COLUMN HireDate;

Note: Be careful when dropping columns, as any data stored in the column will be permanently deleted.

7. How do you rename a column in a SQL Server table?

Answer: Renaming a column in SQL Server is done using the sp_rename stored procedure. For example, to rename Email to EmailAddress in the Employees table:

EXEC sp_rename 'Employees.Email', 'EmailAddress', 'COLUMN';

8. What is an index in SQL Server, and how do you create one?

Answer: An index in SQL Server is a data structure that improves the speed of data retrieval operations on a database table at the cost of additional writes and storage space. To create an index, use the CREATE INDEX statement. For example:

CREATE INDEX idx_EmployeeLastName ON Employees (LastName);

This creates a non-clustered index on the LastName column of the Employees table.

9. How do you delete a table in SQL Server?

Answer: You can delete a table using the DROP TABLE statement. For example:

DROP TABLE Employees;

Note: Dropping a table is irreversible and will permanently delete the table and all of its data. Use this command with caution.

10. How do you create a table with a foreign key constraint?

Answer: A foreign key establishes a link between the data in two tables. To create a foreign key constraint, use the FOREIGN KEY clause in the CREATE TABLE statement. For example:

CREATE TABLE Departments (
    DepartmentID INT PRIMARY KEY,
    DepartmentName NVARCHAR(50)
);

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    HireDate DATE,
    Salary DECIMAL(10, 2),
    DepartmentID INT,
    FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
);

In this example, DepartmentID in the Employees table is a foreign key that references the DepartmentID in the Departments table. This ensures referential integrity between the two tables.

Sql Server Creating And Altering Tables Complete Guide