SQL Sever 2000 System database and Index

During the installation of SQL Server 2000, four system databases were created, which are the basis for running SQL Server 2000, table store, established in these four system databases, runs and uses SQL Server rules. These four databases are: Master, model, tempdb, and masdb.

 

Master Database

Master data records all the server system information of SQL Server 2000, all registered accounts and passwords, and all system settings. The master database also records the storage location and initialization information of all user-defined databases. Because the master database is critical, once it is damaged, it may lead to paralysis of the user's SQL Server application system, so it is necessary to regularly back up the master database.

 

Tempdb Database

The tempdb database records all temporary tables, temporary data, and temporary stored procedures. The tempdb database is a global resource and has no special permission restrictions. It can be used by all users who can connect to the SQL Server server. All data stored in the tempdb database is temporary. When the connection is disconnected, all temporary tables and stored procedures are automatically discarded. Therefore, when SQL Server is started, tempdb data is always empty. When the amount of temporary storage data increases sharply, the size of the tempdb database can automatically increase.

 

Model Database

A model database is a template for creating a new database. It contains the system tables in each database that will be copied. When you execute the create databases statement to create a database, the server always copies the model database to create the first part of the new database. The latter part of the new database is initialized into a blank data page to store data.

 

MSDB Database

MSDB databases are mainly used by SQL Server Agents for copying, job scheduling, and alarm management. Databases are often used to troubleshoot by scheduling tasks.

 

 

Features and usage of Indexes

An index is a database object created by SQL server on a column. It provides logical sorting for the databases in the table, improving the data access speed.

For example, you need to find the names of all employees from Beijing in the employee table with 10000 rows of records. If the table is not created for the employee's address
The DBMS must traverse each row in the table and display only the name fields in the rows from Beijing. This traverses each row of records.
The process of completing the query is called table scan.

SQL Server performs a table scan to read all data pages in sequence. For small tables with only 10000 rows of data, executing a table scan is not counted.
Trouble. But what if the data in the employee table is 1000 times that of the current one and the total number of records reaches 10000000? In addition, even if there is only one record
And recorded on the first page, SQL Server also has to find the names of employees whose addresses are Beijing on all data pages.

If you add an index to the address bar, because the index includes a pointer to the data, you can use the index to perform the same query as the table scan.
Different descriptions. DBMS only reads the index table with only one column of data in the order of index sorting (if only one index is created)
Beijing. Then, the DBMS moves the index pointer to the data table and finds the corresponding data. Because the index is always arranged in a certain order
So the index scanning speed is much faster than the table scanning speed.