2.1 Introduction

A developer of application code is probably quite content to consider an SQL Server as a collection of databases which, in turn, contain tables, indexes, triggers, stored procedures and views. As a database designer and a person who will be responsible for the performance of those databases it is useful to be able to look a little deeper at the storage structures in SQL Server. A lot of the internals of SQL Server are hidden and undocumented but there is a fair amount that we can still learn about the way the product works. This chapter investigates the storage structures that SQL Server uses and the methods available to view them.

2.2 Devices and Databases

A database resides in one or more Windows NT operating system files that may reside on FAT or NTFS partitions. These operating system files are known in SQL Server terminology as database devices. There can be as many as 256 database devices per SQL Server and the maximum size for each device is 32 Gigabytes.

To create a device the SQL Enterprise Manager can be used or the Transact-SQL DISK INIT statement. The New Database Device dialog box in the SQL Enterprise Manager is shown in Figure 2.1. An example of the DISK INIT Transact-SQL statement is shown below:

   DISK INIT      NAME = 'InsuranceDataDev1',      PHYSNAME = 'd:\sqldevices\InsuranceDataDev1.dat',      VDEVNO = 23,  ...