CREATE DATABASE Build Database statement

Original address: http://blog.csdn.net/guguda2008/article/details/5716939

A complete repository statement is similar to this:

IF db_id (' TEST ') is not NULL DROP DATABASE TEST
GO
CREATE DATABASE TEST
On PRIMARY (
Name=test_dat_main1
, Filename= ' d:/sql2008datas/test_dat_main1. MDF '
, SIZE=3MB
, maxsize=unlimited
, FILEGROWTH=5MB
),(
Name=test_dat_main2
, Filename= ' d:/sql2008datas/test_dat_main2. MDF '
, SIZE=3MB
, maxsize=unlimited
, FILEGROWTH=5MB
)
, FILEGROUP test_dat_sub1 DEFAULT (
Name=test_dat_sub1
, Filename= ' D:/sql2008datas/test_dat_sub1. MDF '
, SIZE=2MB
, maxsize=unlimited
, FILEGROWTH=5MB
),(
Name=test_dat_sub2
, Filename= ' D:/sql2008datas/test_dat_sub2. MDF '
, SIZE=2MB
, maxsize=unlimited
, FILEGROWTH=5MB
)
, FILEGROUP test_dat_sub2 (
Name=test_dat_sub3
, Filename= ' D:/sql2008datas/test_dat_sub3. MDF '
, SIZE=2MB
, maxsize=unlimited
, FILEGROWTH=5MB
)
, FILEGROUP test_dat_filestream1 CONTAINS FILESTREAM (
Name=test_dat_filestream1
, Filename= ' e:/sql2008datas/test_dat_filestream1. MDF '
)
, FILEGROUP test_dat_filestream2 CONTAINS FILESTREAM (
Name=test_dat_filestream2
, Filename= ' e:/sql2008datas/test_dat_filestream2. MDF '
)
LOG on (
Name=test_log1
, Filename= ' D:/sql2008datas/test_log1. LDF '
, SIZE=10MB
, maxsize=unlimited
, FILEGROWTH=10MB
),(
Name=test_log2
, Filename= ' d:/sql2008datas/test_log2. LDF '
, SIZE=10MB
, maxsize=unlimited
, FILEGROWTH=10MB
)
COLLATE chinese_prc_ci_as
With
Db_chaining OFF
, trustworthy OFF

A little longer, but it's okay, I'll say 1.1 separate points.

1.

IF db_id (' TEST ') is not NULL DROP DATABASE TEST
GO

This is judged if the database named test is deleted, not within the scope of this article, know what the meaning is good.

2.

CREATE DATABASE TEST

This is the simplest building statement you've just said.

Create DATABASE is a keyword that says you want to set up a data base, and these two sentences cannot be changed.

Test is the name of the library, basically casually hit, as long as it conforms to some specifications, do not play a jumble of characters line, such as Martian text or something. Specific specifications can be seen here

Http://msdn.microsoft.com/zh-cn/library/ms175874.aspx

3.

On PRIMARY (
Name=test_dat_main1
, Filename= ' d:/sql2008datas/test_dat_main1. MDF '
, SIZE=3MB
, maxsize=unlimited
, FILEGROWTH=5MB
),(
Name=test_dat_main2
, Filename= ' d:/sql2008datas/test_dat_main2. MDF '
, SIZE=3MB
, maxsize=unlimited
, FILEGROWTH=5MB
)

Here's a bit more.

MSSQL is a file to store data, in the management of these files can be a number of files in a group. A database must have at least one file and one filegroup, with at least one primary filegroup, and only one master file in the primary filegroup, which is automatically created with some default settings if the user does not specify a primary filegroup. For example, the simplest create dtabase TEST above, the system automatically creates a rpimary filegroup with a test.mdf file.

On primary means that you specify the files that are contained in the primary filegroup. The above statement specifies two files for the main filegroup primary, the first file is the primary file for the database, and the second file is the secondary data file of the same genus primary filegroup. In parentheses, it is the property. If you want to specify properties manually, name and filename must be written, and the next three may not be written. If you do not write, you will have the same settings as the model database.

Name is the logical name of the file, used as the identity of the file when the database is managed, and the file name cannot be duplicate in the database.

FileName is the physical name of the file, which is the path it holds in the operating system. The exact words in MSDN are "files must reside on one of the following devices: Local server, storage area network [SAN], or ISCSI-based network on which SQL Server is installed." ”。 I didn't use the last two, I left to learn to add, the previous one is the path in Windows. Note If the file is in a folder that does not exist, you will get an error, so you should build the folder ahead of time.

Size is the initial size of the file, the primary file is minimum 2MB, and the minor file is 512KB minimum. If size is only followed by a number like 2, the default is 2MB.

MAXSIZE Specifies the maximum size of a file, which can result in an inability to insert or a small-to-large update if all files in the database reach the maximum size or if the disk has no space. MaxSize can write a number, like size, can also write unlimited specifies no limit to the maximum size.

FILEGROWTH specifies how the file will grow, can write the same number as above, or write a percentage, but it is recommended to write a number, because the database is larger and will grow slower by percentage.

A filegroup consists of a defined text and several files wrapped in parentheses, multiple files separated by commas, and each file consists of a pair of parentheses and several comma-delimited attributes, and the syntax is the same, and the following statement is similar to this one.

4.

, FILEGROUP test_dat_sub1 DEFAULT (
Name=test_dat_sub1
, Filename= ' D:/sql2008datas/test_dat_sub1. MDF '
, SIZE=2MB
, maxsize=unlimited
, FILEGROWTH=5MB
),(
Name=test_dat_sub2
, Filename= ' D:/sql2008datas/test_dat_sub2. MDF '
, SIZE=2MB
, maxsize=unlimited
, FILEGROWTH=5MB
)

Except for the first line, the rest is the same as the top. Just say the first line.

Filegroup is the keyword, specifying the user filegroup, followed by the TEST_DAT_SUB1 is the name, default is to specify this filegroup as the default filegroup. The default filegroup means that if you create user data without explicitly specifying it, it will be placed in this filegroup.

The following statement is to allocate two files for the Test_dat_sub1 file group. Not much to say. I'm all copy. A change of name is finished.

5

, FILEGROUP test_dat_sub2 (
Name=test_dat_sub3
, Filename= ' D:/sql2008datas/test_dat_sub3. MDF '
, SIZE=2MB
, maxsize=unlimited
, FILEGROWTH=5MB
)

There is no need to explain the place, can read it.

6

, FILEGROUP test_dat_filestream1 CONTAINS FILESTREAM (
Name=test_dat_filestream1
, Filename= ' e:/sql2008datas/test_dat_filestream1. MDF '
)
, FILEGROUP test_dat_filestream2 CONTAINS FILESTREAM (
Name=test_dat_filestream2
, Filename= ' e:/sql2008datas/test_dat_filestream2. MDF '
)

Note the contains FILESTREAM keyword after the filegroup name, this is the 2008 new Data Flow feature, which is not discussed here first. Anyway, with this keyword, the specified filegroup stores the FILESTREAM binary large object (BLOB) in the file system. Files in this filegroup can only specify name and filename two properties, where the last folder in filename cannot exist in the system, such as d:/abc/123/xxx. MDF, you must have an ABC folder on the D drive, but you cannot have a folder named 123.

There can be only one file in another FILESTREAM filegroup, but multiple FileStream filegroups can be specified.

7

LOG on (
Name=test_log1
, Filename= ' D:/sql2008datas/test_log1. LDF '
, SIZE=10MB
, maxsize=unlimited
, FILEGROWTH=10MB
),(
Name=test_log2
, Filename= ' d:/sql2008datas/test_log2. LDF '
, SIZE=10MB
, maxsize=unlimited
, FILEGROWTH=10MB
)

Log on is the specified database log file for the operation of the database log, but the more disgusting is that MS does not provide a tool to view log files, want to learn through the log file history to use third-party tools. If you do not specify a log file, an LDF file with the same name as the database is created by default and stored in the same directory as the system database. It is recommended that log files and data files be stored on two hard disks.

8

COLLATE chinese_prc_ci_as

Specifies the collation of the database, if it is not written, by default for the database. Collation I'm not sure, it's also about the system, MS's instructions are too simple. Learn later and then write another.

9

With
Db_chaining OFF
, trustworthy OFF

Controls two-way access between the external and the database, db_chaining specifies whether the database can be the source or target of cross-database ownership chaining, and trustworthy specifies that the database module in the impersonation context cannot access resources other than the database. Can not write, will default to off, interested in this thing can go through the information on their own. This I have never used, and so after learning to add to this blog post.

Attach database and database snapshot syntax and this is almost, waiting for another write, if you write too much after reading the back will forget the front.

The above is the entire composition of the construction database statement, except FileStream are 2005 and 2008 general. This article is written to the database is not very familiar with the novice, so if you are novice and there is no explanation to read, please leave me a message. If you find that there is something wrong in the text, please leave me a message.

CREATE DATABASE Build Database statement