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.
Read the containment of two databases, their schemas and tables. Do not infer a target or permission from a table name alone.
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 SQL Server instance contacted by SSMS.
Five sample documents, keyed 101–105.
A separate namespace within each database.
A table containing data for its own database.
Two documents in a separate DB, keyed 101 and 106.
A separate namespace within each database.
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
| Notation | Scope identified | Example |
|---|---|---|
| Instance | Target engine | sql-demo-1 |
| Database | A database on that instance | CorpDocs |
| schema.object | Schema and object in the current DB | app.Documents |
| database.schema.object | A DB-qualified target on the instance | CorpDocs.app.Documents |
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
| Database | Defined document IDs | reader_demo access |
|---|---|---|
| CorpDocs | 101, 102, 103, 104, 105 | SELECT on required tables stipulated |
| TrainingDocs | 101, 106 | Database 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
| Check | Why it matters |
|---|---|
| Instance | Another instance may contain the same DB name |
| Current database | The same schema.object may identify another table |
| Schema-qualified object | Identifies the namespace inside the DB |
| Principal and permissions | Authentication alone does not establish the operation |
| Query and data state | Columns, 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.
Check, then explain in your own words
- Trace instance → database → schema → table.
- Explain why the two Documents tables are separate objects.
- Separate the DB_NAME() result from object permissions.
- 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.
- CREATE SCHEMA (Transact-SQL)Checked:
Scope and conditionsA schema is created in a database and is not synonymous with a database user. Applies to the stated SQL Server example; no live-engine test.
Used in this lessonAn instance contains databases; schemas contain objects Read app.Documents together with the current database Check containment and read conditions
- What is SQL Server?Checked:
Scope and conditionsSQL Server is an RDBMS and uses T-SQL. Applies to the stated SQL Server example; no live-engine test.
Used in this lessonAn instance contains databases; schemas contain objects
- Principals (Database Engine)Checked:
Scope and conditionsServer-level and database-level principals have different scopes. Applies to the stated SQL Server example; no live-engine test.
Used in this lessonAn instance contains databases; schemas contain objects Check containment and read conditions
- SELECT (Transact-SQL)Checked:
Scope and conditionsSELECT returns rows, and WHERE can filter them. Applies to the stated SQL Server example; no live-engine test.
Used in this lessonRead app.Documents together with the current database
- DB_NAME (Transact-SQL)Checked:
Scope and conditionsDB_NAME() returns the current database name when no ID is supplied. Applies to the stated SQL Server example; no live-engine test.
Used in this lessonRead app.Documents together with the current database Check containment and read conditions
Scope and conditionsRow order is not guaranteed without ORDER BY. Applies to the stated SQL Server example; no live-engine test.
Used in this lessonRead app.Documents together with the current database