Published / updated29 Aug 2026

Relational Foundations and SSMS

Before you start

You need: nothing. No database experience is assumed.

You need installed: SQL Server Developer edition and SQL Server Management Studio (SSMS) — a separate download from SQL Server itself. Pick Mixed Mode authentication during install and note the instance name.

Time: about 45 minutes, plus the practice.

New to all of this? Start here explains what an application is made of and introduces the school system every example uses. Any word you do not recognise is in the glossary.

Learning objective

Explain how a relational database stores related information, and connect to SQL Server and run queries with confidence.

Topics

What a relational database is, and what problem it solves
Server, database, schema, table, row, column
SQL Server editions and installation
SQL Server Management Studio
Creating a database
System databases
Writing and running your first queries

Terminology

Term	Meaning
Schema	A named container for tables inside a database, e.g. `dbo`
Table	A set of rows with the same columns
Row	One record — one student, one payment
Column	One attribute, with a fixed data type
Primary key	The column that uniquely identifies a row
Foreign key	A column pointing at another table's primary key

Why relational

Consider recording exam results for NexCoding Academy in a spreadsheet:

Ravi Kumar   NCA-2024-0012  10th  A  Maths    87  Dr. Mehta  9951510727
Ravi Kumar   NCA-2024-0012  10th  A  Science  72  Mrs. Rao   9951510727
Priya Sharma NCA-2024-0018  10th  A  Maths    91  Dr. Mehta  9848012345

Ravi's name, roll number, class, and parent phone repeat on every row. That causes four specific problems:

Problem	Example
Update anomaly	Ravi's phone changes — you must find and edit every row, and missing one leaves the data contradicting itself
Insert anomaly	A new student with no results yet cannot be recorded at all
Delete anomaly	Deleting the last result for a student erases the student
Inconsistency	"Dr. Mehta", "Dr Mehta", and "dr. mehta" become three teachers

A relational database stores each fact once, in the table it belongs to, and links tables by key:

Student            Subject           ExamResult
---------          ---------         ------------
Id                 Id                Id
Name               Name              StudentId  →  Student.Id
RollNumber         TeacherId         ExamId     →  Exam.Id
ClassName                            MarksObtained
ParentPhone                          IsAbsent

Ravi's phone number now exists in exactly one place. Changing it changes it everywhere, because everywhere else just points at his row.

That is the whole idea. Everything else in this track — keys, joins, normalisation — is machinery for making it work.

Server, database, schema, table

SQL Server instance          the running service
└── Database                 NexCodingSchool
    └── Schema               dbo
        └── Table            Student
            └── Row          Ravi Kumar, NCA-2024-0012
                └── Column   Name, RollNumber, ClassName

One SQL Server instance hosts many databases. Each database has schemas — dbo (database owner) is the default, and small applications use only that one. Larger systems split by area:

SELECT * FROM dbo.Student;
SELECT * FROM academic.ExamResult;
SELECT * FROM finance.FeePayment;

Always qualify with the schema. SELECT * FROM Student works but forces SQL Server to resolve the name at run time, and it breaks the moment a second schema has a table of the same name.

The full four-part name is Server.Database.Schema.Object. In practice you write Schema.Object and let the connection supply the rest.

Editions and installation

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

Install Developer for learning — it is free and has every feature, so nothing you learn is unavailable later.

During installation, two choices matter:

Authentication mode. Choose Mixed Mode so both Windows and SQL logins work. Windows-only is more secure but makes some tooling awkward, and applications often need a SQL login.

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

SQL Server Management Studio

SSMS is a separate download from SQL Server itself. Connect with:

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

The panes you will use:

Object Explorer — the tree of databases, tables, views, procedures
Query window — where you write SQL (Ctrl+N for a new one)
Results grid — output, with a Messages tab beside it

Keys worth learning immediately:

Key	Does
F5	Execute — or just the selected text
Ctrl+R	Toggle the results pane
Ctrl+Shift+M	Show the estimated execution plan setting
Ctrl+L	Display estimated execution plan
Ctrl+K, Ctrl+C	Comment selection
Alt+drag	Block select — for editing many lines at once

F5 executes only the selection when text is highlighted. This 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 — select carefully.

Creating a database

CREATE DATABASE NexCodingSchool;
GO

USE NexCodingSchool;
GO

GO is not SQL. It is an SSMS batch separator telling the tool to send everything above it as one batch. Some statements — CREATE PROCEDURE, CREATE VIEW — must be the first statement in a batch, which is why GO appears before them. Your application code never sends GO; it would be a syntax error.

USE switches the active database for subsequent statements. Forgetting it is how a table ends up in master.

System databases

Object Explorer shows four you did not create:

Database	Holds
`master`	Server configuration, logins, the list of databases
`model`	The template every new database is copied from
`msdb`	SQL Agent jobs, backup history
`tempdb`	Temporary tables and internal working storage, recreated on restart

Never create your tables in master. It happens constantly — you open a query window, forget USE, and the default database is master. The table works, and then it is missing from every backup of your real database and invisible to your application.

Check where you are before running anything:

SELECT DB_NAME() AS CurrentDatabase;

The database dropdown in the SSMS toolbar shows the same thing. Look at it.

First queries

USE NexCodingSchool;
GO

CREATE TABLE dbo.Student
(
    Id          INT IDENTITY(1,1) NOT NULL,
    Name        NVARCHAR(100)     NOT NULL,
    RollNumber  NVARCHAR(20)      NOT NULL,
    ClassName   NVARCHAR(10)      NOT NULL,
    Section     NVARCHAR(1)       NOT NULL,
    ParentPhone NVARCHAR(15)      NULL
);
GO

INSERT INTO dbo.Student (Name, RollNumber, ClassName, Section, ParentPhone)
VALUES ('Ravi Kumar',   'NCA-2024-0012', '10th', 'A', '9951510727'),
       ('Priya Sharma', 'NCA-2024-0018', '10th', 'A', '9848012345'),
       ('Arjun Reddy',  'NCA-2024-0031', '10th', 'B', '9701234567');
GO

SELECT Id, Name, RollNumber, ClassName, Section
FROM   dbo.Student
ORDER BY ClassName, Section, Name;

IDENTITY(1,1) makes SQL Server generate Id values automatically, starting at 1 and increasing by 1. You never supply that column on insert.

Comments and formatting

-- A single-line comment

/* A block comment,
   spanning lines */

SELECT   s.Name,
         s.RollNumber
FROM     dbo.Student AS s
WHERE    s.ClassName = '10th'
ORDER BY s.Name;

Formatting is not decoration. A query you can read is a query you can debug, and legacy SQL written as one long line is where mistakes hide. Keep clauses on their own lines and align them.

Keywords are conventionally uppercase, identifiers as declared. SQL Server is case-insensitive for keywords; whether it is case-sensitive for data depends on the collation, which the next articles cover.

Errors you will hit

Message	Cause	Fix
`A network-related or instance-specific error` (error 2 or 53)	Wrong instance name, service stopped, or firewall	Try `.` or `.\SQLEXPRESS`; check SQL Server in `services.msc`
`Login failed for user` (18456)	Wrong credentials, or the login does not exist	Use Windows Authentication first
`Cannot open database "X" requested by the login` (4060)	Wrong database name, or no permission	Check the dropdown in SSMS
`Invalid object name 'Student'`	Connected to the wrong database	Check the database dropdown in the toolbar
`Incorrect syntax near ...`	A typo, usually one line above the reported one	Read the line before

SSMS runs whatever is selected. Highlight one statement and press F5 and only that runs — which is how you test one query in a long script without executing the rest.

Common mistakes

Creating tables in master because USE was forgotten
Not qualifying names with the schema
Assuming GO is SQL and sending it from application code
Using the wrong instance name and reporting "SQL Server is down"
Running an unselected script when only one statement was meant
Installing Express and hitting the 10 GB limit later
Treating a spreadsheet layout as a table design, and repeating data
Reading only the Results tab and missing warnings in Messages

Practice

Install SQL Server Developer and SSMS. Connect, and confirm the instance name you had to use.

Create the NexCodingSchool database and the Student table above. Insert five students using the standard names — Ravi Kumar, Priya Sharma, Arjun Reddy, Sneha Patel, Kiran Rao — across classes 9th and 10th, sections A and B. Then:

Run SELECT DB_NAME() and confirm you are in the right database.
Select just the SELECT statement from your script and press F5. Confirm only that statement ran.
Deliberately create a table without USE, in master. Find it in Object Explorer, then drop it. This is the mistake worth making once, on purpose.
Write out, in your own words, what would go wrong if you also stored each student's exam marks as extra columns on the Student table. Name the four anomalies.

Exercise 4 is the one that matters — it is the reasoning behind everything in the next nine articles.

You can now

Connect to SQL Server from SSMS
Explain why data is split across related tables
Name the parts of a table: rows, columns, keys
Run a query and read the results grid
Diagnose a failed connection from the error number

Review questions

What are the four anomalies that repeating data in one table causes?
What is the difference between a database and a schema?
What does GO do, and why can it not appear in application code?
Why should you check DB_NAME() before running a CREATE TABLE?

Next: Tables and data types

Before you start​

Learning objective​

Topics​

Terminology​

Why relational​

Server, database, schema, table​

Editions and installation​

SQL Server Management Studio​

Creating a database​

System databases​

First queries​

Comments and formatting​

Errors you will hit​

Common mistakes​

Practice​

You can now​

Review questions​