Question: Do all objects within SQL Server 2022 begin with a parent database?
No, not all objects within SQL Server 2022 begin with a "parent database". While most database objects like tables, views, and stored procedures reside within a specific database, there are some exceptions:
Server-level objects:
Logins: These represent user accounts used to connect to the SQL Server instance. They are independent of any particular database and exist at the server level.
Server roles: These define sets of permissions granted to users or groups, allowing access to specific server-wide functionalities. They also exist at the server level.
Server-level objects like endpoints, aliases, and linked servers: These objects facilitate communication and interaction between the SQL Server instance and other resources like databases or services. They are not tied to specific databases.
System objects:
System tables: These tables hold information about the SQL Server instance itself, its configuration, and performance metrics. They are not part of any user-created database and belong to the internal `sys` database.
System views and procedures: Similar to system tables, these objects provide insights into server functionality and are not associated with specific user databases.
Temporary objects:
Temporary tables: These tables exist only for the duration of a session or transaction and are not physically stored in any database.
Temporary stored procedures: Similar to temporary tables, these procedures exist only within a session and don't belong to any specific database.
**Therefore, while a vast majority of objects in SQL Server reside within a particular database, some crucial server-level, system, and temporary objects exist independently, not directly tied to a specific "parent database".**
All objects within SQL Server 7 begin with a parent database.
The database acts as a collector for child objects contained within it. As an example, let's follow the course project. We have a database named Timesheets. The database contains four tables: Employees, Clients, Tasks, and Timesheets. These tables might contain indexes (discussed later in this mocule), designed to facilitate faster queries. The parent-child relationship, known as a hierarchy, exists between the database itself and its table objects. Also, the parent-child relationship exists between the table objects and the index objects contained within those tables. This relationship looks like this:
Database as a collector of objects
Databases and their objects can be created in one of two ways:
By using a provision in Transact-SQL known as Data Definition Language, or DDL. You execute these statements in the Query Analyzer to create or modify SQL Server objects.
By using the Enterprise Manager or SQL Server wizards.
SQL - Server wizards
Throughout the remainder of this course and the other courses in this series, anytime I mention how to modify a SQL Server object with
Transact-SQL, I'll also refer to the wizard designed to help you perform the same operation.
However, in preparation for the 70-029 exam, it is important that you know the Transact-SQL to deal with SQL Server objects, not the wizards.
These tools enable you to create, delete, or modify most objects within the SQL Server environment without needing to know DDL. However, the wizards in particular can be limiting. You maintain much more control when you issue the DDL statements.
In the next lesson, we will look at the permissions required to create, modify, or delete a database.
