All courses > Information Technology > Database ::

SQL Server Essentials: Working with Databases, Schemas, and Tables

Capítulo 1

Estimated reading time: 7 minutes

1) Databases and Schemas in SQL Server (and Why Naming Matters)

In SQL Server, objects are organized in a hierarchy. You typically work inside a single SQL Server instance (the “server”), which contains multiple databases. Inside each database, objects are grouped into schemas, and schemas contain objects such as tables, views, and stored procedures.

Database: a logical container for related data

A database is a boundary for data and many settings (for example, collation, file locations, and security). In practice, you often use a database to separate applications or environments (e.g., SalesApp vs. HRApp, or Dev vs. Prod). When you connect in SSMS or Azure Data Studio, you choose a database context, and most object creation happens inside that database.

Schema: a namespace and security boundary inside a database

A schema is a named container inside a database. It helps you organize objects and avoid name collisions. For example, you can have Sales.Orders and Purchasing.Orders in the same database because the schema name makes each table distinct.

Schemas also help with permissions: you can grant permissions at the schema level (e.g., allow a role to read everything in Sales) rather than granting permissions table-by-table.

Naming and organization

Good naming reduces confusion and makes code easier to read and maintain. Practical guidelines:

Continue in our app.

Listen to the audio with the screen off.
Earn a certificate upon completion.
Over 5000 courses for you to explore!

Or continue reading below...

Download the app

Use meaningful database names that reflect the application or domain (e.g., RetailOps, Inventory).
Use schemas to group by domain or responsibility (e.g., Sales, Billing, Reporting).
Avoid spaces and special characters in object names; prefer PascalCase or snake_case consistently.
Use consistent singular/plural conventions for tables (pick one and stick to it).
Prefer explicit schema qualification in queries and DDL (schema.table) to avoid ambiguity and improve plan reuse.

2) Creating and Referencing Schema-Qualified Objects (schema.table)

In SQL Server, a table’s fully qualified name is typically schema.table. You can also fully qualify with database and schema: database.schema.table. When you omit the schema, SQL Server uses your default schema (often dbo), which can lead to objects being created in the wrong place or queries referencing the wrong object.

Create a schema

Run the following in the target database (select the database in SSMS/ADS, or use USE):

USE YourDatabaseName;  -- change to your database context

CREATE SCHEMA Sales AUTHORIZATION dbo;

If you don’t want to specify an owner explicitly, you can omit AUTHORIZATION in many setups, but including it makes intent clear.

Reference objects with schema qualification

Once a schema exists, create and reference objects like this:

SELECT * FROM Sales.Customers;

If you need to reference across databases on the same server:

SELECT * FROM RetailOps.Sales.Customers;

In most day-to-day work, schema.table is the standard form.

Exercise: create a schema for practice

Pick a schema name that represents a domain (e.g., Training, Sales, or App).
Create it in your chosen database.
Confirm it exists using a system view (shown later in this chapter).

3) Practice: Basic Table Creation with Clear Naming Conventions

This section focuses on creating a small set of related tables inside your new schema. Keep the design simple and focus on organization and naming.

Step-by-step: create tables inside your schema

Example: create a small Sales area with Customers, Products, and Orders. Adjust names to match the schema you created (here we use Sales).

Table 1: Customers

CREATE TABLE Sales.Customers (    CustomerId      int IDENTITY(1,1) NOT NULL,    CustomerNumber  varchar(20) NOT NULL,    FirstName       varchar(50) NOT NULL,    LastName        varchar(50) NOT NULL,    EmailAddress    varchar(255) NULL,    CreatedAt       datetime2(0) NOT NULL,    CONSTRAINT PK_Sales_Customers PRIMARY KEY (CustomerId),    CONSTRAINT UQ_Sales_Customers_CustomerNumber UNIQUE (CustomerNumber));

Naming conventions used:

Primary key column named TableNameId (CustomerId) for clarity.
Business identifier (CustomerNumber) is separate from the surrogate key.
Constraints are explicitly named (PK_Sales_Customers, UQ_Sales_Customers_CustomerNumber) to make troubleshooting easier.

Table 2: Products

CREATE TABLE Sales.Products (    ProductId     int IDENTITY(1,1) NOT NULL,    ProductSku    varchar(30) NOT NULL,    ProductName   varchar(200) NOT NULL,    UnitPrice     decimal(10,2) NOT NULL,    IsActive      bit NOT NULL,    CreatedAt     datetime2(0) NOT NULL,    CONSTRAINT PK_Sales_Products PRIMARY KEY (ProductId),    CONSTRAINT UQ_Sales_Products_ProductSku UNIQUE (ProductSku));

Table 3: Orders

CREATE TABLE Sales.Orders (    OrderId      int IDENTITY(1,1) NOT NULL,    CustomerId   int NOT NULL,    OrderDate    date NOT NULL,    OrderStatus  varchar(20) NOT NULL,    CreatedAt    datetime2(0) NOT NULL,    CONSTRAINT PK_Sales_Orders PRIMARY KEY (OrderId),    CONSTRAINT FK_Sales_Orders_Customers FOREIGN KEY (CustomerId)        REFERENCES Sales.Customers(CustomerId));

Notice how the foreign key references a schema-qualified table name (Sales.Customers). This avoids ambiguity and makes scripts portable within the database.

Optional: insert a couple of rows to make verification easier

INSERT INTO Sales.Customers (CustomerNumber, FirstName, LastName, EmailAddress, CreatedAt) VALUES ('CUST-1001', 'Ava', 'Nguyen', 'ava.nguyen@example.com', SYSUTCDATETIME()), ('CUST-1002', 'Noah', 'Patel', NULL, SYSUTCDATETIME());

INSERT INTO Sales.Products (ProductSku, ProductName, UnitPrice, IsActive, CreatedAt) VALUES ('SKU-001', 'Notebook', 4.99, 1, SYSUTCDATETIME()), ('SKU-002', 'Pen', 1.50, 1, SYSUTCDATETIME());

INSERT INTO Sales.Orders (CustomerId, OrderDate, OrderStatus, CreatedAt) VALUES (1, CAST(GETDATE() AS date), 'New', SYSUTCDATETIME());

Exercise: build your own mini set of tables

Create a new schema named Training (or another name you choose).
Inside it, create 2–3 tables that belong together (e.g., Training.Students, Training.Courses, Training.Enrollments).
Use explicit schema qualification in every CREATE TABLE statement.
Use consistent column naming (e.g., StudentId, CourseId) and explicitly named constraints.

4) Verify Results in SSMS or Azure Data Studio (and with System Views)

After creating schemas and tables, verify them in two ways: (1) visually in the Object Explorer, and (2) by querying system views. Both are useful: the UI helps you navigate quickly, and system views help you confirm objects programmatically.

Verify in SSMS / Azure Data Studio: Object Explorer

Expand your database.
Expand
Security
then
Schemas
(SSMS) to find your schema name.
Expand
Tables
to see your schema and tables listed (e.g., Sales.Customers).

If you don’t see your objects, refresh the database node in Object Explorer.

Inspect object properties and script the object

To confirm the exact DDL SQL Server stored:

Right-click the table (e.g., Sales.Customers).
Select
Script Table as
>
CREATE To
>
New Query Editor Window
.
Review the generated script to confirm schema name, columns, and constraints.

This is a practical way to learn how SQL Server represents your design and to capture reproducible scripts for source control.

Verify with INFORMATION_SCHEMA views

INFORMATION_SCHEMA is a standard-ish set of views that can be convenient for basic checks.

SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'   AND TABLE_SCHEMA IN ('Sales', 'Training') ORDER BY TABLE_SCHEMA, TABLE_NAME;

To confirm columns exist and are in the expected schema/table:

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE, IS_NULLABLE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'Sales'   AND TABLE_NAME IN ('Customers','Products','Orders') ORDER BY TABLE_NAME, ORDINAL_POSITION;

Verify with sys.* catalog views (more SQL Server-specific)

sys views are SQL Server’s native catalog and are often more complete than INFORMATION_SCHEMA.

List schemas:

SELECT name AS SchemaName, schema_id FROM sys.schemas ORDER BY name;

List tables with schema:

SELECT s.name AS SchemaName, t.name AS TableName, t.create_date FROM sys.tables AS t JOIN sys.schemas AS s   ON t.schema_id = s.schema_id WHERE s.name IN ('Sales', 'Training') ORDER BY s.name, t.name;

Confirm constraints (example: primary keys and unique constraints):

SELECT s.name AS SchemaName, t.name AS TableName, kc.name AS ConstraintName, kc.type_desc FROM sys.key_constraints AS kc JOIN sys.tables AS t   ON kc.parent_object_id = t.object_id JOIN sys.schemas AS s   ON t.schema_id = s.schema_id WHERE s.name = 'Sales' ORDER BY t.name, kc.name;

Confirm foreign keys:

SELECT s.name AS SchemaName, t.name AS TableName, fk.name AS ForeignKeyName FROM sys.foreign_keys AS fk JOIN sys.tables AS t   ON fk.parent_object_id = t.object_id JOIN sys.schemas AS s   ON t.schema_id = s.schema_id WHERE s.name = 'Sales' ORDER BY t.name, fk.name;

Exercise: confirm your objects exist using system views

Query sys.schemas to confirm your new schema exists.
Query sys.tables joined to sys.schemas to list only tables in your schema.
Query INFORMATION_SCHEMA.COLUMNS for one of your tables and verify column names and data types match what you intended.
Use “Script as CREATE” on one table and compare it to your original CREATE TABLE statement.

Now answer the exercise about the content:

Why is it recommended to use explicit schema qualification (schema.table) when creating and referencing tables in SQL Server?

You are right! Congratulations, now go to the next page

You missed! Try again.

Using schema.table makes it clear which object you mean and reduces mistakes caused by default schemas (often dbo). It also helps avoid ambiguity and can improve plan reuse by keeping object references consistent.