Chapter 6: Databases, Tables, and Indexes

Overview

SQL Server 2005 provides one of the most flexible database management systems around. And this flexibility does not come at a high price either. SQL Server databases can be small, large, very large, or gigantic. You can expand them, shrink them, infuse them with thousands of tables, copy them, move them, detach them, transform them, encrypt their objects, and so on. And you can have 32,767 databases for every instance of SQL Server, and, er, billions of tables.

A SQL Server database consists of numerous components and objects. I have discussed many of them in previous chapters-especially in Chapter 2-in which we studied the database architecture from several levels. This chapter guides you through creating databases and tables through several facilities provided as the data storage, data management, and data processing aspects of SQL Server databases. We will also look at table creation, working with the Table Designer features, and database diagrams.

SQL Server is famous for the way it handles two types of databases, OLTP databases and OLAP databases-Online Transaction Processing and Online Analytical Processing databases, respectively. The former architecture is used for managing data that is constantly changing, through INSERT, UPDATE, and DELETE statements. Data is retrieved (checked out) for editing and reporting via a variety of T-SQL SELECT queries.

The online part of the OLTP paradigm is to cater to multiple users as described in the architecture chapters in Part I. SQL Server transaction and concurrency models prevent users from bumping into each other.

OLTP databases can be very complex, especially if there are a large number of tables to process. However, a poorly designed OLTP database can create a severe bottleneck on the access and updating of OLTP data. Knowledge of database design is essential for SQL Server 2005 administration and development (obtained either through experience alone or by doing a Microsoft DBA certification course along with experience). It is not unusual to find SQL Server databases that contain over 600 tables, when only 15 or so would have been enough had the database been designed by a pro.

While this chapter does not get into database design per se, it will provide you with a foundation of knowledge for creating databases and tables and indexes.