What Are Views in SQL? – Definition, Benefits, and Examples

Introduction

In SQL, text data types are used to store alphanumeric values like names, addresses, emails, and descriptions. Choosing the correct text type — CHAR, VARCHAR, or TEXT — is important for optimizing storage space, query speed, and database performance.

In this section, you'll learn the definitions, differences, and best use cases for each text data type.

1. CHAR (Fixed-Length String)

CHAR is used to store fixed-length strings. If the stored string is shorter than the defined length, SQL automatically pads it with spaces to match the specified size.

Features:

• Fixed length
• Fast and predictable performance
• Uses extra storage if the data is often shorter than the specified length

Syntax:

column_name CHAR(length);

length = number of characters (1 to 255 depending on the database system)

Example:

CREATE TABLE countries (
country_code CHAR(2),
country_name CHAR(50)
    );

country_code like 'US', 'IN', 'UK' will always take 2 characters.

When to Use CHAR:

• Data with a constant size, such as country codes, gender ('M', 'F'), state abbreviations
• Fixed-format fields like credit card types ('VISA', 'MC')
• When exact storage size is known and consistent

2. VARCHAR (Variable-Length String)

VARCHAR stands for Variable Character. It stores variable-length strings, meaning only the actual characters are stored without unnecessary padding.

Features:

• Variable length
• More space-efficient than CHAR for varying-length text
• Slightly slower than CHAR when processing large volumes (because of extra calculations for string lengths)

Syntax:

column_name VARCHAR(length);

length = maximum number of characters allowed

Example:

CREATE TABLE employees (
first_name VARCHAR(50),
email VARCHAR(100)
    );

Names and emails can vary in length, making VARCHAR ideal.

When to Use VARCHAR:

• Data with unpredictable or variable length
• Names, emails, addresses, and descriptions under 255-65535 characters
• Most general-purpose text fields

3. TEXT (Large Text Field)

TEXT is used to store large amounts of text like long descriptions, blog posts, comments, or articles.

Features:

• Meant for large text storage (up to 65,535 characters for standard TEXT in MySQL)
• Cannot have a default value (in some databases like MySQL)
• TEXT fields are stored outside the main table with a pointer reference
• Different variants exist (TINYTEXT, MEDIUMTEXT, LONGTEXT) for various sizes

Syntax:

column_name TEXT;

Example:

CREATE TABLE articles (
id INT,
title VARCHAR(255),
body TEXT
   );

body will store the full article content, which can be very large.

When to Use TEXT:

• Long-form text fields (comments, articles, reviews, reports)
• Data that exceeds normal VARCHAR limits
• When exact storage requirements are unknown or potentially very large

Quick Comparison: CHAR vs VARCHAR vs TEXT

Important Tips

• Use CHAR only when all values will be exactly the same length
• VARCHAR is the best choice for most standard text fields
• Reserve TEXT for content that exceeds VARCHAR limits
• Consider VARCHAR(MAX) in SQL Server for large text that might need indexing
• Be aware that TEXT fields may have limitations on default values and full-text indexing

Definition: What Is a View?

A view in SQL is a virtual table based on the result of a SELECT query. It does not store data physically but provides a way to save and reuse complex queries.

Think of it as a named query that you can treat like a table.

Syntax to Create a View

CREATE VIEW view_name AS
SELECT columns
FROM table
WHERE condition;

Example:

CREATE VIEW high_salary_employees AS
SELECT id, name, salary
FROM employees
WHERE salary > 60000;

You can now query it like this:

SELECT * FROM high_salary_employees;

Key Features of Views

• Virtual: No data is stored — it's just a stored SQL statement.
• Reusable: Use the same logic without rewriting the query.
• Simplifies code: Abstracts complex joins and filters.
• Secures data: You can limit column access by creating views.

Why Use Views?

1. Simplify Complex Queries

If your query has multiple joins and filters, a view hides that complexity.

CREATE VIEW department_summary AS
SELECT d.name, COUNT(e.id) AS employee_count
FROM departments d
JOIN employees e ON d.id = e.department_id
GROUP BY d.name;

2. Reusability

Write once, reuse in multiple queries.

3. Security

Create views that only expose certain columns (e.g., no salary or personal info).

CREATE VIEW public_employees AS
SELECT name, department_id
FROM employees;

4. Logical Data Independence

If the base table changes, you only need to update the view instead of rewriting every query.

Updating Views

Some views are updatable — meaning you can run INSERT, UPDATE, or DELETE on them — but this depends on the view definition and database system.

Updatable View:

CREATE VIEW simple_view AS
SELECT id, name FROM employees;

You can update this view because it maps directly to a single base table.

Materialized Views (Bonus Concept)

Some databases (e.g., PostgreSQL, Oracle) support materialized views, which store the result of the query on disk. They are refreshed manually or on schedule and are useful for performance on large datasets.

Limitations of Views

• Can't always perform DML (INSERT/UPDATE) on complex views
• May slow down performance if overused or poorly written
• Some databases have restrictions on what a view can contain (like LIMIT or ORDER BY)