Overview

A database is an organized store of information, kept in tables of rows and columns, a bit like a set of spreadsheets that many programs can share at once. SQL Server is Microsoft's database program, and T-SQL is the language you use to ask it questions and change its data.

This section of The Geeks Forge is a knowledge base for looking after SQL Server and writing good T-SQL. Many pages follow the same pattern: a PowerShell script collects facts from the servers, SQL Server stores them, and a simple web page (built with Blazor) shows them so problems are easy to spot.

Where to begin

If you are new, start with Inventory baseline: knowing which servers you have comes before backup or index work. Then read the Maintenance Outline, which shows how all the topics fit together and what to learn next. Every page is also in the menu on the left, in the same groups as below.

Start here

• Maintenance Outline — a map of the whole section, showing which topics to learn in which order

Ops maintenance (01-10)

• Agent jobs — keep track of the scheduled tasks SQL Server runs on its own, and spot ones that fail or run too long
• Alerting & runbooks — send alerts that matter, let people acknowledge them, and link each alert to short fix-it steps
• Backup & restore — make sure every database has a recent backup copy, and that the copy can really be restored
• Capacity & growth — watch how fast databases grow so the disks never fill up by surprise
• HA/DR — understand the backup-server setups that keep databases running when a server fails (high availability and disaster recovery)
• Index & statistics — keep the lookup aids (indexes) and data summaries (statistics) that make queries fast in good shape
• Inventory baseline — make a list of every database server you look after and notice when its settings change
• Patching & upgrades — keep every server on a current, supported version and plan update windows
• Performance health — find out why a server is slow: what it is waiting on, what is blocked, and which queries cost the most
• Security hygiene — see who has powerful access to your servers and catch risky changes early

Stored Procedures

• Config / instance procs — built-in procedures for viewing and changing server-wide settings
• Extended procs (xp) — the powerful extended procedures (names starting xp_), why they are risky, and safer choices
• Object help procs — built-in procedures that describe tables, databases and files
• Security & principals procs — built-in procedures for logins, users and permissions
• SQL Agent procs — built-in procedures for managing scheduled jobs
• Stored procedures — saved, reusable blocks of SQL code that apps call by name, and how to write them well
• System stored procedures — the built-in procedures that come with SQL Server and how to call them

Functions

• Built-in functions — the ready-made functions for text, numbers, dates and more, and when to use them
• User Defined Functions — write your own functions in T-SQL, and which kinds can slow queries down

T-SQL deep-dives (13-17)

• APPLY & set-based — handle many rows at once instead of one at a time, using the APPLY operator
• Dynamic SQL — building SQL code as text at run time, when it makes sense, and how to keep it safe from attacks
• Error handling — catch and log errors in T-SQL so a failure does not leave half-finished work behind
• Indexing for procs — practical indexes that make the procedures behind dashboards fast
• Temp vs table vars — two ways to hold rows for a short time while a query runs, and when each one is better

T-SQL deep-dives (18-23)

• MERGE & upserts — insert a row if it is new or update it if it already exists, safely, even when run twice
• Query Store — use Query Store, SQL Server's built-in history of query performance, to catch slowdowns
• Synonyms & cross-DB — short alias names that point to objects in other databases, and when to use them
• Temporal / history — tables that automatically keep every past version of a row, so you can see what changed and when
• Transactions & isolation — group changes so they all succeed or all fail, and control what other users see meanwhile
• TVPs Blazor/PS — send a whole table of rows to SQL Server in one call from a web app or PowerShell (table-valued parameters)

T-SQL deep-dives (24-29)

• Constraints — rules built into tables that stop bad or duplicate data from being saved
• Extended Events — use Extended Events, a lightweight tracing tool, to catch blocking and slow procedure calls
• Filtered indexes — indexes that cover only some rows, and columns calculated from other columns
• JSON in T-SQL — read and produce JSON (a common text data format) inside SQL Server
• Pagination — show long lists one page at a time, quickly and in a stable order
• Parameter sniffing — why the same procedure can be fast one time and slow the next, and measured ways to fix it

T-SQL deep-dives (30+)

• Common Table Expressions — name a query step with WITH so long queries read top to bottom, including queries that walk a tree

Implementation spikes

• Agent fail board — a web page listing scheduled jobs that failed, ran too long or were switched off
• Alert evaluate + ack — turn findings into alerts that close themselves when fixed and can be acknowledged on a web page
• Backup heat map — a color-coded web page showing which databases are behind on backups
• Build out-of-date — a web list of servers whose SQL Server version is behind the target
• Capacity tight volumes — a web card showing disks that are nearly full and the databases growing fastest
• Inventory MVP — a first working build: collect server facts, store them in SQL Server and show them in a web page
• Perf triage board — a web page showing what servers are waiting on, what is blocked and the costliest queries right now
• Security exceptions — a web page listing risky access findings, such as too many administrators

SQL CLI

• Azure CLI SQL — create and manage SQL databases in Microsoft's Azure cloud from the command line
• bcp — copy large amounts of data in and out of tables with the bcp command-line tool
• sqlcmd / go-sqlcmd — run SQL from a command prompt or script with the sqlcmd tool
• sqlpackage — package a database's design into a file and deploy it elsewhere with sqlpackage
• SqlServer PowerShell module — work with SQL Server from PowerShell using Microsoft's SqlServer module