Creating and Managing Views in SQL

Views in SQL are virtual tables created using queries to simplify data access and improve readability. While creating views is common, effectively managing them is equally important. This blog explores how to update, rename, drop, and maintain views to ensure optimal database performance, consistency, and easier long-term maintenance.

Updating Views in SQL

Why Update a View?

Updating a view becomes necessary when:

• Schema changes occur (e.g., column additions or renaming in base tables)
• Business logic updates require new filtering or selection criteria
• Performance improvements are needed by simplifying the query inside the view

Syntax:

CREATE OR REPLACE VIEW view_name AS
SELECT column1, column2
FROM table_name
WHERE condition;

This statement will create the view if it doesn’t exist, or replace the existing one with the new query logic.

Example:

CREATE OR REPLACE VIEW active_customers AS
SELECT id, name
FROM customers
WHERE status = 'active';

Explanation:

• This view selects only customers whose status is 'active'.
• The CREATE OR REPLACE ensures that if the view already exists, it will be updated without needing to drop it manually.

Output (when queried):

SELECT * FROM active_customers;

Sample Result:

id	name
1	Alice Smith
3	John Ray

Tips:

• Prefer CREATE OR REPLACE for safe updates without breaking dependencies.
• If the view is used in multiple reports or apps, test thoroughly before replacing.

3. Altering Views (If Supported)

Some databases like SQL Server and PostgreSQL support the ALTER VIEW statement to modify the logic of a view.

Syntax:

ALTER VIEW view_name AS
SELECT column1, column2
FROM table_name
WHERE condition;

Example (PostgreSQL or SQL Server):

ALTER VIEW active_customers AS
SELECT id, name, email
FROM customers
WHERE status = 'active';

Explanation:

• This modifies the existing active_customers view to also include the email field.
• Unlike CREATE OR REPLACE, ALTER VIEW is only available in certain RDBMS.

MySQL Note:

MySQL does not support ALTER VIEW. Instead, you must use:

CREATE OR REPLACE VIEW view_name AS ...

Output (after altering the view):

SELECT * FROM active_customers;

Updated Sample Result:

id	name	email
1	Alice Smith	alice@example.com
3	John Ray	john@example.com

4. Renaming a View

Renaming a view can be helpful when refactoring database objects or aligning naming conventions. The syntax varies depending on the database management system (DBMS).

Syntax (Depends on RDBMS):

MySQL & MariaDB:

RENAME TABLE old_view_name TO new_view_name;

Example:

RENAME TABLE active_customers TO current_customers;

PostgreSQL:

ALTER VIEW old_view_name RENAME TO new_view_name;

Example:

ALTER VIEW active_customers RENAME TO current_customers;

SQL Server:

EXEC sp_rename 'old_view_name', 'new_view_name';

Example:

EXEC sp_rename 'active_customers', 'current_customers';

Explanation:

• These commands allow you to rename an existing view without altering its logic.
• Renaming does not affect the view’s structure or data, but all references to the old view name in your application or reports must be updated accordingly.

Dropping a View

When a view is no longer needed, or if you want to completely replace it from scratch, you can drop it from the database.

Syntax:

DROP VIEW view_name;

Example:

DROP VIEW inactive_customers;

Explanation:

• This command removes the view from the database.
• Any queries or reports that reference the view will fail after it’s dropped.

Caution:

• Dropping a view is permanent—there’s no undo.
• Before dropping a view, check for dependencies, such as:
  • Other views that depend on it
  • Stored procedures or functions
  • Application code or reports

Use tools like INFORMATION_SCHEMA.VIEWS (MySQL/PostgreSQL) or sys.sql_dependencies (SQL Server) to audit dependencies before deletion.

Views in SQL

sql
CREATE VIEW EmployeeList AS
SELECT EmployeeID, FirstName, LastName, Department
FROM Employees
WHERE Status = 'Active';

SELECT * FROM EmployeeList;

ALTER VIEW EmployeeList AS
SELECT EmployeeID, FirstName, LastName, Department, Salary
FROM Employees
WHERE Status = 'Active';

DROP VIEW EmployeeList;
  

Explanation of the Code


In this exercise, let’s break down the SQL code step by step to enhance understanding of its functionality. Here’s how it works:

1. The initial command `CREATE VIEW EmployeeList AS…` creates a new view named “EmployeeList.” This view is essentially a virtual table that pulls specified columns from the “Employees” table, displaying only those employees whose `Status` is marked ‘Active’.


2. The subsequent command `SELECT * FROM EmployeeList;` is straightforward. It retrieves all rows and columns defined in “EmployeeList”, allowing users to interact with the data subset effectively.


3. Then, `ALTER VIEW EmployeeList AS…` modifies the existing view by adding a new column, `Salary`, enabling additional data insight while keeping the filtering condition intact.


4. Finally, `DROP VIEW EmployeeList;` is a command to remove the “EmployeeList” view from the database, useful when it’s no longer needed.

This series of commands demonstrates how views can streamline data access and manipulation in databases.

Output

EmployeeID | FirstName | LastName | Department
-----------|-----------|----------|-----------
1          | John      | Doe      | HR
2          | Jane      | Smith    | IT

EmployeeID | FirstName | LastName | Department | Salary
-----------|-----------|----------|------------|-------
1          | John      | Doe      | HR         | 50000
2          | Jane      | Smith    | IT         | 60000

Practical Uses of Views in SQL

Now, let’s pivot to discuss how businesses use these concepts practically:

1. Retail Industry Simplifications: Retail businesses often use Views to gain insights into customer purchasing habits. By creating a View summarizing customer purchase data monthly, they can tailor marketing campaigns to fit consumer behaviour closely.
2. Tech Company Analytics: Tech giants need quick, efficient ways to assess user interaction data on their platforms. Using Views, they can extract only essential user interaction details from the larger data logs, simplifying analysis and decision-making processes.
3. Finance Sector Efficiency: Financial firms frequently create Views to streamline daily transaction tracking. This allows them to isolate and examine only current transactions, speeding up the reconciliation and auditing processes.
4. Healthcare Data Security: In the healthcare industry, Views are often employed to ensure patient privacy. By creating Views that expose only non-sensitive patient data, healthcare providers can remain compliant with data protection laws while allowing authorized data access for analytical purposes.

As you can see, Views are incredibly versatile, fitting into various business needs seamlessly. Whether it’s simplifying complex data analysis or enhancing data security, Views are a fundamental component of modern data management strategies. So, next time you’re working with SQL, consider how Views might come in handy!

Checking View Definition

Understanding the underlying logic of a view is crucial for maintenance, debugging, and auditing. Each RDBMS offers a way to retrieve the SQL used to define an existing view.

Use Cases:

• Auditing view definitions for correctness or compliance
• Troubleshooting issues in business logic
• Comparing current definitions before applying updates

Syntax (varies by DBMS):

MySQL:

SHOW CREATE VIEW view_name;

This command displays the exact SQL used to create the view, including column aliases and SELECT logic.

PostgreSQL:

SELECT definition FROM pg_views WHERE viewname = 'view_name';

You can also query pg_catalog.pg_views or use built-in functions to get more metadata.

SQL Server:

sp_helptext 'view_name';

This system stored procedure displays the complete definition of the view, line by line.

Best Practices for Managing Views

Use descriptive names:
Avoid generic names like view1 or temp_view. Use names that reflect the purpose of the view, such as monthly_sales_summary or active_user_logins.

Version views during updates:
When making significant changes, create a new version like sales_report_v2 instead of replacing the original immediately. This allows you to test without disrupting existing systems.

Avoid complex joins or aggregations in frequently accessed views:
Overly complex logic can impact performance. If needed, break down logic into multiple smaller views or use materialized views (if supported) for heavy operations.

Document the purpose and logic of each view:
Maintain clear documentation for each view—its role, dependencies, and key columns—to help teams understand and maintain the database structure.

Monitor performance impacts:
Use database profiling tools to identify if any view is slowing down queries. Refactor or optimize views based on usage and performance metrics.

Have you ever wished you could instantly write, run, and test your SQL code? Our AI-powered SQL online compiler makes this dream a reality. It streamlines your coding process, allowing you to focus on creating efficient database queries without any hassle.

Conclusion

Creating and Managing Views in SQL enhances your ability to efficiently organise and query data. Completing this guide boosts your confidence in handling databases, empowering you to solve complex problems. Ready to dive deeper? Explore more programming languages on Newtum and expand your coding skills.

Edited and Compiled by

This article was compiled and edited by @rasikadeshpande, who has over 4 years of experience in writing. She’s passionate about helping beginners understand technical topics in a more interactive way.

About The Author