Journal of T-SQL On [Primary]
Nothing these days have nothing. It seems that there is a busy thing before the year, but there is a good fault after the year. . . .
Today, I started to see the database development examples of the school experiment guidance. I have never been seen before. . .
In the statement of the table, the last time I always have an on [primary]. What does this mean?
Turn over the entire machine. There seems to be found under the syntax of the Create Table. After hard scalp, I finally understood some.
Write out. Teacher Hou said that it is the best memory. :)
There is such an optional selection in the syntax of the Create Table: [ON {fileGroup | default}]
The file group used to specify the storage table. If FileGroup is specified, the table will be stored in the specified file group. This file group must be exist in the database. If the default is specified, or 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 of the default file group and needs to be bound, such as "default", ON [default].)
ON [primary] means that the table is built on the main file group. PRIMARY represents the primary file group. If you do not specify a default file group, the primary file group is the default file group, ON [primary] can also be omitted.
Say by the database. Microsoft® SQL ServerTM 2000 uses a set of operating system file mapping databases, or it can be said to map databases to a set of operating system files (see how you understand). All data and objects in the database are stored in the operating system files (database files): primary data files, secondary data files, and transaction log files.
1. Main data file This file contains the startup information of the database and is used to store data. It is the starting point of the database, pointing to other parts of the file in the database. Each database has a primary data file. The recommended file extension of the main data file is. Mdf.
2. Secondary Data Files These files contain all data that cannot be placed in the primary data file, which contains all data files except the primary data file. Some databases may have no secondary data files, while some databases have multiple sub-data files. (If the primary file can contain all the data in the database, the database does not require a secondary data file. Some databases may need more than a few secondary data files, or expand data using auxiliary files located on different disk drives. Multiple disks.) The recommended file extension of the secondary data file is .ndf.
3. Transaction log file The log information of the database. Each database must have at least one log file. The recommended file extension of the log file is .ldf.
File groups can divide database files into different file groups for allocation and management purposes. The file group is a logical management unit for the SQL Server 2000 data file. The naming collection of one or more files in SQL Server, which constitutes a single unit assigned or used for database management. You can create a file group when you build a database for the first time, or add more files to your database. The file group cannot be created independently of the database file. The file group is a management mechanism to group files in the database. There are two types of file groups: main file groups and user definition file groups. (It can be said that the database consists of a file group defined by a primary file group and any user.)
1. Primary User Defining File Group The default file group is a type of file group. It contains major data files and any other files that are not explicitly assigned to other file groups. All pages of the system table are allocated in the primary file group. 2. User-defined file group User-defined file group is in the CREATE DATABASE or ALTER DATABASE statement, use the FileGroup keyword or "Properties" dialog in SQL Server Enterprise Manager The specified file group. Another: The default file group (DEFAULT file group) file group is included, which contains the page of all tables and indexes that do not specify the file group when creating. In each database, only one file group can be a default file group each time. If you do not specify a default file group, the default file group is a primary file group. The default file group can be changed with the ALTER DATABASE statement. By changing the default file group, any object that is not specified when creating the file group is assigned to the data file in the new default file group. However, the system objects and tables remain in the primary file group instead of the new default file group. (There is a file group in each database as the default file group. When SQL Server does not assign a table or an index assignment page for its specified file group, there will be one at a time. The file group as the default file group.db_owner fixed database role member can switch the default file group from one file group to the other. If the default file group is not specified, the primary file group is the default file group.)
Note: SQL Server 2000 can work effectively when there is no file group, so many systems do not need to specify user definition file groups. In this case, all files are included in the main file group, and SQL Server 2000 can assign data anywhere within the database. The file group is not the only way to assign I / O between multiple drives.
Attachment:
Why establish a file group
There are two purposes in the establishment of the file group.
Q One is to better allocate and manage storage spaces to improve the performance of the database by controlling data and indexing on a particular disk drive.
Q II is because the operating system limits the size of the physical file, so when the data file on a disk exceeds the maximum value allowed by a single file, you can use the file group.
Data file stored on other drives continues to expand storage. Data file and file group usage rules
The use rules for data files and file groups in SQL Server 2000 include:
Q Data file or file group cannot be used by more than one database.
Q Data file can only be a member of a file group.
Q Data files and log files cannot belong to the same file or file group.
Q The log file cannot belong to any file group.
q Only all data files in the file group have no space, the file group's file will automatically grow.
File group works
File groups use all files in the group to use a proportional fill policy. When the data is written to the file group, SQL Server 2000 will write a certain proportion of data according to the amount of available space in the file.
Each file of a group, rather than writing all the data first, then writes the next file. For example, if the file 1 has 100MB free space, file 2 has 200MB
With space, a panel is assigned from file 1, allocating two panels from file 2, and so on. In this way, the two files are almost filling.
Once all files in the file group are full, SQL Server 2000 automatically uses a loop mode once extended a file to accommodate more data (assuming the database is set to automatically increase)
. For example, a file group consists of three files, which are set to automatic growth. When all files in the file group are running out, only the first file is expanded. When the first file is full,
The second file cannot be written when more data is written to the file group. When the second file is full, the third file is extended when more data is written to the file group. If the third file is full, you cannot write more data to the file group, then expand the first file again, and push it again.
At the time of this characteristics of the file group, the database is allowed to create a database across multiple disks, multiple disk controllers, or RAID systems to improve database performance. For example, if
There are 4 disks in the computers, then you can create a database consisting of 3 data files and a log file, and place a file on each disk. When visiting the data, four
A read / write head can accelerate database operations in parallel.
In addition, files and file groups allow data layout to create a table in a specific file group. Because all input / outputs of a particular table can be oriented to a specific disk, the performance is improved.
. For example, you can place the most commonly used table in one file in a file group, which is on one disk; and put the other files in the database in another file group.
In other files, the file group is on the second disk. How to use the file group advice
Q Most databases can run well in the case of only a single data file and a single transaction log file.
Q If you use multiple files, create a second file group for attached files and set it to the default file group. This way, the main file will only contain system tables and objects.
q To get the best performance, create a file or file group as possible on the local physical disk, and place the most intense target for space in a different file group.
Q Use the file group to allow the object to be placed on a specific physical disk.
Q Place different tables used in the same junction in different file groups. Since the connection data is searched by parallel disk input / output, performance will be improved.
Q Turning the most commonly accessed tables and non-aggregated indexes belonging to these tables on different file groups. If the file is on a different physical disk, since parallel input / output is adopted,
Performance is improved.
Q Do not place log files on the same physical disk with other files and files.
