Part 3: Databases, Tables, and Data Types in SQL Server

Microsoft SQL Server Tutorial Series: Beginner to Expert

Welcome back to our SQL Server tutorial series! Now that you’ve installed SQL Server and SSMS, it’s time to start building your database.

In this tutorial, you’ll learn:

How to create a database in SQL Server
What tables are and how to create them
Understanding SQL Server data types
Running your first SQL statements
Best practices for database structure and naming

💻 A Quick Look at the SSMS Interface

Before we dive into SQL code, open SQL Server Management Studio (SSMS) and do the following:

Connect to your server using localhost or your instance name.
In the Object Explorer panel on the left, you’ll see the server node with folders like Databases, Security, and Server Objects.
Click New Query in the toolbar to open a query editor window where you will write SQL commands.

💡 Tip: You can also create databases and tables using the SSMS UI by right-clicking Databases → New Database or right-clicking Tables → New Table. However, learning to write SQL code is essential for mastering SQL Server.

🏗️ Creating Your First Database

Let's create a new database called SchoolDB. Use this SQL command:

CREATE DATABASE SchoolDB;
GO

After running this, refresh the Databases node in Object Explorer to see SchoolDB.

📁 What is a Table?

A table is where your data is stored. It consists of:

Columns (fields): define the type of data
Rows (records): the actual data entries

Think of a table like a spreadsheet: columns are headers, and each row is a record.

✍️ Creating a Table in SQL Server

Now, create a Students table inside SchoolDB:

USE SchoolDB;
GO

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    BirthDate DATE,
    IsActive BIT
);
GO

Explanation of Columns:

Column	Data Type	Description
`StudentID`	INT	Integer, used as Primary Key
`FirstName`	VARCHAR(50)	Variable-length text (up to 50 characters)
`LastName`	VARCHAR(50)	Variable-length text (up to 50 characters)
`BirthDate`	DATE	Date of birth
`IsActive`	BIT	Boolean (0 = false, 1 = true)

🗃️ Common SQL Server Data Types

Data Type	Description	Example Values
`INT`	Whole numbers	1, 50, 1000
`VARCHAR(n)`	Variable length text	'John', 'Hello World'
`DATE`	Date only	'2023-01-01'
`DATETIME`	Date and time	'2023-01-01 12:30:00'
`DECIMAL`	Decimal numbers	99.99, 1200.75
`BIT`	Boolean (0 or 1)	0 = False, 1 = True

➕ Inserting Data into a Table

Insert a sample student into the Students table:

INSERT INTO Students (StudentID, FirstName, LastName, BirthDate, IsActive)
VALUES (1, 'Alice', 'Johnson', '2005-03-15', 1);
GO

You can insert more records by changing the values.

🔎 Querying the Data

To view all data in the Students table:

SELECT * FROM Students;
GO

Running this query will display all records. Congratulations! 🎉 You have created and queried your first table in SQL Server.

🧾 Quick SQL Cheat Sheet

CREATE DATABASE [DatabaseName];
CREATE TABLE [TableName] (
    Column1 DataType PRIMARY KEY,
    Column2 DataType,
    ...
);
INSERT INTO [TableName] (Column1, Column2, ...)
VALUES (Value1, Value2, ...);
SELECT * FROM [TableName];

💡 Did You Know?

SQL Server automatically prevents duplicate primary keys, so you can't insert the same StudentID twice!

🌍 Real-World Example: School Database

Imagine a school system where the Students table stores student info. You could also have related tables like Courses, Grades, and Attendance, all stored in the same database.

This helps organize data efficiently and is a stepping stone to learning relational databases.

🖼️ Simple Database and Table Diagram

+------------------+
|    SchoolDB      |  <-- Database
+------------------+
         |
         v
+------------------+
|    Students      |  <-- Table
+------------------+
| StudentID (INT)  |
| FirstName (VARCHAR) |
| LastName (VARCHAR) |
| BirthDate (DATE)  |
| IsActive (BIT)    |
+------------------+

📋 Best Practices for Beginners

Tip	Recommendation
Naming	Use PascalCase or snake_case (e.g., `StudentID`, `first_name`)
Primary Keys	Always define a primary key for every table
Data Types	Choose appropriate data types to save space and improve performance
Comments	Use `--` for inline comments in SQL to document your code
Consistency	Maintain consistent naming, formatting, and structure

✅ Summary

In this tutorial, you learned how to:

Create a database in SQL Server
Understand and create tables with columns and data types
Insert and query data using basic SQL statements
Follow best practices for database design and naming

🔗 What’s Next?

Check out the previous tutorials to refresh your knowledge:

Stay tuned for Part 4: Writing Basic Queries (SELECT, WHERE, ORDER BY) where we’ll learn how to extract and filter data.

Have questions or want to share your progress? Drop a comment below! 🙌

← Previous Next →

Learn Python

Menu

Creating Databases, Tables & Data Types in SQL Server