Databases. Databases contain the information that applications use. SQL Server databases contain collections of tables, views, indexes, and stored procedures. Each application is typically designed so that it connects to its own database. A single SQL Server system can support multiple databases up to as many as 32,767 databases per server. SQL Server databases can grow quite large: The maximum size of a database is 1,048,516TB. Every database must consist of at least two files: a data file and a log file. The data file contains the table, row, and column information that's stored in the database. The log file contains all the transaction operations (INSERT, UPDATE, and DELETE) that users or applications execute against the database. As I mentioned earlier, to ensure optimum performance, you should never place the data and log files on the same drive on production systems. Likewise, you should never place data and log files on compressed or encrypted drives.
During database creation, SQL Server uses a copy of the model database as a template for the new database. You can specify a maximum growth amount for the database either in megabytes or as a percentage of its size. However, for most installations, you're better off specifying the auto-grow option, which lets the database grow automatically as necessary. If you're familiar with your application, you'll want to anticipate the database's growth and size appropriately so that SQL Server avoids using auto-grow during production periods, which would cause a temporary performance hit.
Tables. The table is the basic unit of storage for all relational databases. Tables contain a set of related information. For instance, each row in a customer table would contain all the related information for a given customer. Typically, such information includes the customer's ID number, name, address, and contact information. Each separate piece of information (e.g., customer number) is contained in a column, and each column is defined to hold only a given data type. For example, the first column in the customer table might be named CustomerID and be defined as int, meaning that it can store only integers. The second column, CustomerName, might be defined as varchar(40), meaning that it can store as many as 40 characters of text data.
Indexes. The primary reason for using indexes is to improve query performance. Indexes are built on selected columns in a table. SQL Server uses two types of indexes: clustered and nonclustered. A clustered index determines the order of data in the table. When a clustered index is created on a table, SQL Server arranges the rows in the base table to match the order defined in the clustered index. Each table can have only one clustered index. Nonclustered indexes don't reorder the data in the base table; they simply provide an alternative optimized access path to the data.
You can delete or add indexes without affecting the basic database design. Although an index's primary purpose is to improve data-retrieval speed, too many indexes can have the opposite effect. When an index is built, SQL Server must maintain that index (in other words, keep it up-to-date). As data is added to the tables in a database, the SQL Server engine must not only add the data to the table but also update the existing indexes with the new data. As you might imagine, the more indexes you've created, the longer this update process can take. Too many indexes can adversely affect your system's performance. One common technique that can improve batch processing is to write one script that deletes the affected indexes before a batch procedure begins, then write another script that recreates the indexes when the procedure is finished. SQL Server 2000 provides an Index Tuning Wizard that analyzes queries and suggests indexes that would improve query performance.
Views. A view is like a virtual table or a stored query. The data that's accessible through a view isn't stored in a discrete database object. Instead, you create a view by using a SQL SELECT statement. When accessing a view, the user sees the results of that SELECT statement. Views are typically used to restrict users to a subset of the rows or columns in one or more tables. You can also use views to join multiple tables, thereby making them appear to be one table.
Stored procedures and triggers. Stored procedures, which you create by using compiled T-SQL code, are the backbone of most database applications. Triggers are a special type of stored procedure that can be attached to a table. Because stored procedures are compiled, they provide better performance than dynamic SQL (i.e., SQL code that a program generates before the SQL code is executed). When dynamic SQL is executed on the server, the database engine first parses the statement to ensure that the syntax is valid. Then the server constructs a data-access plan. Stored procedures let SQL Server perform this work at the time the stored procedure is created instead of at runtime, which gives stored procedures a performance advantage over dynamic SQL at execution time. SQL Server is very smart about how it caches queries and will even cache ad hoc dynamic SQL for a period of time, but there's no guarantee that the dynamic SQL will still be cached the next time it's used.
Like stored procedures, you use T-SQL to create triggers. However, unlike stored procedures, which can be freely executed by any user who has permission to those stored procedures, triggers are executed only by the database itself. You attach triggers to a table by using the CREATE TRIGGER statement, and they're executed only when an INSERT, UPDATE, or DELETE action is performed on the table. DBAs commonly use triggers to help enforce Declarative Referential Integrity (DRI). You can also cascade DELETE operations from the referenced table to associated rows in the referencing table. Trigger code fires only after SQL Server checks constraints, including DRI.
Take a Break
It's time to sit back and absorb what you've learned about SQL Server 2000. By now, you should understand the basics of sizing RAM and determining disk configuration on a SQL Server system. You should also be familiar with SQL Server's fundamental components. Stay tuned for part 2 in this series, where I'll reveal more SQL Server insights to help make your job as a part-time DBA a lot easier.