system databases and indexes for SQL Sever 2000

Data | database | Index


SQL Sever 2000 system database and index





 





Learning Person: hsly





Learning Books: Getting Started with SQL Server 2000 and improving writing date: 2004-7-12





Chapters: 4.1 P44 9.6.1p126





 





SQL Server 2000 during the installation process, 4 system databases were created, the 4 system databases are the basis for running SQL Server 2000, and the tables built in these 4 system databases Shanyi the rules for running and using SQL Server. These 4 databases are: Master, model, tempdb, masdb.





 





master Database





master data records all server system information for SQL Server 2000, all registered accounts and passwords, and all system setup information. The master database also records the storage location and initialization information for all user-defined databases. Because of the criticality of the master database, once it is corrupted, it can cause the user's SQL Server application to become paralyzed, so it is necessary to back up the master database frequently.





 





tempdb Database




The
tempdb database records all temporary tables, temporary data, and temporarily created stored procedures. The tempdb database is a global resource that does not have a specific permission limit that allows all users who can connect to the SQL Server server. All data information stored in the tempdb database is temporary. All temporary tables and temporary stored procedures are automatically discarded whenever the connection is disconnected. So every time SQL Server starts, the tempdb data is always empty. The size of the tempdb database can grow automatically when the amount of data temporarily stored increases dramatically.





 





model Database




The
model database is a template for creating a new database that contains the system tables in each database that will be replicated. When you execute the statement create databases that creates the database, the server always creates the previous part of the new database by copying the model's database, and the later part of the new database is initialized to a blank data page to hold the data.





 





msdb Database




The
msdb database is primarily used by the SQL Server Agent for replication, job scheduling, and management alerting activities. Databases are often used to troubleshoot problems by scheduling tasks.





 





 




Features and uses of
index




The
index is a database object that SQL Server establishes on a column. It provides a logical ordering of the databases in the table, and can improve the speed of data access.





For example, to find the names of all employees in the employee table with 10000 rows of records from Beijing. If you do not index an employee's address on this table, the DBMS must traverse each row in the table and display only the name fields from the rows in Beijing when the operation is performed. The process of traversing each row of records and completing a query is called a table scan.





SQL Server performs a table scan, which reads all the data pages sequentially. Performing a table scan is not a hassle for a small table with only 10000 rows of data. But what if the employee table's data is 1000 times times what it is now, and the total record is 10000000? Also, even if there is only one record and the first page is recorded, SQL Server has to find the names of all the employees whose addresses are in Beijing on all the data pages.





If you add an index to the address bar, because the index includes a pointer to the data, using the index to complete the same query differs from the table scan. The DBMS reads only the index table with only one column of data along the order of the index (if only one index is established) until it finds Beijing. The DBMS then moves to the datasheet along the pointer to the index pointer to find the appropriate data. Because indexes are always sorted in a certain order, the speed at which the indexes are scanned is much faster than the table.