INFRASTRUCTURE LEARNINGINFRASTRUCTURE LEARNING
インフラ大全インフラ大全IT INFRASTRUCTURE / LEARNING
Infrastructure learning guide
Find a lesson or lab/

Esc to close · Search terms stay on your device

LEARNING日本語

Topic lessonTopic: SQL Server and database foundations

SQL Server structure: instances, databases and schemas

An instance is a target engine; a database is a grouping within it; a schema is a namespace within a database. Tables belong to schemas. CorpDocs.app.Documents and TrainingDocs.app.Documents are separate tables, even though both use app.Documents. Include the connection and database context when identifying the target.

What you will learn

Read the containment of two databases, their schemas and tables. Do not infer a target or permission from a table name alone.

Prerequisite

Know that SQL Server is a DBMS and SSMS is a connecting client. The fictional diagram requires no installation.

An instance contains databases; schemas contain objects

The same table name can refer to different database objects
sql-demo-1

The SQL Server instance contacted by SSMS.

CorpDocs

Five sample documents, keyed 101–105.

app schema

A separate namespace within each database.

Documents table

A table containing data for its own database.

TrainingDocs

Two documents in a separate DB, keyed 101 and 106.

app schema

A separate namespace within each database.

Documents table

A table containing data for its own database.

Matching names identify separate tables in separate databases.

A fictional teaching example. Containment, communication and permission relationships have different meanings.

The boxes represent database containment, not physical folders or a network route. A schema groups database objects and has an owner. Matching schema and user names do not make them the same kind of entity.

Read app.Documents together with the current database

What each name component identifies
NotationScope identifiedExample
InstanceTarget enginesql-demo-1
DatabaseA database on that instanceCorpDocs
schema.objectSchema and object in the current DBapp.Documents
database.schema.objectA DB-qualified target on the instanceCorpDocs.app.Documents
Read-only example: identify the database context
SELECT DB_NAME() AS current_database;

SELECT document_id
FROM app.Documents
ORDER BY document_id;

DB_NAME() without an argument returns the query’s current database. Knowing that name does not establish SELECT permission. The examples include the schema, app.Documents, instead of depending on a partly specified object name.

Five rows versus two: data and access are separate

Defined document rows and read conditions
DatabaseDefined document IDsreader_demo access
CorpDocs101, 102, 103, 104, 105SELECT on required tables stipulated
TrainingDocs101, 106Database access and SELECT unspecified

If access is independently permitted in both databases and the data remain as defined, the same SELECT targets five CorpDocs rows or two TrainingDocs rows. But TrainingDocs access is unspecified for reader_demo. A defined two-row table does not prove a successful two-row query.

The context to identify before reading a query

Checks that prevent target confusion
CheckWhy it matters
InstanceAnother instance may contain the same DB name
Current databaseThe same schema.object may identify another table
Schema-qualified objectIdentifies the namespace inside the DB
Principal and permissionsAuthentication alone does not establish the operation
Query and data stateColumns, predicates and committed state determine results

The SSMS lesson connects these concepts to connection settings and a query tab’s current database.

Check containment and read conditions

Choose an option to see the result and explanation.

If both CorpDocs and TrainingDocs contain app.Documents, what identifies the target?
Read the answer and reason

Answer: Instance, database, schema and object · The endpoint and namespace determine the object.

Is a schema the same entity as a SQL Server login?
Read the answer and reason

Answer: No; a schema is a namespace within a database · A login is an authentication principal, not a schema.

TrainingDocs contains two document rows, but reader_demo access and SELECT permissions are unspecified. Can we assert that a query returns two rows?
Read the answer and reason

Answer: No; defined data and permitted access are separate · Two defined rows do not establish that this principal can select them.

Check, then explain in your own words

  1. Trace instance → database → schema → table.
  2. Explain why the two Documents tables are separate objects.
  3. Separate the DB_NAME() result from object permissions.
  4. Check the target and authorization before predicting a row count.
Check your understanding: Would a schema named reader_demo be the same entity as that user?

No. A schema and a user are different concepts.

A schema is a database namespace; a user is a principal. Matching names do not merge their roles.

Sources and conditions checked

Primary references are collected here with their scope, conditions and corresponding sections. Independent expert review and live-environment validation have not been performed.