Skip to content
Tutorials Center
My learning

Topics

Lesson 3 of 39 ยท Getting Started

Databases, Schemas and Objects in SQL Server

Understand how an instance, databases, schemas, tables and files relate, what the system databases do, and how to create a database and tables in T-SQL.

Before writing queries it helps to know where your data lives. SQL Server organizes objects in layers: an instance contains databases, a database contains schemas, and a schema contains tables, views, procedures and other objects. A full name has up to four parts: server.database.schema.object. Most of the time you write only the last one or two parts.

This lesson explains the layers, the system databases you must not break, how a database is stored in files, and how to create your own database and tables.

flowchart TD
  I["Instance"] --> M["master, model, msdb, tempdb"]
  I --> D["ShopDB (yours)"]
  D --> S1["Schema dbo"]
  D --> S2["Schema sales"]
  S1 --> T1["Table dbo.customers"]
  S2 --> T2["Table sales.orders"]
  S2 --> V["View sales.recent_orders"]

One instance, several databases, schemas inside each database, objects inside schemas.

The system databases

Every instance has four built-in databases:

  • master holds instance-level information: logins, configuration, the list of all databases. If it is damaged, the instance will not start, so back it up.
  • model is the template. Every new database starts as a copy of model, so settings placed there apply to future databases.
  • msdb is used by SQL Server Agent for jobs, schedules, alerts and backup history.
  • tempdb is scratch space for temporary tables, sorting, row versioning and spills. It is recreated empty every time the service starts, so never keep anything important in it.

(A fifth hidden database, mssqlsystemresource, stores the system objects themselves.) A good habit: create your own database for practice and never store user tables in master.

How a database is stored

A database is at least two files:

  • a primary data file (.mdf), and optionally secondary data files (.ndf);
  • a transaction log file (.ldf).

Data files are organized in 8 KB pages, grouped in 64 KB extents of eight pages. Large databases can spread tables over several filegroups, which lets an administrator place hot tables on fast disks. You will touch these details again in the administration chapter.

Creating a database

The simplest form lets SQL Server pick the defaults from model:

SQL
CREATE DATABASE ShopDB;
GO
USE ShopDB;
GO

GO is not T-SQL: it is a command understood by the client tools (SSMS, sqlcmd) that means "send everything so far as one batch". USE ShopDB changes the current database for the connection. You can also be explicit about the files:

SQL
CREATE DATABASE ShopDB
ON PRIMARY (NAME = ShopDB_data, FILENAME = 'C:\Data\ShopDB.mdf', SIZE = 64MB, FILEGROWTH = 64MB)
LOG ON      (NAME = ShopDB_log,  FILENAME = 'C:\Data\ShopDB.ldf', SIZE = 32MB, FILEGROWTH = 32MB);

Set a sensible growth size in megabytes rather than the old 10 percent default; many tiny automatic growths hurt performance. To remove a database, DROP DATABASE ShopDB; deletes the files too, so be certain of the name.

Schemas

A schema is a named container for objects. It groups related tables (sales.orders, hr.employees), gives permissions a natural scope, and avoids name clashes. Every user has a default schema; for most that is dbo ("database owner"). Always qualify names in production code (dbo.customers), because it avoids ambiguity and lets SQL Server reuse query plans more reliably.

SQL
CREATE SCHEMA sales;
GO
CREATE TABLE sales.orders (
    order_id    INT IDENTITY(1,1) PRIMARY KEY,
    customer_id INT           NOT NULL,
    ordered_at  DATETIME2(0)  NOT NULL DEFAULT SYSDATETIME(),
    total       DECIMAL(10,2) NOT NULL CHECK (total >= 0)
);

This statement uses several T-SQL ideas at once: IDENTITY(1,1) generates an auto-incrementing number (seed 1, step 1), DATETIME2(0) is a date and time without fractions, SYSDATETIME() returns the current time, and CHECK enforces a rule. The data types lesson explains these types in detail.

SQLite, which powers the live editors here, has no schemas and uses INTEGER PRIMARY KEY AUTOINCREMENT instead of IDENTITY. The stand-in below builds a similar table and shows the table's structure.

SQL Stand-in: create and inspect a table

Press Run (or Ctrl + Enter) to see the result here.

Other objects you will meet

Object What it is
Table Rows and columns; the actual data
View A saved SELECT that behaves like a table
Index A structure that speeds up lookups
Stored procedure A named, reusable batch of T-SQL
Function Returns a value or a table; can be used in queries
Trigger Code that fires automatically on data changes
Sequence A number generator independent of any table
Synonym An alias for another object

Each has its own lesson later. For now, remember that they all live in a schema, and that the catalog views describe them all. The stand-in below lists a table's columns, much like sys.columns would.

SQL Stand-in: inspect columns (like sys.columns) ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Naming rules and conventions

Identifiers may contain letters, digits, underscore, @, # and $, and must not start with a digit. If a name has spaces or is a reserved word, wrap it in square brackets, [Order Details], or double quotes. Prefixes matter: #name is a local temporary table, ##name a global temporary table, and @name a variable. Choose one convention (for example snake_case or PascalCase) and keep it across the database.

Never create objects in master and never run a script without checking the current database first. A DROP TABLE in the wrong database is the classic way to ruin a day. Make USE the first line of scripts, or verify with SELECT DB_NAME();.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which system database is recreated empty every time the instance starts?

    tempdb holds temporary objects and work space and is rebuilt at startup, so nothing permanent belongs there.
  2. 2. What does the model database do?

    New databases start as a copy of model, so settings there apply to databases created later.
  3. 3. What is GO?

    GO is a client-tool command that sends the preceding statements to the server as one batch; the server itself never sees it.
  4. 4. How do you refer to a table named Order Details that contains a space?

    Square brackets (or double quotes) delimit identifiers with spaces or reserved words in T-SQL.

Summary

  • Instance, database, schema, object: the layers of a four-part name.
  • master, model, msdb and tempdb are system databases with specific jobs.
  • A database consists of data files (.mdf/.ndf) and a transaction log (.ldf).
  • Schemas group objects and permissions; dbo is the default.
  • Use GO between batches in client tools, and square brackets for unusual identifiers.

Next: T-SQL Basics: Batches, Variables and Control Flow