Published / updated29 Aug 2026

SQL Server and SSMS

Before you start

You need: a database to explore — the schema from Track 06.

Time: about 50 minutes, at the keyboard.

Learning objective

Connect to a SQL Server instance, explore an unfamiliar database, run queries safely, and read what the server reports back.

Topics

• Editions and installation
• Connecting
• Object Explorer
• The query window
• Reading results and messages
• Execution plans
• Scripting objects
• Backup and restore
• Diagnosing a connection failure

Editions and installation

Edition	Use	Limit
Developer	Local development	Full features, not licensed for production
Express	Small applications, free	10 GB per database
Standard	Most production	Feature-limited
Enterprise	Large production	Full
LocalDB	Lightweight, on demand	Single user

Install Developer for learning. It is free and has every feature, so nothing you learn turns out to be unavailable.

SSMS is a separate download from the database engine. Azure Data Studio is the lighter cross-platform alternative, and both connect to the same server.

Two installation choices matter:

Authentication mode. Choose Mixed Mode so both Windows and SQL logins work. Windows-only is more secure and makes some tooling awkward — and an application often needs a SQL login.

Instance name. The default instance is reached as . or localhost. A named instance needs .\SQLEXPRESS. Getting this wrong is the most common "cannot connect" cause.

Connecting

Field	Value
Server name	., localhost, .\SQLEXPRESS, server,1433
Authentication	Windows, or SQL Server
Trust certificate	Tick for a local server

. and localhost mean the default instance on this machine. server,1433 specifies a port — note the comma, not a colon.

SELECT @@SERVERNAME AS ServerName,
       @@VERSION    AS Version,
       DB_NAME()    AS CurrentDatabase,
       SUSER_SNAME() AS LoginName;

Run that first on any unfamiliar server. It confirms which instance you reached, which database you are in, and who you are connected as — three facts that explain most subsequent confusion.

Object Explorer

localhost (SQL Server 16.0)
├── Databases
│   ├── System Databases
│   │   ├── master        server configuration, logins
│   │   ├── model         template for new databases
│   │   ├── msdb          Agent jobs, backup history
│   │   └── tempdb        temporary storage, recreated on restart
│   └── NexCodingSchool
│       ├── Tables
│       ├── Views
│       ├── Programmability
│       │   ├── Stored Procedures
│       │   └── Functions
│       └── Security
├── Security
│   └── Logins
└── SQL Server Agent

Never create objects in master. It happens constantly — open a query window, forget USE, and the table lands in master where it is missing from every backup of your real database and invisible to the application.

SELECT DB_NAME();

The database dropdown in the toolbar shows the same thing. Look at it before running anything.

Useful right-click actions on a table: Select Top 1000 Rows, Edit Top 200 Rows, Design, Script Table as, View Dependencies.

View Dependencies answers "what breaks if I change this column" — it lists every view, procedure and function referencing the table. Run it before any schema change.

The query window

Ctrl+N opens one.

Action	Shortcut
Execute	F5
Execute selection	F5 with text highlighted
Parse only	Ctrl+F5
Display estimated plan	Ctrl+L
Include actual plan	Ctrl+M
Toggle results pane	Ctrl+R
Results to grid / text / file	Ctrl+D / Ctrl+T / Ctrl+Shift+F
Comment / uncomment	Ctrl+K, Ctrl+C / Ctrl+K, Ctrl+U
Block select	Alt+Drag

F5 runs only the highlighted text when there is a selection. That is the most useful habit in SSMS — highlight one statement in a long script and run just that.

It is also how people accidentally run a DELETE without its WHERE clause. Highlight carefully.

USE NexCodingSchool;
GO

GO is not SQL. It is an SSMS batch separator telling the tool to send everything above it as one batch. CREATE PROCEDURE and CREATE VIEW must be first in a batch, which is why GO precedes them.

Your application never sends GO — it would be a syntax error.

Reading results and messages

The Results tab shows rows. The Messages tab shows row counts, PRINT output, warnings and errors.

Read Messages, not just Results. A query returning rows can still have raised a warning, and a truncation or conversion warning there explains a wrong number that Results alone does not.

SET STATISTICS TIME ON;
SET STATISTICS IO ON;

Table 'Student'. Scan count 1, logical reads 2847, physical reads 0
SQL Server Execution Times: CPU time = 31 ms, elapsed time = 124 ms

logical reads is the number to compare when tuning. Elapsed time varies with server load and caching; logical reads is stable and directly reflects how much data the query touched.

Dropping logical reads from 2,847 to 8 after adding an index is proof the index worked, in a way that "it feels faster" is not.

The habit that prevents disasters

-- 1. Write it as a SELECT and check the row count
SELECT * FROM dbo.Student
WHERE  SchoolId = 1 AND ClassName = '12th';

-- 2. Same WHERE, now as the UPDATE
UPDATE dbo.Student
SET    Status = 3
WHERE  SchoolId = 1 AND ClassName = '12th';

An UPDATE or DELETE with no WHERE affects every row. There is no confirmation prompt.

On production data, wrap it:

BEGIN TRANSACTION;

UPDATE dbo.Student
SET    Status = 3
WHERE  SchoolId = 1 AND ClassName = '12th';

SELECT @@ROWCOUNT AS RowsAffected;   -- expected 48?

-- ROLLBACK TRANSACTION;
-- COMMIT TRANSACTION;

Run it, read the count, and only then run COMMIT or ROLLBACK. If the number is not what you expected, roll back.

Never leave a transaction open. An uncommitted UPDATE holds locks, and every other user blocks behind it until you commit, roll back or your session is killed. Leaving one open and going to lunch is a genuine incident, and it looks to everyone else like the application has hung.

SET IMPLICIT_TRANSACTIONS OFF and checking @@TRANCOUNT before closing a window are worth the habit.

Execution plans

Ctrl+L shows the estimated plan without running. Ctrl+M includes the actual plan with the results.

Read plans right to left, following the arrows.

Operator	Meaning
Index Seek	Good — jumps to matching rows
Index Scan	Reads the whole index
Clustered Index Scan	Reads the whole table
Table Scan	Whole heap, no clustered index
Key Lookup	Index found the row, table read for more columns
Nested Loops	Fine for small inputs
Hash Match	Normal for large joins
Sort	Often removable with the right index

Three things to check:

Thick arrows — many rows moving between operators.

A large gap between estimated and actual rows — stale statistics. UPDATE STATISTICS dbo.Student;

Key Lookup with a high row count — add the looked-up columns to the index as INCLUDE.

A scan is not automatically bad. Reading 90% of a small table is faster by scan than by seek plus lookups. Judge by row counts, not by operator name.

SSMS suggests missing indexes in green above the plan. Treat those as candidates, not instructions — the optimiser proposes one index per query with no awareness of the others, and applying every suggestion produces dozens of overlapping indexes that cripple writes.

Scripting objects

Right-click any object → Script as → CREATE To → New Query Window.

That gives the exact definition of a table, view or procedure — including every constraint, index and default. It is the fastest way to understand an unfamiliar schema, and far more reliable than reading the Design view.

Right-click a database → Tasks → Generate Scripts for the whole schema, with or without data. Advanced → Types of data to script → Schema and data produces a complete, runnable copy.

Object Explorer Details (F7) allows multi-select scripting — script twelve tables at once rather than one at a time.

Do not use the table Designer on a production table. SSMS may script the change as create-new, copy, drop-old — which on a large table takes the table offline for the duration and can time out halfway. Write the ALTER TABLE yourself.

sp_help 'dbo.Student';
sp_helptext 'dbo.usp_GetStudentsByClass';

SELECT * FROM sys.tables;
SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('dbo.Student');
SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID('dbo.Student');

-- Find every procedure mentioning a column
SELECT OBJECT_NAME(object_id)
FROM   sys.sql_modules
WHERE  definition LIKE '%RollNumber%';

That last query is how you find every dependency on a column before renaming it — including ones View Dependencies misses because they use dynamic SQL.

Backup and restore

BACKUP DATABASE NexCodingSchool
TO DISK = 'C:\Backups\NexCodingSchool.bak'
WITH FORMAT, INIT, COMPRESSION,
     NAME = 'NexCodingSchool full backup';

RESTORE DATABASE NexCodingSchool_Copy
FROM DISK = 'C:\Backups\NexCodingSchool.bak'
WITH MOVE 'NexCodingSchool'     TO 'C:\Data\NexCodingSchool_Copy.mdf',
     MOVE 'NexCodingSchool_log' TO 'C:\Data\NexCodingSchool_Copy_ldf.ldf',
     REPLACE;

MOVE is required when restoring alongside the original, since the file paths would otherwise collide.

Take a backup before any schema change on data you care about. It takes seconds and it is the difference between an incident and an inconvenience.

A backup you have never restored is not a backup. Restoring to a copy database is the only way to know it works — and it is also how you get a realistic development database.

INIT overwrites the backup file. Omitting it appends, producing a file with several backup sets where restoring the wrong one is easy.

Diagnosing a connection failure

Error	Meaning
2 / 53 — server not found	Wrong instance name, service stopped, SQL Browser off, or firewall
18456 — login failed	Wrong credentials, or the account has no login
4060 — cannot open database	Wrong database name, or no permission to it
-2 — timeout	Slow query, blocking, or unreachable
Named Pipes / TCP provider	Protocol disabled in SQL Server Configuration Manager

Work in this order:

1. Is the service running? SQL Server Configuration Manager, or services.msc.

2. Is the instance name right? A named instance needs .\SQLEXPRESS. This is the most common cause by a wide margin.

3. Is TCP/IP enabled? Configuration Manager → SQL Server Network Configuration. It is disabled by default on Express, so a local connection works and a remote one does not.

4. Is SQL Browser running? Required to resolve named instances remotely.

5. Firewall. Port 1433 for the default instance; named instances use a dynamic port.

6. Does the login exist and have access?

SELECT SUSER_SNAME(), DB_NAME();

Run that from the application, not from SSMS. With Integrated Security=True the account is the process's, not yours — a site running as IIS APPPOOL\SchoolPortal needs a login for that account. That is why an application works in Visual Studio and fails on the server.

Finding blocking

SELECT session_id, blocking_session_id, wait_type, wait_time, last_wait_type
FROM   sys.dm_exec_requests
WHERE  blocking_session_id <> 0;

SELECT r.session_id, r.status, r.wait_type, t.text
FROM   sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE  r.session_id <> @@SPID;

"The application has hung" is very often one blocking session, and frequently it is a colleague's uncommitted transaction in SSMS. The first query names the blocker in one line.

sp_who2 gives the same information in a more readable grid.

Errors you will hit

Error	Cause	Fix
2 or 53 — server not found	Instance name, stopped service, firewall	Try . or .\SQLEXPRESS
18456 — login failed	Credentials, or the login does not exist	Try Windows Authentication
4060 — cannot open database	Wrong name or no permission	Check the dropdown
-2 — timeout expired	Slow query, or blocked	Check sys.dm_exec_requests
The application hangs and nothing errors	A transaction left open in SSMS	SELECT @@TRANCOUNT; must be 0
Object explorer shows stale objects	Cached	Refresh the node

Never leave a transaction open in a query window. It blocks the whole application, and the symptom looks like the API is broken.

Common mistakes

• Creating objects in master
• Not checking DB_NAME() before running a script
• UPDATE or DELETE with no WHERE
• Not checking @@ROWCOUNT
• Leaving a transaction open, blocking everyone
• Reading Results and ignoring Messages
• Using the table Designer on a production table
• Applying every missing-index suggestion
• Judging a plan by operator name rather than row counts
• Comparing elapsed time rather than logical reads
• No backup before a schema change
• A backup never test-restored
• BACKUP without INIT, appending sets
• Assuming Integrated Security uses your account on a server

Practice

The course exercise is run a SQL script.

1. Install SQL Server Developer and SSMS. Connect and run the @@SERVERNAME / DB_NAME() / SUSER_SNAME() query.
2. Create a table without USE. Find it in master, then drop it.
3. Highlight one statement in a five-statement script and press F5. Confirm only that ran.
4. Run SELECT then UPDATE with the same WHERE. Check @@ROWCOUNT before committing.
5. Open a transaction, update a row, and leave it uncommitted. From a second window, select that row and observe the block. Find the blocker with sys.dm_exec_requests, then commit.
6. Enable STATISTICS IO. Run an unindexed query and record logical reads. Add an index and compare.
7. Capture the actual plan with Ctrl+M. Identify the operator, then add an index and confirm a seek.
8. Create a Key Lookup by selecting a column not in the index. Remove it with INCLUDE.
9. Find a query where estimated and actual rows differ widely. Run UPDATE STATISTICS and re-check.
10. Script a table as CREATE and read every constraint and index in the definition.
11. Run View Dependencies on a table before changing a column.
12. Find every procedure mentioning a column with sys.sql_modules.
13. Generate a full schema-and-data script for the database.
14. Back up the database, restore it as a copy with MOVE, and query the copy.
15. Break the connection four ways — wrong instance, stopped service, wrong credentials, wrong database — and record each error number.
16. Disable TCP/IP in Configuration Manager and attempt a remote connection.

Exercises 5 and 6 are the two that most change how you work.

You can now

• Connect to SQL Server and explore an unfamiliar database
• Run only the selected statement with F5
• Read an execution plan and compare logical reads
• Check @@TRANCOUNT before closing a window
• Find a blocking session
• Diagnose a connection failure from its error number

Review questions

1. Why check DB_NAME() before running a script?
2. What does leaving a transaction open in SSMS do to everyone else?
3. Why compare logical reads rather than elapsed time?
4. Why does Integrated Security=True behave differently on a server?

Next: Postman and Swagger