@[toc]

SQL statement to create the database

example

CREATE DATABASE NewsDB
ON PRIMARY
(
	NAME=NewsDB,
	FILENAME='D:\XXXX\NewsDB.mdf',	
	SIZE=10MB,
	MAXSIZE=30MB,
	FILEGROWTH=10%
)
LOG ON 
( 
	NAME = NewsDB_log, 
	FILENAME = 'D:\XXXX\NewsDB_log.ldf', 
	SIZE = 5MB , 
	MAXSIZE=20MB,
	FILEGROWTH=5%
)
GO
Copy the code

The basic format of the CREATE DATABASE statement is as follows:

  • Database_name: specifies the name of the new database. The database name must be unique in the same SQL Server instance and can contain a maximum of 128 characters.
  • ON: Explicitly defines the data file used to store the data portion of the database. When followed by a comma-separated data file used to define the main file group<filespec>When listing items, use ON.
  • <filespec>: Controls file properties. Define data file or log file properties in detail.
  • PRIMARY: indicates the associated<filespec>The list defines the master file. In the main file group<filespec>The first file specified in the command becomes the primary file. A database can only have one primary file.
  • <filegroup>: Controls file group properties.
  • LOG ON: Explicitly defines the database LOG file. LOG ON followed by comma-delimited to define LOG files<filespec>The item list.
  • COLLATE collation_name: specifies the default collation rule for the database.
  • WITH <external_access_option>: Controls two-way access between the external and the database.

Filespec is defined in the following format:

  • <filespec>: Controls file properties.
  • NAME=logical_file_name: specifies the logical NAME of the file.
  • FILENAME=’ OS_FILe_name ‘: Specifies the name of the operating system (physical) file. Os_file_name specifies the path and name used by the OPERATING system during file creation.
  • SIZE= SIZE: Specifies the file SIZE. If no size is provided for the primary file, the database engine will use the size of the primary file in the Model database, which is MB by default.
  • MAXSIZE=max_size: Specifies the maximum size to which the file can be increased.
  • FILEGROWTH=growth_increment: specifies the automatic increment of a file. Growth_increment is the amount of space that is added to a file each time a new space is required. This value can be specified as a fixed value or as a percentage (%).
  • UNLIMITED: Specifies that the file will grow to disk full.

Filegroup is defined as follows

  • FILEGROUP filegroup_name: indicates the logical name of a FILEGROUP.
  • DEFAULT: Specifies the named file group as the DEFAULT file group in the database.

External_access_option is defined as follows:

  • DB_CHAINING {ON | OFF} : when specified as ON database can link the source or destination of cross-database ownership. When OFF, the database cannot participate in cross-database ownership links. The default value is OFF.
  • TRUSTWORTHY {ON | OFF} : when specified as ON, using simulation context outside the database module can access the database of resources. The default value is OFF. As soon as you attach the database, TRUSTWORTHY will be set to OFF.

SQL statement modifies the database

example

Add a log file.

CREATE DATABASE NewsDB
ADD LOG FILE
( 
	NAME = NewsDB_log, 
	FILENAME = 'D:\XXXX\NewsDB_log.ldf', 
	SIZE = 5MB , 
	MAXSIZE=20MB,
	FILEGROWTH=5%
)
GO
Copy the code

Modify the collation rules of the database.

ALTER DATABASE NewsDB
COLLATE Chinese_PRC_CI_AS_KS
Copy the code

The syntax of the ALTER DATABASE statement is as follows

  • Database_name: specifies the name of the database to be modified.
  • <add_or_modify_files>: : = : Specifies the file to add or modify.
  • <add_or_modify_filegroups>: : = : Adds or deletes file groups from the database.
  • <set_database_options>: Sets database options.
  • MODIFY NAME=new_database_name: Rename the database with the specified NAME.
  • COLLATE collation_name: specifies the collation rule of the database.

The syntax for the < add_or_MODIfy_files > clause is as follows:

  • ADD FILE: Adds a FILE to the database.
  • The TO FILEGROUP {filegroup_name | DEFAULT} : add the specified file TO the file group.
  • ADD LOG FILE: Adds the LOG FILE to be added to the specified database.
  • REMOVE FILE logical_file_name: Deletes logical files and physical files from the SQL Server instance. Unless the file is empty; Otherwise, the file cannot be deleted.
  • MODIFY FILE: Specifies the FILE to be modified. Only one FILE can be modified at a time<filespec>Properties.