This guide demonstrates practical implementation of indexes and views in Microsoft SQL Server 2017 using a teaching information management schema. All operations are performed via T-SQL scripts, validated through execution plans, performance metrics, and permission testing.

Index Design and Management

Indexes accelerate data retrieval by providing efficient lookup paths. In this scenario, three index types are implemented to address common query patterns:

• Nonclustered index on Student.Sdept: Optimizes department-based filtering. Since Sno is the primary key (and thus implicitly clustered), a nonclustered index is appropriate.
• Unique nonclustered index on Course.Cname: Enforces uniqueness while enabling fast lookups by course name—complementing declarative constraints with performance benefits.
• Composite nonclustered index on SC.Sno, SC.Grade: Designed for queries filtering by student ID first, then grade range. Column order matters: Sno appears before Grade to support equality + range predicates efficiently.

To inspect index metadata, use:

EXEC sp_helpindex 'Student';
EXEC sp_helpindex 'Course';
EXEC sp_helpindex 'SC';

Attempting to create a second clustered index (e.g., on Student.Sno) will fail with error 1902, confirming that only one clustered index per table is allowed.

View Implementation Strategies

Views serve as virtual tables defined by stored queries. They simplify access, abstract complexity, and enforce security boundaries.

Filtered Single-Table View

A view isolating students from the "School of Computer Science and Technology":

CREATE VIEW vw_cs_students AS
SELECT 
  student_id = Sno,
  full_name = Sname,
  gender = Ssex,
  age = Sage
FROM Student
WHERE Sdept = '计算机科学与技术学院';

Computed Column View

A view enriching course data with dynamically derived categories:

CREATE VIEW vw_course_levels AS
SELECT 
  course_code = Cno,
  title = Cname,
  credits = Ccredit,
  level = CASE 
            WHEN Ccredit <= 2 THEN 'LowCredit'
            WHEN Ccredit BETWEEN 3 AND 4 THEN 'MediumCredit'
            ELSE 'HighCredit'
          END
FROM Course;

Join-Based View

A denormalized view linking students and their graded enrollments:

CREATE VIEW vw_enrollment_grades AS
SELECT 
  s.Sname AS student_name,
  s.Sno AS student_id,
  sc.Cno AS course_code,
  sc.Grade AS score
FROM Student s
INNER JOIN SC sc ON s.Sno = sc.Sno
WHERE sc.Grade IS NOT NULL;

Note: This view supports updates to updatable columns (e.g., Sage via vw_cs_students), but inserts must satisfy the underlying WHERE condition—attempting to insert a non-CS student fails gracefully.

Performance Validation

Use SET STATISTICS TIME ON to measure CPU and elapsed time before and after index creation:

SET STATISTICS TIME ON;

-- Indexed query
SELECT * FROM Student WHERE Sdept = '计算机科学与技术学院';

-- Unique-indexed query  
SELECT * FROM Course WHERE Cname = '数据库原理';

-- Composite-indexed query
SELECT * FROM SC WHERE Sno = '2023010101' AND Grade BETWEEN 80 AND 90;

SET STATISTICS TIME OFF;

Observed improvements includee sub-10ms CPU time for previously slow scans—especially noticeable when SC contains ≥20 rows.

Security Enforcement via Views

Views enable granular access control. For example:

-- Create limited-access user
CREATE USER demo_user FOR LOGIN [DOMAIN\user];
GO

-- Grant view-only access
GRANT SELECT ON vw_cs_students TO demo_user;
DENY SELECT ON Student TO demo_user;
GO

When authenticated as demo_user, queries against vw_cs_students succeed, but direct access to Student raises a permissions error—demonstrating how views act as secure data gateways.

Key Observations

• Index column order directly impacts usability: (Sno, Grade) enables efficient lookups for WHERE Sno = ? AND Grade BETWEEN ? AND ?, whereas (Grade, Sno) does not.
• Computed columns (e.g., level in vw_course_levels) cannot be updated directly—modifications must target base-table columns.
• While indexes speed up reads, excessive indexing degrades write performance due to maintenance overhead during INSERT/UPDATE/DELETE.
• Views with aggregates, DISTINCT, or complex joins may become read-only unless defined with WITH CHECK OPTION or backed by updatable base structures.