SQL Server / Basic / Lesson 1

The engine, databases, tools, and batches

Build a reliable mental model of a SQL Server connection before writing business queries. Distinguish engine responsibilities, database context, client tools, and batch boundaries.

About 5 min readContent reviewed 2026-10-23

On this page

Lesson goals

Identify instance, database, schema, and session boundaries.
Inspect environment metadata without changing configuration.
Explain why GO belongs to a script tool.

Follow a request through the engine

A SQL Server instance is a running Database Engine service that can manage several databases. A client authenticates, opens a session, and submits T-SQL. The engine parses names and syntax, checks permissions, chooses an execution plan, and reads or changes pages through its storage components. The client displays results but does not decide relational correctness. Closing a query tab and stopping an instance are therefore very different operations.

An instance has shared resources such as memory and schedulers; databases contain their own objects and transaction logs. Busy work in one database can still affect another through those shared resources. Think of a connection as access to a service with an initial database, not as a file opened directly by your application. This model explains why server availability, credentials, and database permissions must be diagnosed separately.

Read context before acting

The following metadata query describes the session rather than inventing a universal server version. SERVERPROPERTY returns instance properties; DB_NAME reports the current database. Results vary with the environment. Record the engine major version, edition, database, and compatibility level when comparing behavior with documentation. Database compatibility is an independent setting that can preserve older optimizer behavior on a newer engine.

The course uses SQL Server 2019, major version 15, as its syntax baseline and labels selected SQL Server 2022 features explicitly. It makes no claim that either is the newest release. Edition, platform, permissions, and database options still determine which administrative features are usable. A tool version number is not an engine version number.

sql Read-only environment inspection

SELECT SERVERPROPERTY('ProductVersion') AS engine_version,
       SERVERPROPERTY('Edition') AS edition,
       DB_NAME() AS database_name;
SELECT name, compatibility_level
FROM sys.databases
WHERE database_id = DB_ID();

Expect environment-specific rows, not a fixed version string. Catalog visibility follows permissions.

Understand storage and system databases

A user database normally has data files and a transaction log. Data files hold allocated pages; the log records changes needed for recovery and rollback. A log is not a disposable debugging transcript. Removing it or shrinking it repeatedly does not solve the workload or backup issue that caused growth. File layout, capacity, growth increments, and recovery objectives belong in an operational design.

Know the system databases conceptually: master tracks instance-level information, model supplies a template for new databases, msdb supports facilities such as SQL Server Agent history, and tempdb supports temporary objects and engine workspaces. tempdb is recreated when the service starts. Do not place durable business records there. These roles help you ask the right administrator for help without editing system tables.

Separate a statement, a batch, and GO

A semicolon terminates a T-SQL statement. A batch is a group sent to SQL Server together. GO, placed on its own line, is a batch delimiter recognized by tools such as SSMS and sqlcmd; it is not T-SQL server syntax. Sending a string containing GO through a normal database driver produces a syntax problem unless the application deliberately implements script splitting.

Local variables exist only within their batch. In this example the first SELECT can read @label; a SELECT after GO cannot. GO does not mean COMMIT and does not automatically end an open transaction. A repeated GO count can rerun a batch, so never treat an unfamiliar script as harmless formatting. Stored-module creation also has batch requirements that will matter later.

sql A tool script containing two batches

DECLARE @label nvarchar(30) = N'Training';
SELECT @label AS label;
GO
SELECT N'New batch' AS label;

Expected output

label
Training

label
New batch

These are two separate result sets when a GO-aware tool submits the script. The second batch declares no dependency on @label.

Set boundaries for learning

All examples here are theory examples marked non-runnable because they were reviewed without executing a disposable SQL Server engine. They use synthetic values; administrative snippets require an expressly designated training environment and appropriate privileges. Never point a practice script at a configured application server simply because a connection string is available. Read the entire batch and identify any CREATE, ALTER, INSERT, UPDATE, DELETE, BACKUP, or RESTORE before deciding how to study it.

A useful learning record contains the question, expected rows, reasoning, and any environmental assumptions. For read-only examples, predict values before execution. For changes, explain both the success path and how state would be restored. Tool familiarity is valuable, but a visible grid and a green completion message are not evidence that a business rule was implemented correctly.

Common errors and fixes

Incorrect syntax near 'GO'.

Why it happens: A driver sent a tool delimiter directly to the engine.
How to fix it: Submit separate batches or remove GO from application command text.
Prevent it next time: Keep migration-script processing separate from parameterized application commands.

Must declare the scalar variable "@label".

Why it happens: A variable was referenced in another batch.
How to fix it: Declare and use the variable in the same batch.
Prevent it next time: Mark scope boundaries while reviewing scripts.

Invalid object name.

Why it happens: The session is in a different database or the object name is wrong.
How to fix it: Inspect DB_NAME(), verify the database, and use a schema-qualified name.
Prevent it next time: Record intended context before querying.

Check your understanding

1. A script declares @n, contains GO, then selects @n. Explain the result.

Show worked solution

The tool sends two batches. The declaration belongs to the first, so the second fails variable resolution. Keep the declaration and SELECT together; adding a semicolon does not extend scope.

2. A server reports major version 16 and a database compatibility level of 150. What should you record?