SQL Server 2000 creates four system databases during the installation process, which is based on the basis of running SQL Server 2000. It is built in the four system databases to run and use the rules of SQL Server. These four databases are: Master, Model, Tempdb, Masdb.
Master database
The Master data records all server system information of SQL Server 2000, all registered accounts and passwords, and all system settings information. The Master database also logs all user-defined database storage locations and initialization information. Due to the key to the Master database, once it is damaged, it is possible to cause the user's SQL Server application to be paralyzed, so back up the Master database often.
Tempdb database
The Tempdb database records all temporary tables, temporary data, and temporary creation stored procedures. The Tempdb database is a global resource that does not have a dedicated permission limit that allows all users who can connect the SQL Server server. All data information stored in the TEMPDB database is temporary. Whenever the connection is disconnected, all temporary tables and temporary stored procedures will be automatically discarded. So each time SQL Server starts, TEMPDB data is always empty. When the amount of data of the temporary store has increased dramatically, the size of the TEMPDB database can grow automatically.
MODEL database
The Model database is a template for establishing a new database, which contains system tables in each database that will be copied. When performing the statement Create Databases for creating a database, the server always creates the front part of the new database, and the back section of the new database is initialized into a blank data page to store data.
MSDB database
The MSDB database is mainly used by SQL Server Agent for activities such as replication, job scheduling, and management alarms. The database is often used to troubleshoot by scheduling tasks.
Features and uses of indexes
Index is a database object that SQL Server is built on column. It provides logical sorts of databases in the table to increase access speed of data.
For example, to find all the names from the employee from Beijing in the Employee table with a 10,000-row record. If the index is not established for the address of the employee on this table, DBMS is performing a visual table in the execution operation, and only the name fields from the rows from Beijing. This process of traversing each row record and completes the query is called a table scan.
SQL Server performs a table scan and read all data pages in turn. For a small table with only 10,000 lines of data, it is not troublesome to perform a table scan. But if the data of the Employee table is 1000 times now, how is the total record of 10000000? Moreover, even if there is a record, and recorded on the first page, SQL Server has to find all the names of the employee of Beijing in all data pages.
If an index is added to the address bar, since the index includes a pointer pointing to the data, the same query is used in the same query. DBMS reads only the index table with only one column data along the order of the index (if only one index is established) until Beijing. Then, the DBMS is transferred to the data table along the pointing point of the index pointer to find the corresponding data. Since the index is always sorted in a certain order, the speed of scanning the index is greatly scanned with the table.
