Section 19.2. The Power User s View

19.2. The Power User's View

Power users typically understand these elements of SQL Server architecture. They may use them when conversing with database administrators and system administrators.

19.2.1. Instance

An instance is a copy of SQL Server running on a computer. With SQL Server, you can have multiple instances running simultaneously. With versions prior to 2000, you can have only one instance running per machine.

19.2.2. Databases

A database in SQL Server is a collection of tables, indexes, and other objects that store data in a structured, relational format. An individual instance of SQL Server can support many different databases. Each database can store data that is accessed by the other, or they can be completely independent. SQL Server databases are divided into two generic categories, system databases and user databases. System databases hold information pertaining to the running system as well as configuration information for all databases. The master database is the best example of a system database. User databases store user data, hence the name. A database can have many states depending on its current condition; these include online, offline, restoring, recovering, recovery pending, suspect, and emergency.

SQL Server 2005 is very robust. While not generally practiced, it is possible to have up to 32,767 databases per instance of SQL Server 2005.

19.2.2.1. System databases

System databases are the databases that SQL Server uses to manage and configure the running system. By default, the following system databases are installed:

master

Contains server-specific configuation information as well as configuration information about all databases

model

Contains a sample database on which you can base your databases

tempdb

Provides a temporary area for processing queries and other tasks

msdb

Used by the SQL Server Agent services for handling alerts, notifications and scheduled tasks

distribution

Used by the Replication Services when a server is configured as a publisher or distributor

Never create user objects such as tables, stored procedures, or triggers in the master database. The master database is used only to house system-level information used by the instance.

19.2.2.2. User databases

User databases are the databases specifically for nonsystem information. This is the location where users store all their information.

19.2.2.3. Viewing information about databases

Configuration information about the database can be obtained using system stored procedures (defined later in this section) via Transact-SQL. To view information relating to a particular database, issue the following Transact-SQL command:

sp_helpdb dbname

This dumps the configuration information for a particular database. For our Inventory database, the output would look like this:

name        db_size  owner             dbid   created     status  compatibility_level            Inventory 3.00 MB  Administrator  7     Mar 21 2006  Status=ONLINE 90                                         Updateability=READ_WRITE,                                          UserAccess=MULTI_USER,                                          Recovery=FULL,                                          Version=611,                                          Collation=SQL_Latin1_General_CP1_CI_AS,                                         SQLSortOrder=52,                                          IsAutoCreateStatistics,                                          IsAutoUpdateStatistics name           fileid   filename                                                                Inventory     1    C:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\Inventory.mdf       Inventory_log 2    C:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\Inventory_log.ldf filegroup     size       maxsize         growth       usage PRIMARY       2048 KB    Unlimited       1024 KB      data only NULL          1024 KB    2147483648 KB   10%          log only

This information is, of course, also available using the Management Studio in 2005 or the Enterprise Manager in 2000.

19.2.3. Tables

A table is a database object where data is stored and organized into rows and columns. As in other versions, 2005 can have up to 1,024 columns in any one table. Each column is configured to store a certain type of datafor example, character data, numeric data, or binary data.

19.2.3.1. System tables

In addition to user tables as defined previously, SQL Server stores the configuration of the server in system tables. These tables cannot be updated or queried directly by the user, but they can be queried through a dedicated administrator connect (DAC) or through the catalog views. The schema of these tables typically change from version of version, so take note if you are trying to install an older application that accesses a newer version of SQL Server.

19.2.3.2. Temporary tables

Temporary tables are just like regular user tables except they are created in the tempdb and are only for short-term use. There are two types of temporary tables to choose from based on how long you need the temporary tables to stay around: local and global. Local temporary tables are available only to the user that created them and only during the same connected session to the SQL Server instance. These tables are deleted after the session disconnects. Global temporary tables are available to any connected session or user. These are deleted after all sessions referencing the table are disconnected.

19.2.3.3. Index

Indexes are created on tables or views in order to make lookups on the data faster. Indexes are created from one or more columns and form a key. By using indexes, much less data needs to be traversed in order to find the data that you need. This can greatly speed up the operation of your application.

19.2.3.4. Partitioned tables

A partitioned table is a special type of table that has its data divided across more than one filegroup in the database. (Filegroups are defined in the section "The DBA's View" later in this chapter.) By doing this, larger tables become more manageable, and the data within them can be accessed faster. This happens because the data within the partitioned table can be accessed or manipulated in subsets, which means that only part of the table needs to be read to find the result.

19.2.3.5. Partitioned indexes

Partitioned indexes are indexes spread over more than one filegroup. These, along with partitioned tables, make indexes more manageable and speed up access.

19.2.4. Stored Procedures

Stored procedures are precompiled Transact SQL statements that are stored in the database. Stored procedures provide an excellent way to execute code that would otherwise be executed outside the database. They offer a great performance benefit because you save the overhead of having to transfer the data to some outside program or client before performing operations on it.

19.2.5. Memory Management

By default, the SQL Server database engine always obtains as much memory as it can without causing a memory shortage on the system. It then continues to capture more memory based on factors such as the number of connections or how much work/computations need to be done. The amount of memory SQL Server uses continues to grow until either the host operating system signals that there is no more memory available or it reaches its maximum memory allocation target. Memory can be freed and reacquired as often as needed.