I haven't done anything these days. Years ago, there seems to be busy, after the year but also have a good escape of the problem ....
Only today began to see the school Experiment Guidance database development examples. I've never had time to see ...
In the statement that creates the table, the last one is always a on[primary] what does that mean?
Turn over Books Online. It does not seem to be found under the syntax of CREATE table. Bite the bullet and look down, finally understand some.
Write it out. Houtie Teacher said that publication is the best memory. ：）
There is an optional syntax item in the syntax of CREATE TABLE:
[ON {filegroup | DEFAULT}]
Used to specify the filegroup in which to store the table.
If filegroup is specified, the table is stored in the specified filegroup. This filegroup must exist in the database. If you specify default, or if the on parameter is not specified at all, the table is stored in the default file group.
(DEFAULT is not a keyword.) Default is the identifier for the file group and needs to be bounded, such as on ' Default ', on [default]. ）
On [PRIMARY] is the indication that the table is based on the primary filegroup. Primary represents the primary filegroup. If you do not specify a default filegroup, the primary filegroup is the default filegroup and on [PRIMARY] can also be omitted.
The database says. Microsoft®sql server™2000 uses a set of operating system files to map the database, or it can be said to map the database to a set of operating system files (see how you understand it). All data and objects in the database, such as tables, stored procedures, triggers, and views, are stored in the operating system files (database files): Primary data files, secondary data files, and transaction log files.
1. Main data document
This file contains the startup information for the database and is used to store the data. Is the starting point for the database, pointing to other parts of the file in the database. Each database has a primary data file. The recommended file name extension for the primary data file is. mdf.
2. Secondary data files
These files contain all data files that cannot be placed in the primary data file, except for the primary data file. Some databases may not have secondary data files, while others have multiple secondary data files. (If the primary file can contain all the data in the database, then the database does not need a secondary data file.) Some databases may be sufficient to Da Hing multiple secondary data files, or to extend data to multiple disks using secondary files located on different disk drives. The recommended file name extension for secondary data files is. ndf.
3. Transaction log files
Log information for this database. Each database must have at least one log file. The recommended file name extension for the log file is. ldf.
File groups
For allocation and management purposes, you can divide the database files into different filegroups. A filegroup is a logical management unit of a SQL Server 2000 data file. A named collection of one or more files in SQL Server that constitutes a single unit allocated or used for database administration. You can create filegroups when you first build a database, or when you later add more files to the database. Filegroups cannot be created independently of database files. A filegroup is a management mechanism that groups files in a database.
There are two types of filegroups: Primary filegroup and user-defined file group. (You can say that a database consists of a primary filegroup and any user-defined filegroups.) ）
1. Main file group (PRIMARY)
The user-defined filegroup default filegroup is one of the same types as the filegroup.
It contains the primary data files and any other files that are not explicitly assigned to other filegroups. All pages of the system table are assigned to the primary filegroup.
2. User-defined file group
The user-defined filegroup is in the CREATE database or ALTER database statement, using the FILEGROUP keyword or the filegroup specified in the Properties dialog box in SQL Server Enterprise Manager.
Another: Default file group (group default)
A type of filegroup that contains pages of all tables and indexes for which no filegroup is specified at the time of creation. In each database, only one filegroup at a time can be the default file group. If you do not specify a default filegroup, the default filegroup is the primary file group. You can change the default filegroup by using the ALTER DATABASE statement. By changing the default filegroup, any object created without the specified filegroup is assigned to the data file in the new default filegroup. However, system objects and tables remain within the PRIMARY filegroup rather than the new default filegroup. (Each database has a filegroup running as the default filegroup.) When SQL Server assigns a page to a table or index that is not assigned a filegroup when it was created, it is allocated from the default filegroup. You can have only one filegroup at a time as the default file group. DB_Owner fixed database role members can switch the default filegroup from one filegroup to another. If you do not specify a default filegroup, the primary filegroup is the default file group. ）
NOTE: SQL Server 2000 works effectively when there are no filegroups, so many systems do not need to specify user-defined filegroups. In this case, all files are included in the primary filegroup, and SQL Server 2000 can allocate data anywhere within the database. Filegroups are not the only way to allocate I/O across multiple drives.
Report:
. Why to set up filegroups
There are two purposes for establishing a filegroup.
Q One is better able to allocate and manage storage space and improve database performance by controlling the placement of data and indexes on specific disk drives.
Q Two is because the operating system limits the size of the physical file, so when a data file on a disk exceeds the maximum allowable value for a single file, you can use the Save in the filegroup
Data files stored on other drives continue to expand storage space.
. Rules for using data files and filegroups
The rules for using data files and filegroups in SQL Server 2000 include:
Q Data files or filegroups cannot be used by more than one database.
The Q data file can only be a member of a filegroup.
Q Data files and log files cannot belong to the same file or filegroup.
Q log files cannot belong to any filegroup.
Q Only the files in the filegroup have no space and the file group will automatically grow.
. How the file group works
Filegroups use a proportional fill policy for all files within a group. When you write data to a filegroup, SQL Server 2000 writes a percentage of the data according to the amount of free space in the file
Group, instead of writing all the data to the first file, and then writing to the next file. For example, if file 1 has 100MB of free space, file 2 has 200MB to
With space, allocate a disk area from file 1, allocate two disks from file 2, and so on. In this way, two of files are almost simultaneously filled.
Once all the files in the filegroup are full, SQL Server 2000 automatically extends one file at a time in a circular fashion to accommodate more data (assuming the database is set to grow automatically)
。 For example, a filegroup is made up of 3 files, all of which are set to grow automatically. Only the first file is extended when the space for all the files in the filegroup is exhausted. When the first file is full,
When more data cannot be written to the filegroup, the second file is extended. When the second file is full and you cannot write more data to the filegroup, expand the 3rd file. If the 3rd text
The piece is full, you cannot write more data to the filegroup, expand the first file again, and so on.
Positive time because of this feature of filegroups, database performance can be improved by allowing databases to be created across multiple disks, multiple disk controllers, or RAID (redundant array of inexpensive disks) systems. For example, if
If you have 4 disks in your computer, you can create a database of 3 data files and one log file, with one file on each disk. When accessing data, the four
A read/write head can speed up database operations by simultaneously accessing the data in parallel.
In addition, files and filegroups allow data layouts that can be created in a specific filegroup. Performance improves because all input/output from a particular table can be directed to a specific disk
。 For example, you can place the most commonly used tables in one file in a filegroup, on one disk, and the other infrequently accessed tables in the database in another filegroup
This filegroup is located on the second disk in the other files in the
. Suggestions on how to use filegroups
Q Most databases work well in the case of a single data file and a single transaction log file.
Q If you use more than one file, create a second filegroup for the attached file and set it as the default filegroup. This way, the primary file will contain only system tables and objects.
Q For best performance, create files or filegroups on as many available local physical disks as possible, and place the objects that have the most contention for space into different filegroups.
Q uses filegroups to allow objects to be placed on specific physical disks.
Q places different tables that are used in the same join query in different filegroups. Performance can be improved by using parallel disk input/output to search for joined data.
Q places the most frequently accessed tables and the nonclustered indexes that belong to those tables on different filegroups. If the file is on a different physical disk, the parallel input/output is used, so
Performance improved.
Q Do not place log files on the same physical disk as other files and filegroups.
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.
