T-SQL Essentials: Schema Management, Querying, and Data Aggregation

Data Type Selection & Storage Characteristics

When defining schema objects, selecting the appropriate storage type is critical for performance and data integrity. Reserved identifiers such as name or order must be escaped using square brackets [Identifier].

String & Numeric Formats

Fixed vs Variable Length: CHAR(n) reserves exactly n bytes regardless of content size. VARCHAR(n) consumes only the actual byte count plus overhead, capped at n.
Unicode Support: Prefixing string types with 'n' enables UTF-16 storage. NVARCHAR(100) accommodates up to 100 characters, regardless of whether they are ASCII or multi-byte CJK symbols (each character uses 2 bytes).
Legacy Text: TEXT handles large payloads but is deprecated in modern architectures; consider VARCHAR(MAX) instead.

Date, Time, & Precision

DATE: Calendar date only.
DATETIME / SMALLDATETIME: Includes time components, with varying resolution ranges.
DECIMAL(p,s) / MONEY: Preferred over FLOAT for financial or exact arithmetic due to precision guarantees.

Database & Object Initialization

Creating a dedicated workspace requires specifying primary data files and transaction logs. Below demonstrates a secure initialization pattern that drops existing instances before building fresh structures.

-- Safely reset environment
IF EXISTS(SELECT name FROM sys.databases WHERE name = 'CorpDB')
    DROP DATABASE CorpDB;
GO

CREATE DATABASE CorpDB
CONTAINMENT = NONE
ON PRIMARY
(NAME = N'CorpDB_Primary',
 FILENAME = N'C:\SQLData\CorpDB.mdf',
 SIZE = 10MB, MAXSIZE = UNLIMITED, FILEGROWTH = 5MB)
LOG ON
(NAME = N'CorpDB_Log',
 FILENAME = N'C:\SQLData\CorpDB_log.ldf',
 SIZE = 5MB, MAXSIZE = 10GB, FILEGROWTH = 10%)
WITH CATALOG_COLLATION = DATABASE_DEFAULT;
GO

USE CorpDB;
GO

Table Construction & Constraint Implementation

Relational integrity relies heavily on declarative constraints. Primary keys enforce uniqueness and clustering preferences, while identity properties automate sequence generation. Check, unique, default, and foreign key rules maintain domain consistency.

-- Department Registry
IF OBJECT_ID('dbo.Departments', 'U') IS NOT NULL DROP TABLE dbo.Departments;
CREATE TABLE dbo.Departments
(
    DeptID INT IDENTITY(1,1) CONSTRAINT PK_Departments PRIMARY KEY,
    DeptName NVARCHAR(100) NOT NULL,
    DeptDesc VARCHAR(MAX)
);

-- Career Ladder
IF OBJECT_ID('dbo.Levels', 'U') IS NOT NULL DROP TABLE dbo.Levels;
CREATE TABLE dbo.Levels
(
    LevelID INT IDENTITY(1,1) CONSTRAINT PK_Levels PRIMARY KEY,
    LevelName NVARCHAR(50) NOT NULL,
    LevelDesc VARCHAR(MAX)
);

-- Staff Roster with Referential Integrity
IF OBJECT_ID('dbo.Employees', 'U') IS NOT NULL DROP TABLE dbo.Employees;
CREATE TABLE dbo.Employees
(
    EmpID INT IDENTITY(1,1) CONSTRAINT PK_Employees PRIMARY KEY,
    AssignedDept INT CONSTRAINT FK_Emp_Dept FOREIGN KEY REFERENCES dbo.Departments(DeptID),
    AssignedLevel INT CONSTRAINT FK_Emp_Level FOREIGN KEY REFERENCES dbo.Levels(LevelID),
    FullName NVARCHAR(80) NOT NULL,
    Gender CHAR(1) DEFAULT('M') CHECK(Gender IN ('M','F')) NOT NULL,
    HireDate DATETIME2 NOT NULL,
    MonthlyPay DECIMAL(10,2) CHECK(MonthlyPay BETWEEN 5000 AND 999999) NOT NULL,
    ContactTel NVARCHAR(20) UNIQUE NOT NULL,
    OfficeLoc VARCHAR(200),
    OnboardedAt DATETIME2 DEFAULT(GETDATE())
);

Schema Evolution

Post-deployment adjustments utilize ALTER TABLE. Exercise caution when modifying populated columns, as data type or width changes often trigger truncation errors if existing values exceed new bounds.

-- Append a new tracking field
ALTER TABLE dbo.Employees ADD EmailAddr NVARCHAR(150);

-- Modify column definition
ALTER TABLE dbo.Employees ALTER COLUMN OfficeLoc VARCHAR(300);

-- Remove obsolete field
ALTER TABLE dbo.Employees DROP COLUMN EmailAddr;

Constraint Maintenance

Named constraints facilitate lifecycle management. System-assigned names can be discovered via catalog views; alternative, aliasing improves readability.

-- Enforce business rule post-creation
ALTER TABLE dbo.Employees ADD CONSTRAINT CHK_PayRange CHECK(MonthlyPay >= 10000 AND MonthlyPay <= 800000);

-- Apply composite uniqueness
ALTER TABLE dbo.Employees ADD CONSTRAINT UQ_Emp_Tel UNIQUE(ContactTel);

-- Set fallback value dynamically
ALTER TABLE dbo.Employees ADD CONSTRAINT DF_HireDefault DEFAULT(GETUTCDATE()) FOR HireDate;

Record Insertion Strategies

Bulk loading benefits from set-based operations rather than iterative single-row statements.

-- Populate hierarchy tables efficiently
INSERT INTO dbo.Departments(DeptName, DeptDesc)
VALUES ('Engineering', 'Core Dev Team'),
       ('QA_Ops', 'Testing & Deployment');

INSERT INTO dbo.Levels(LevelName, LevelDesc)
VALUES ('Junior', 'Entry Tier'),
       ('Senior', 'Lead Tier');

-- Multi-row staff ingestion
INSERT INTO dbo.Employees(AssignedDept, AssignedLevel, FullName, Gender, HireDate, MonthlyPay, ContactTel, OfficeLoc)
SELECT 1, 1, 'Alice Chen', 'F', '2023-01-15', 12500.00, '13800138001', 'Shanghai' UNION ALL
SELECT 2, 2, 'Bob Wang', 'M', '2022-06-10', 18000.50, '13800138002', 'Hangzhou';

Update & Deletion Mechanisms

Data modification targets subset rows via predicates. Omitting a WHERE clause applies transformations globally, which should be validated first.

-- Universal adjustment
UPDATE dbo.Employees SET MonthlyPay = MonthlyPay + 1500;

-- Targeted correction
UPDATE dbo.Employees 
SET MonthlyPay = 22000, OfficeLoc = 'Beijing' 
WHERE EmpID = 5;

-- Conditional tier adjustment
UPDATE dbo.Employees 
SET MonthlyPay = 15000 
WHERE AssignedDept = 1 AND MonthlyPay < 14000;

Row removal supports two distinct approaches:

DELETE [FROM] table_name [WHERE condition]: Logs individual row removals. Identity counters persist.
TRUNCATE TABLE table_name: Resets data pages instantly, deallocates extents, resets identity seeds to start. Cannot use predicates and bypasses triggers.

-- Archive before clearing
TRUNCATE TABLE dbo.Employees;

Projection & Deduplication

Basic retrieval leverages SELECT with optional column aliases and set normalization.

-- Standard projection
SELECT EmpID AS ID, FullName AS Name, MonthlyPay AS Compensation FROM dbo.Employees;

-- Eliminate duplicates
SELECT DISTINCT AssignedDept FROM dbo.Employees;

Filtering Logic & Temporal Handling

Predicates support arithmetic, range checks, and logical negation (NOT, IN, BETWEEN). Date literals require strict formatting or implicit conversion awareness.

-- Range boundaries
SELECT FullName FROM dbo.Employees WHERE HireDate >= '2023-01-01' AND HireDate < '2023-12-31';

-- Negated membership
SELECT FullName FROM dbo.Employees WHERE AssignedLevel NOT IN (1, 2);

Sorting Techniques

Output sequencing defaults to ascending but accepts explicit direction modifiers. Expressions enable dynamic ordering.

-- Ascending/Descending
SELECT FullName FROM dbo.Employees ORDER BY MonthlyPay DESC;

-- Functional ordering
SELECT FullName FROM dbo.Employees ORDER BY LEN(FullName);

-- Volume capping
SELECT TOP 5 FullName FROM dbo.Employees ORDER BY EmpID;
SELECT TOP 10 PERCENT FullName FROM dbo.Employees ORDER BY NEWID();

Null State Identification

Empty cells differ from empty strings. Always use existential operators for undefined states.

-- Identify missing references
SELECT FullName FROM dbo.Employees WHERE OfficeLoc IS NULL;

-- Explicit empty check
SELECT FullName FROM dbo.Employees WHERE OfficeLoc = '';

Date Component Extraction

Temporal analysis often isolates calendar parts for cohort grouping.

-- Filter by birth year
SELECT FullName FROM dbo.Employees WHERE YEAR(HireDate) = 2023;

-- Current period comparison
SELECT FullName FROM dbo.Employees WHERE MONTH(GETDATE()) = MONTH(HireDate);

Categorization Patterns

Conditional logic transforms raw values into readable categories using procedural branching.

-- Decade cohort classification
SELECT FullName,
       CASE 
           WHEN YEAR(HireDate) BETWEEN 2020 AND 2023 THEN 'Recent Hires'
           ELSE 'Veteran Staff'
       END AS Cohort
FROM dbo.Employees;

Pattern Matching Engine

The LIKE operator leverages wildcard syntax for substring discovery. Character classes allow precise positioning control.

Symbol	Behavior
`_`	Single character placeholder
`%`	Zero or more characters
`[abc]`	Match any specified character
`[^abc]`	Exclude specified characters

-- Prefix search
SELECT FullName FROM dbo.Employees WHERE FullName LIKE 'C%';

-- Internal containment
SELECT FullName FROM dbo.Employees WHERE FullName LIKE '%Wang%';

-- Exact length validation
SELECT FullName FROM dbo.Employees WHERE FullName LIKE '__Chen';

-- Complex numeric patterning
SELECT ContactTel FROM dbo.Employees WHERE ContactTel LIKE '13[8-9]%[0-5]';

Statistical Aggregation

Rollup functions condense datasets into summary metrics. Null values are excluded from AVG(), SUM(), and COUNT(column) automatically.

-- Core metrics
SELECT 
    COUNT(EmpID) AS Headcount,
    MAX(MonthlyPay) AS PeakCompensation,
    MIN(MonthlyPay) AS EntryRate,
    SUM(MonthlyPay) AS PayrollOutlay,
    ROUND(AVG(MonthlyPay), 2) AS MeanSalary
FROM dbo.Employees;

Threshold Comparisons

Subqueries enable cross-referencing aggregate baselines against individual records.

-- Outlier detection
SELECT FullName, MonthlyPay 
FROM dbo.Employees 
WHERE MonthlyPay > (SELECT AVG(MonthlyPay) FROM dbo.Employees);

Chronological Derivatives

Age and tenure calculasions rely on temporal deltas.

-- Tenure estimation
SELECT 
    FullName,
    DATEDIFF(YEAR, HireDate, GETDATE()) AS YearsService
FROM dbo.Employees;

Grouped Analytics

Aggregation pipelines transform granular rows into summarized dimensions. Execution precedence dictates WHERE filters raw rows, GROUP BY forms buckets, and HAVING validates bucket aggregates.

-- Location-based payroll summary
SELECT 
    OfficeLoc AS Region,
    COUNT(*) AS StaffCount,
    SUM(MonthlyPay) AS RegionalBudget,
    ROUND(AVG(MonthlyPay), 2) AS AvgComp,
    MAX(MonthlyPay) AS TopEarner,
    MIN(MonthlyPay) AS FloorPay
FROM dbo.Employees
WHERE YEAR(HireDate) < 2023
GROUP BY OfficeLoc;

-- Filtered group inclusion
SELECT 
    OfficeLoc AS Region,
    COUNT(*) AS StaffCount,
    SUM(MonthlyPay) AS RegionalBudget
FROM dbo.Employees
WHERE YEAR(HireDate) < 2023
GROUP BY OfficeLoc
HAVING COUNT(*) >= 3;

Tags: T-SQL Microsoft-SQL-Server database-design SQL-Querying Data-Aggregation

Posted on Thu, 30 Jul 2026 16:31:01 +0000 by LeeReed

Freaks City