Home SQL Beginner’s Guide to MSSQL: Create SQL Table, Insert, Update, and Delete Data

Beginner’s Guide to MSSQL: Create SQL Table, Insert, Update, and Delete Data

September 07, 2025

SQL Server Students Table Example – Create, Insert, Update, Delete Data

Managing data is one of the most important parts of working with databases. If you are new to MSSQL (Microsoft SQL Server), you need to learn the basic operations: Create, Insert, Update, Delete, and Select. These are also called CRUD operations.

In this article, we’ll cover everything step by step with examples.

What is MSSQL Database Table? Step-by-Step Implementation Create SQL Table Add Default Value Create Unique Index Insert Data Update Data Delete Data Select Data Best Practices Frequently Asked Questions (FAQ) Conclusion

What is MSSQL Database Table?

In SQL Server, a table is like an Excel sheet.

Each row = a record (e.g., one student).
Each column = a field (e.g., FirstName, Email).

Example: A Students table can store details like Student Number, Name, Email, Mobile, Date of Birth, and Fees.

Step-by-Step Implementation

Let’s now create a real Students table and perform basic CRUD operations.

Create SQL Table

We’ll create a table named Students.


CREATE TABLE [dbo].[Students](
    [StudentID] INT IDENTITY(1,1) NOT NULL,       -- Auto number (1, 2, 3…)
    [UpdateDateTime] DATETIME NOT NULL DEFAULT (GETDATE()), -- Last update time
    [RegDateTime] DATETIME NOT NULL,              -- Registration date
    [Active] BIT NOT NULL DEFAULT (1),            -- 1 = Active, 0 = Inactive
    [StudentNo] NVARCHAR(20) NOT NULL,            -- Student Number (unique)
    [FirstName] NVARCHAR(100) NOT NULL,           -- First name
    [LastName] NVARCHAR(100) NOT NULL,            -- Last name
    [Email] NVARCHAR(100) NULL,                   -- Email (optional)
    [Mobile] NVARCHAR(20) NULL,                   -- Mobile number
    [DateOfBirth] DATE NOT NULL,                  -- Date of birth
    [StudentFee] DECIMAL(18,2) NULL,              -- Student fee (money)
    CONSTRAINT [PK_Students] PRIMARY KEY CLUSTERED ([StudentID] ASC) -- Primary Key
);
GO

Add Default Value

We want RegDateTime to automatically set to the current date when inserting new records.


ALTER TABLE [dbo].[Students] 
ADD DEFAULT (GETDATE()) FOR [RegDateTime];
GO

Create Unique Index

To prevent duplicate student numbers, we add a unique index.


CREATE UNIQUE INDEX UIX_Students_StudentNo
ON [dbo].[Students] ([StudentNo]);
GO

Insert Data

Now we add some sample students.


INSERT INTO [dbo].[Students]
(
    StudentNo, FirstName, LastName, Email, Mobile, DateOfBirth, StudentFee
)
VALUES
('STU0001', 'John',    'Smith',     'john.smith@example.com',    '+1-202-555-0123', '2000-05-14', 1500.00),
('STU0002', 'Emily',   'Johnson',   'emily.johnson@example.com', '+1-202-555-0456', '2001-08-22', 1600.00),
('STU0003', 'Michael', 'Williams',  'michael.w@example.com',     '+1-202-555-0789', '1999-11-30', 1550.50),
('STU0004', 'Sarah',   'Brown',     'sarah.brown@example.com',   '+1-202-555-0198', '2002-02-18', 1400.00),
('STU0005', 'David',   'Jones',     'david.jones@example.com',   '+1-202-555-0246', '2000-09-05', 1700.00),
('STU0006', 'Olivia',  'Garcia',    'olivia.garcia@example.com', '+1-202-555-0671', '2001-04-11', 1450.00),
('STU0007', 'James',   'Martinez',  'james.m@example.com',       '+1-202-555-0822', '1998-12-25', 1650.00),
('STU0008', 'Sophia',  'Davis',     'sophia.davis@example.com',  '+1-202-555-0913', '2003-01-07', 1500.00),
('STU0009', 'William', 'Miller',    'will.miller@example.com',   '+1-202-555-0147', '2000-07-19', 1750.00),
('STU0010', 'Isabella','Wilson',    'isabella.w@example.com',    '+1-202-555-0334', '2002-03-28', 1525.00);
GO

Update Data

Let’s say Emily Johnson (StudentID = 2) got a fee change.


UPDATE Students
SET UpdateDateTime = GETDATE(),   -- record update time
    StudentFee     = 2000.00      -- new fee value
WHERE StudentID = 2;              -- only update student 2

Delete Data

If John Smith (StudentID = 1) leaves, we delete his record:


DELETE FROM Students WHERE StudentID = 1;

Careful! This removes the student permanently.

Select Data (Read)

To see all students:


SELECT * FROM Students;

SELECT statement is used to select data from a MSSQL table.

To see only active students:


SELECT * FROM Students WHERE Active = 1;

Best Practices

Always back up your database before making changes.
Use WHERE in UPDATE and DELETE to avoid changing/deleting everything by mistake.
Use transactions if you want safe rollbacks.
Keep StudentNo unique to prevent duplicate records.
Use meaningful column names (e.g., DateOfBirth, not just DOB).

Frequently Asked Questions (FAQ)

1. What is a Primary Key in SQL Server?

A Primary Key uniquely identifies each record in a table. In our example, StudentID is the primary key.

2. What is a Unique Index?

It ensures no two records have the same value in a specific column. Example: StudentNo must always be unique.

3. Can I recover deleted data?

If you have a backup or transaction logs, yes. Otherwise, once deleted, the data is gone.

4. What does NVARCHAR mean?

It stores text and supports Unicode characters (letters from any language).

Conclusion

In this beginner-friendly SQL Server tutorial, we learned how to:

Create SQL Table with fields and keys
Insert Data into MSSQL
Update Data when values change
Delete Data safely
Select Data to view records

These are the CRUD operations that form the base of every database system. Once you understand these basics, you’re ready to explore joins, stored procedures, views, and triggers.

Keep practicing with different examples and you’ll soon become confident with SQL queries!

Post ID: 3