SQL Server — Core Concepts with examples

 

• Data Definition Language (DDL): CREATE, ALTER, DROP (tables, views, procedures, triggers).

• Data Manipulation Language (DML): SELECT, INSERT, UPDATE, DELETE.

• Constraints: PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, DEFAULT.

• Indexes: clustered vs non-clustered, covering indexes, filtered indexes.

• Stored Procedures: encapsulate logic, enforce controlled access.

• Functions: scalar, table-valued, system functions (e.g., LEN, LEFT, RIGHT, REPLICATE).

• Triggers: automatic actions on INSERT, UPDATE, DELETE.

• Transactions: BEGIN TRAN, COMMIT, ROLLBACK for consistency.

• Security: roles, permissions, encryption, dynamic data masking.

Advanced SQL Server Features

• Views: virtual tables for abstraction and security.

• CTEs (Common Table Expressions): recursive queries, cleaner logic.

• Window Functions: ROW_NUMBER(), RANK(), LEAD(), LAG().

• Partitioning: split large tables for performance.

• Stored Procedure Best Practices: validation, error handling, logging.

• Dynamic SQL: build queries at runtime (with caution).

• Performance Tuning: query plans, indexes, statistics, caching.

• High Availability: replication, mirroring, AlwaysOn availability groups.

API Security (Angular + .NET + SQL Server)

• Frontend Validation (Angular): user experience only, not security.

• Backend Validation (.NET): enforce rules, limits, sanitization.

• Rate Limiting: prevent brute force attacks.

• Authentication: JWT, OAuth2, ASP.NET Identity.

• Authorization: role-based, claims-based.

• SQL Security: parameterized queries, stored procedures, deny direct table access.

• Audit Logging: track who did what, when.