SQL Server 2000 database file organization

server| Data | database
One, data file type:

Each SQL Server 2000 database has a master data file that can have multiple secondary data files, which can only be used by the database.

Second, the data file name:

Each data file is a stand-alone operating system file. The main data file is usually a file suffix with. mdf (this suffix is not required, but is useful for identifying files). The master data file stores the data in a table or index, which contains the startup information for the database. It also contains system tables that record the location of objects in the database, such as all other files in the database (secondary data files and transaction log files).

Each secondary data file is usually a file suffix of. NDF (which is also not required). Secondary data files are used primarily when a database spans multiple hard drives.

Each data file (primary and secondary) has a logical file name, used in the Transact_sql statement, and a physical file name for use by the Windows operating system. The logical file name must be unique to a particular database and must comply with SQL Server's identifier rules.

Third, data file storage:

SQL Server 2000 records all the databases in two locations: the primary database and the primary file for that database. Many times, the database engine uses the file location information found in the primary database. Of course, there are exceptions, for example, when you perform certain actions that allow the database engine to use the primary file location information to initialize the file location input in the primary database. These operations are being upgraded from SQL Server 7.0 to SQL Server 2000, restoring the primary database, and using the sp_attach_db system stored procedure to add a database to SQL Server 2000.

Iv. spatial allocation of data files:

Before storing information in a table or index in SQL Server 2000, you must allocate some free space within a data file to that object. Each unit of free space assigned to tables and indexes is called the extents (extent). One expansion panel is 64KB, consisting of 8 consecutive pages, each of which is 8k. There are two types of extents: mixed extents and consolidated extents.

Each time you create a new table or index, SQL Server 2000 looks for a mixed extents with free space, and then assigns that free page to the new object. A page contains only one object's data. When an object requires additional space, SQL Server 2000 assigns free space in the mixed extents to the object until it uses 8 pages altogether. After that, SQL Server 2000 assigns a unified extents to that object. If there is no free space on all the data files, and the "Auto growth" option is started, SQL Server 2000 will grow these data files with a looping algorithm.

Reference sqlserver2000 Help file