SQL VIEW

SQL
POSTGRES
INTERACTIVE

Complete SQL VIEW Technical Guide

1. Fundamental Syntax 
CREATE [OR REPLACE] VIEW view_name [(column_list)]
AS
    SELECT_statement
[WITH [CASCADED | LOCAL] CHECK OPTION];
  1. OR REPLACE: Updates existing view if it already exists
  2. column_list: Optional explicit column names for the view
  3. SELECT_statement: The query that defines the view's data
  4. CHECK OPTION: Ensures data modifications through the view comply with the view definition
2. View Characteristics
Virtual Tables
-- Simple view example
CREATE VIEW active_customers AS
SELECT id, name, email, last_purchase_date
FROM customers
WHERE status = 'active'
AND last_purchase_date > CURRENT_DATE - INTERVAL '1 year';
Materialized Views
-- PostgreSQL materialized view
CREATE MATERIALIZED VIEW monthly_sales_summary AS
SELECT 
    date_trunc('month', order_date) AS month,
    SUM(total_amount) AS total_sales,
    COUNT(*) AS order_count
FROM orders
GROUP BY month
WITH DATA;

-- Refresh materialized view
REFRESH MATERIALIZED VIEW monthly_sales_summary;
3. View Operations
Updating Through Views
-- Updatable view example
CREATE VIEW customer_emails AS
SELECT id, name, email
FROM customers
WHERE email IS NOT NULL;

-- Update through view
UPDATE customer_emails
SET email = 'new@example.com'
WHERE id = 123;
View Alterations
-- Alter view (MySQL)
ALTER VIEW active_customers AS
SELECT id, name, email, phone, last_purchase_date
FROM customers
WHERE status = 'active'
AND last_purchase_date > CURRENT_DATE - INTERVAL '6 months';

-- Drop view
DROP VIEW IF EXISTS old_customer_view;
4. Advanced View Concepts
Recursive Views
-- Hierarchical data with recursive view
CREATE RECURSIVE VIEW org_chart (employee_id, name, manager_id, level) AS
-- Base case
SELECT id, name, manager_id, 0
FROM employees
WHERE manager_id IS NULL

UNION ALL

-- Recursive case
SELECT e.id, e.name, e.manager_id, oc.level + 1
FROM employees e
JOIN org_chart oc ON e.manager_id = oc.employee_id;
Security with Views
-- Column-level security view
CREATE VIEW secure_employee_data AS
SELECT 
    id,
    first_name,
    last_name,
    department,
    '***-**-' || RIGHT(ssn::TEXT, 4) AS masked_ssn,
    CASE 
        WHEN current_user = hr_manager THEN salary
        ELSE NULL
    END AS salary
FROM employees;
5. Database-Specific View Features
SQL Server Indexed Views
-- Create schema-bound view with index
CREATE VIEW dbo.sales_summary WITH SCHEMABINDING AS
SELECT 
    product_id,
    COUNT_BIG(*) AS sales_count,
    SUM(quantity) AS total_quantity,
    SUM(quantity * unit_price) AS total_sales
FROM dbo.sales
GROUP BY product_id;

-- Create clustered index on the view
CREATE UNIQUE CLUSTERED INDEX IDX_sales_summary
ON dbo.sales_summary (product_id);
Oracle View Features
-- Oracle object view
CREATE VIEW employee_obj_view OF employee_type
WITH OBJECT IDENTIFIER (employee_id) AS
SELECT 
    e.employee_id,
    e.name,
    address_type(e.street, e.city, e.state, e.zip) AS address,
    phone_array_type(
        phone_type('work', e.work_phone),
        phone_type('mobile', e.mobile_phone)
    ) AS phones
FROM employees e;
6. Performance & Best Practices
  1. Query Simplification: Views can simplify complex queries into reusable components
  2. Security Layer: Restrict column and row access through views
  3. Performance Impact: Views don't store data (except materialized views) and execute their underlying query each time
  4. Nested Views: Limit nesting depth to avoid performance degradation
  5. Documentation: Clearly document view purposes and dependencies
Optimization Example
-- Efficient view with proper indexing
CREATE VIEW fast_order_details AS
SELECT 
    o.order_id,
    o.order_date,
    c.customer_name,
    p.product_name,
    od.quantity,
    od.unit_price
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_details od ON o.order_id = od.order_id
JOIN products p ON od.product_id = p.product_id
WHERE o.order_date > CURRENT_DATE - INTERVAL '3 months'
WITH CHECK OPTION;

Advanced VIEW Topics

1. View Dependencies and Impact Analysis

Understanding how views depend on other database objects and analyzing the ripple effects of schema changes. 

-- Example: Finding view dependencies in SQL Server
SELECT 
    referencing_schema_name,
    referencing_entity_name,
    referenced_schema_name,
    referenced_entity_name
FROM sys.dm_sql_referencing_entities('dbo.base_table', 'OBJECT');
  1. Cross-database dependencies: Views that reference tables in other databases
  2. Dynamic SQL in views: Hidden dependencies in views using dynamic SQL
  3. Schema binding implications: How WITH SCHEMABINDING affects dependency tracking
2. Advanced Materialized View Strategies

Optimization techniques for materialized views in high-transaction environments. 

-- PostgreSQL incremental refresh example
CREATE MATERIALIZED VIEW mv_order_stats AS
SELECT customer_id, COUNT(*) as order_count
FROM orders
GROUP BY customer_id;

-- Incremental refresh function
CREATE FUNCTION refresh_mv_order_stats() RETURNS void AS $$
BEGIN
    REFRESH MATERIALIZED VIEW CONCURRENTLY mv_order_stats;
END;
$$ LANGUAGE plpgsql;
  1. Incremental refresh: Only updating changed data instead of full rebuilds
  2. Staleness tolerance: Configuring acceptable data freshness levels
  3. Distributed MV consistency: Handling MVs across database clusters
3. Advanced View-Based Security Models

Implementing row-level and column-level security through views with performance considerations. 

-- Row-level security view example
CREATE VIEW secure_employee_data AS
SELECT * FROM employees
WHERE department_id = 
    (SELECT department_id FROM user_departments 
     WHERE user_id = CURRENT_USER);

-- Column masking example
CREATE VIEW masked_customer_data AS
SELECT 
    id,
    name,
    '****' || SUBSTRING(credit_card, -4) AS masked_cc,
    CASE 
        WHEN is_manager(CURRENT_USER) THEN email
        ELSE 'restricted' 
    END AS email
FROM customers;
  1. Dynamic data masking: Runtime redaction of sensitive data
  2. Context-aware views: Views that change based on execution context
  3. Performance overhead: Measuring security view impact on query speed
4. View Optimization Techniques

Advanced methods to improve view performance beyond basic indexing. 

-- SQL Server indexed view with NOEXPAND hint
CREATE VIEW dbo.vw_SalesSummary WITH SCHEMABINDING AS
SELECT product_id, SUM(quantity) as total_qty
FROM dbo.sales
GROUP BY product_id;

-- Query using the indexed view
SELECT * FROM dbo.vw_SalesSummary WITH (NOEXPAND)
WHERE product_id BETWEEN 100 AND 200;
  1. Query plan forcing: Directing the optimizer to use specific view execution plans
  2. Nested view flattening: How databases optimize chains of views
  3. Materialized view selection: Automatic MV usage in query optimization
5. Temporal and Versioned Views

Implementing time-travel and historical data access patterns through views. 

-- Temporal table view example (SQL Server)
CREATE VIEW vw_EmployeeHistory AS
SELECT 
    e.*,
    ht.valid_from,
    ht.valid_to
FROM Employees FOR SYSTEM_TIME ALL e
JOIN EmployeeHistory ht ON e.id = ht.id;

-- Point-in-time query
SELECT * FROM vw_EmployeeHistory
WHERE valid_from <= '2023-01-01'
  AND valid_to > '2023-01-01';
  1. Time-based partitioning: Views that automatically filter by time ranges
  2. Slowly changing dimensions: View patterns for Type 2 SCDs
  3. Version comparison views: Highlighting differences between data versions
6. Distributed Database Views

Implementing views across sharded, partitioned, or federated database systems. 

-- PostgreSQL foreign data wrapper view
CREATE VIEW global_customers AS
SELECT * FROM local_customers
UNION ALL
SELECT * FROM customers_europe
UNION ALL
SELECT * FROM customers_asia;

-- Shard routing view example
CREATE VIEW sharded_orders AS
SELECT * FROM orders_shard1 WHERE shard_key BETWEEN 1 AND 1000
UNION ALL
SELECT * FROM orders_shard2 WHERE shard_key BETWEEN 1001 AND 2000;
  1. Shard-aware views: Intelligent routing to appropriate shards
  2. Cross-database federation: Views spanning different DBMS platforms
  3. Latency masking: Techniques to hide distributed query latency