Working with Date, String, and Numeric Types in SQL Server - Beginner Tutorial

Part 7: Working with Date, String, and Numeric Types in SQL Server

Microsoft SQL Server Tutorial Series: Beginner to Expert

---

Welcome to Part 7 of our SQL Server tutorial series! In this lesson, we’ll dive into the essential data types that SQL Server offers for handling dates, strings, and numbers — the building blocks for storing and manipulating your data.

---

By the end of this article, you’ll understand:

• How to work with date and time data types
• The different string data types and their uses
• Numeric types and how to choose the right one for your data
• Examples of working with these data types in SQL queries

---

📅 Working with Date and Time Data Types

SQL Server provides several data types to store dates and times. Choosing the right one depends on the precision and storage size you need.

Data Type	Description	Storage Size	Example Format
DATE	Stores only date (year, month, day)	3 bytes	YYYY-MM-DD (e.g., 2025-09-09)
TIME	Stores time of day (hour, minute, second, fractional seconds)	3-5 bytes	HH:MM:SS[.fractional seconds]
DATETIME	Stores date and time with 3.33 ms precision	8 bytes	YYYY-MM-DD HH:MM:SS
DATETIME2	Stores date and time with higher precision	6-8 bytes	YYYY-MM-DD HH:MM:SS[.fractional seconds]
SMALLDATETIME	Stores date and time with less precision (1 minute)	4 bytes	YYYY-MM-DD HH:MM:00

Example: Creating a table with date and time columns

CREATE TABLE Events (
    EventID INT PRIMARY KEY,
    EventName VARCHAR(100),
    EventDate DATE,
    StartTime TIME,
    CreatedAt DATETIME2 DEFAULT SYSDATETIME()
);

---

📝 String Data Types in SQL Server

Strings are used to store text. SQL Server supports different string types depending on length and Unicode support:

Data Type	Description	Max Length	Use Case
CHAR(n)	Fixed-length non-Unicode string	1 to 8000	When string length is consistent
VARCHAR(n)	Variable-length non-Unicode string	1 to 8000	Most common for English or ASCII text
VARCHAR(MAX)	Variable-length non-Unicode string (max size)	Up to 2 GB	Very long text, e.g., descriptions, documents
NCHAR(n)	Fixed-length Unicode string	1 to 4000	International characters with fixed length
NVARCHAR(n)	Variable-length Unicode string	1 to 4000	Supports international/multilingual text
NVARCHAR(MAX)	Variable-length Unicode string (max size)	Up to 2 GB	Long international/multilingual text

Example: Creating a table with string columns

CREATE TABLE Users (
    UserID INT PRIMARY KEY,
    UserName VARCHAR(50),
    Email NVARCHAR(100),
    Bio VARCHAR(MAX) NULL
);

---

🔢 Numeric Data Types in SQL Server

Numbers in SQL Server come in various sizes and types. Here are the main numeric types:

Data Type	Description	Storage Size	Example Values
INT	Integer (whole number)	4 bytes	-2,147,483,648 to 2,147,483,647
SMALLINT	Small integer	2 bytes	-32,768 to 32,767
TINYINT	Very small integer	1 byte	0 to 255
BIGINT	Large integer	8 bytes	-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807
DECIMAL(p,s)	Fixed precision and scale decimal number	5 to 17 bytes	e.g., DECIMAL(10,2) = 12345678.90
FLOAT	Approximate floating point number	4 or 8 bytes	3.14159, 2.7e10

Example: Creating a table with numeric columns

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(100),
    Price DECIMAL(10, 2),
    Quantity INT,
    Weight FLOAT
);

---

💡 Tips for Choosing Data Types

• Use DATE if you only need the date, not time.
• Choose Unicode types like NVARCHAR if your app needs to support multiple languages.
• Use DECIMAL for exact numeric values like money; use FLOAT for approximate values.
• Use the smallest integer type that fits your range to save space.

---

📋 Quick Data Types Cheat Sheet

Dates: DATE, DATETIME, DATETIME2
Strings: VARCHAR, NVARCHAR, CHAR, NCHAR
Numbers: INT, SMALLINT, TINYINT, BIGINT, DECIMAL, FLOAT

---

🔍 Summary

Understanding SQL Server’s date, string, and numeric data types is fundamental to designing efficient databases and writing effective queries. By choosing the right data type, you optimize storage and ensure your data behaves as expected.

In the next part of this series, we’ll explore advanced SQL functions and how to manipulate data more powerfully.

---

Did you find this article helpful?

Feel free to leave a comment or share your questions below!

← Previous Next →

Creating Databases, Tables & Data Types in SQL Server

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:

1. Connect to your server using localhost or your instance name.
2. In the Object Explorer panel on the left, you’ll see the server node with folders like Databases, Security, and Server Objects.
3. 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:

• Part 1: What is SQL Server? Editions and Architecture
• Part 2: Installing SQL Server and SSMS

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 →