SQL Server Objects

Objects are composed of two basic categories: user objects developed for use in a business application and system objects that the server itself uses to keep track of information on the server and help manage the server itself.

A first look after installation will reveal several databases that have already been created during the installation process. Two of these databases (Pubs and Northwind) are user databases used to provide implementation examples. The other databases created by the installation process are used by SQL Server to maintain the server. Pubs and Northwind are defined in the following list:

• Pubs The Pubs database is a real-world example of a database serving as a learning tool. The Pubs database is a fictitious publisher's database containing publisher-specific tables and information such as authors and titles. The Pubs database may be dropped, because doing so does not affect the SQL Server environment whatsoever.

• Northwind The Northwind database is the second sample database. It is preferred by many Microsoft Access users who are new to SQL Server because it is the same sample database that was provided for Microsoft Access.

The Northwind and Pubs configurations are poor examples of the configuration options to use in a live database. Don't use them as a measure for the options you need for your own database. Before selecting any option, know the impact of the option on the database and client connections, as well as any applications-required settings. Similarly, you will find that the settings of the system database are rather specialized and do not provide the typical settings used in a production database.

The system databases should not be deleted; doing so would be detrimental to the server. Master of the Server, the Master database, is crucial to the operations of SQL Server and should be considered to be as important as the most mission-critical database stored on the server. If it becomes damaged, the server ceases to function.

The Master Database

The Master database is provided to keep your instance of SQL Server functioning. This database records all the system information for an instance of SQL Server. The Master database contains all information that is global to the server, including logins, error messages, system configuration, and the list of other databases that exist on the server. The Master database helps in tracking the location of primary files in order to view other user databases.

The other system database that is almost equally critical is the msdb database. This database maintains information for the agent. If this database is damaged, most administrative tasks will be hampered.

The Msdb Database

The SQL Server Agent uses the msdb database to store information about the scheduling of alerts, the definition of jobs, and the recording of the server operators to be contacted when a particular event occurs on the server. Maintenance plans, backup jobs, scheduled data operations, and other administrative tasks are maintained within this database.

Another system database, perhaps less critical but still necessary for server operations, is the tempdb database. This database has no permanent storage file, but while the server is running, it is the most used.

The Tempdb Database

The tempdb database contains all temporarily created stored procedures and tables and is generally used as a work area by SQL Server. Tempdb is where tasks that require memory are performed, such as join and sort operations. The temporary tables and objects created in a SQL Server session are dropped after SQL Server is shut down. Tempdb never saves information permanently. By default, the size of the tempdb database automatically increases when needed and is restored to its default size (2.5MB) each time SQL Server is started.

Some of the system databases are used only during specific circumstances on the server. This is the case with the Model database. The Model is accessed only when another database is initialized for the first time.

The Model Database

The Model database stores a complete template for creating new databases. When you create a new database, SQL Server copies the contents of the Model database into the new database you create. It is a good idea to populate the Model database with objects that are present in all of your other user databases. Common stored procedures, user-defined functions, and other resources can automatically be initialized with the creation of a new database.

You may not even recognize the existence of the final system database. The distribution database is seen only if replication is configured. This database is not generated upon installation but is created only when replication is configured.

The Distribution Database

The distribution database is used in the replication process. This database will store data temporarily when moving from a publishing system to the subscribers of the data. The data contained in the database would be transactions, snapshot jobs, synchronization status, and replication history information. Any server configured to participate either as a remote distribution server or as a combined Publisher/Distributor will have the distribution database.

Objects Within a Database

Many objects can be found within a database. Certain objects are present after database creation. These system-level objects are necessary for a database to function. The other objects are defined by the user as the physical structure of a database is assembled. The following list outlines the different database objects:

• Table A table is the first thing you create in the database to facilitate data storage. Tables, like spreadsheets, are composed of rows and columns, usually referred to as fields and records in a database environment.

• View A view is an object generally used in displaying a subset of data from a table. This can be used to ensure security or reduce data redundancy. A view is stored as a SQL query. You can assign permissions to a view to enable an administrator to forgo more granular permission assignments at the column level of a table.

• User-defined function A user-defined function is a group of T-SQL statements that can be reused. Functions are subroutines used to encapsulate frequently performed logic. Any code that must perform the logic incorporated in a function can call the function rather than having to repeat all the function logic.

• Stored procedure Stored procedures are collections of T-SQL statements that execute as a single unit. Stored procedures are stored on the server and can execute faster than queries at the client, without any extra overhead.

• Trigger A trigger is a stored procedure that automatically executes at the invocation of INSERT, DELETE, or UPDATE. triggers can be used to validate the data being entered and to enforce data integrity. They can also be used to alert users of changes to the data.

• Database role A role is a collection of users and permissions. Users can be assigned to various database roles that determine what access they have to which database objects. Members of a role inherit the permissions associated with the role.

• Database diagram Database diagrams are graphically created outlines of how your database is structured. Database diagrams show how tables, and the fields that compose the table, are related.

• Constraint A constraint is an attribute that a column or table can take to restrict what users enter into your database.

• Index An index is a database object that provides fast access to data in the rows of a table, based on key values. Indexes can also enforce uniqueness on the rows in a table. SQL Server supports clustered and nonclustered indexes. The primary key constraint of a table is automatically indexed.

• User A database user is an individual who has a login on the server and has been given access to a database.

Each database object has its own properties that make it an important piece of the entire picture. This book looks deep into the SQL Server object structure and looks independently at each part of SQL Server from multiple perspectives.

A quick perusal of the SQL Enterprise Manager will show a number of other objects. These other objects serve various purposes, but for the most part they are used within the realm of server administration.

Other Objects Found on the Server

When moving data from one server to another, you can use various techniques, but in most instances Distributed Transaction Services (DTS) will be the preference for the import and export of data. If the process is repeated on an ongoing basis, a DTS definition can be saved and the correlated object is stored on the server.

A selection of objects is defined within the msdb database for use by the SQL Server Agent in the maintaining of the server. These objects are defined within the Management branch of the Enterprise Manager under the limb belonging to SQL Server Agent. Each of these objects is defined in the following list:

• Operators An operator is an individual who can be contacted by the agent if need be for attention to a SQL Server condition. Contact can be made by email, network message, and/or pager.

• Alerts Alerts represent the actions to be taken when something of interest occurs on the server. An alert will allow for a response to an error, a physical condition of the server, or an important alteration to data. The alert can then send an email, page an operator, or run a job to address the problem.

• Jobs Jobs can be defined to perform one or more steps. The steps are T-SQL statements, operating-system commands, ActiveX scripting, and replication activities. The jobs can then be scheduled or left to be run on demand.

Another set of objects can be used in troubleshooting problems on the server. Also found in the Management branch, processes, server logs, and lock information can be quickly referenced to provide feedback on what the server is or has been doing. A short description of this set of objects follows:

• Process Information Each process that is using server resources is tracked and logged. The information about these processes can be quickly identified. The types of information available include the ID of the process and 21 other attributes.

• Locks by Process ID If a process ID is known, you can quickly find the resources locked by the process and the attributes associated with the lock.

• Locks by Object If you desire information on what locks are in place for a given object, you can find that information under this limb.

• SQL Server Logs The last seven detailed logs of server activity are maintained within the Enterprise Manager for quick reference.

Also within the Management branch can be found details about the maintenance plans currently defined for the server and the logical backup devices that are being utilized.

If a server has had replication enabled, you may have article objects defined. An article is data that is defined for replication. An article can be an entire table or a portion of the table as defined through the use of vertical or horizontal filters. An article could also be a partition of data, or a database object that is specified for replication. The article can be made up of a stored procedure, a view definition, the execution of a stored procedure, a view, an indexed view, or a user-defined function. Once defined, an article is made available by a publisher object and received by the subscriber objects.

Another set of objects found on the server includes those within the framework of the server "Security" branch. These objects define how a server can be accessed and by whom. The set of security objects includes the following:

• Logins These objects will identify who can access the server.

• Server Roles Server-based administrative groups indicate the level of administrative authority that a login has.

• Linked Servers These items define an available OLE-DB data source.

• Remote Servers This is a configuration definition that will allow a client to connect to one instance of SQL Server to execute a stored procedure on another instance of SQL Server without the need to establish another connection.

All in all, quite a wide variety of objects are held within the definitions maintained by the server. The system databases and tables store the object by definition, but these system objects do not store any production data. The data itself is stored within the user objects.