Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "the method of creating database by SQL Server". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

1. Introduction to creating a database

Before you create a database, you need to determine the name, owner, size, files, and filegroups of the database.

Database owner: the user who created the database. In general, most product objects are owned by the database owner.

2. Considerations for creating a database

CREATE DATABASE, CREATE ANY DATABASE, or ALTER ANY DATABASE permissions are required to create a database.

The user who created the database will become the owner of the database.

A maximum of 32767 databases can be created for one SQL Server instance.

Database names must follow the rules specified for identifiers.

When you create a new database, all user-defined objects in the model database are copied to the newly created database. You can add any common objects (such as tables, views, stored procedures, and data types) to the model database, and then copy these objects to the newly created database in the created database.

3. Create the syntax format of the database

The syntax format is as follows:

CREATE DATABASE database_name [ON [PRIMARY] [< filespec > [,... n] [, < filegroup > [,... n]] [LOG ON {< filespec > [,... n]}] [COLLATE collation_name]] [;]

Parameter description:

Database_name: database name.

ON: specifies the data file in which the data portion of the database is stored in an explicit definition.

PRIMARY: specifies the master file in the list. The first file in the item will be the master file. If no PRIMARY is specified, the first file becomes the database master file by default.

LOG ON: specifies the log file in which the database logs are stored. The LOG ON is followed by a comma-separated list of entries used to define the log file. If you do not specify LOG ON, a log file will be automatically created with a file size equal to 1max 4 or 512 KB of the sum of all the data file sizes in the database, whichever is the larger.

COLLATE collation_name: specifies the default collation for the database. Collation names include Windows collation and SQL collation names. No collation is specified, the default collation for the SQL Server instance is assigned as the collation for the database.

The part is mainly used to control file attributes, and the syntax format is as follows:

(NAME = logical_file_name, FILENAME = 'os_file_name' [, SIZE = size [KB | MB | GB | TB]] [, MAXSIZE = {max_size [KB | MB | GB | TB] | UNLIMITED}] [, FILEGROWTH = growth_increment [KB | MB | GB | TB |%]]

Logical_file_name: specifies the logical name of the file. The logical_file_name must be unique in the database and must comply with the prescribed rules for identifiers.

'os_file_name': specifies the operating system (physical) file name. The specified file path must exist before the create database statement can be executed. If the UNC (Universal naming Convention) path is specified, the SIZE, MAXSIZE, and FILEGROWTH parameters cannot be set.

Size: specifies the initial size of the file. No master file specified size, the database engine will use the size of the master file in the model database. If a secondary data file or log file is specified, but the size for the file is not specified, the Database engine takes 1 MB as the size of the file.

You can use kilobytes (KB), megabytes (MB), gigabytes (GB), or megabytes (TB) suffixes, the default unit is MB.

Max_size: specifies the maximum to which the file can grow. You can use the KB, MB, GB, and TB suffixes. The default unit is MB.

UNLIMITED: specifies that the file can grow until the disk space is full. In SQL Server, the maximum value for log files specified as unlimited growth is 2 TB, while the maximum value for data files is 16 TB.

Growth_increment: specifies the amount of space added to the file each time a new space is needed. The growth_increment value cannot exceed the MAXSIZE setting value. This value can be specified in units using MB, KB, GB, TB, or percentage (%). The default is MB. A growth_ increment value of 0 indicates that automatic growth is turned off and additional space is not allowed.

If FILEGROWTH is not specified, the default value for data files is 1 MB, the default growth rate for log files is 10%, and the minimum value is 64 KB.

The part is mainly used to control filegroup attributes, and the syntax format is as follows:

FILEGROUP filegroup_name [DEFAULT] < filespec > [,... n]

Filegroup_name: must be unique in the database and cannot be the system-supplied names PRIMARY and PRIMARY_LOG.

DEFAULT: specifies that the filegroup is the default filegroup in the database.

4. Create a database example

1. The simplest example of creating a database

Create database TestDB

The database is created based on the SQLServer default settings (file storage location, file size increase, and so on).

two。 Database example for specifying data and transaction log files

IF DB_ID (NationTestDB') is not null-determines whether the database exists or not. If it exists, delete DROP DATABASE TestDB GO CREATE DATABASE TestDB ON (NAME = TestDB,-- logical database file name FILENAME ='D:\ TestDB.mdf', SIZE = 10, MAXSIZE = 200, FILEGROWTH = 5) LOG ON (NAME = TestDB_log) -- logical database log file name FILENAME ='D:\ TestDB_log.ldf', SIZE = 5MB, MAXSIZE = 50MB, FILEGROWTH = 5MB)

3. Example of specifying multiple data files and transaction log files

USE master GO IF DB_ID (NationTestDB') is not null-if there is a database, delete DROP DATABASE TestDB GO CREATE DATABASE TestDB ON PRIMARY (NAME = TestDB1, FILENAME ='d:\ TestDB1.mdf', SIZE = 100MB, MAXSIZE = 200, FILEGROWTH = 20), (NAME = TestDB2, FILENAME ='d:\ TestDB2.ndf', SIZE = 100MB MAXSIZE = 200,20) LOG ON (NAME = TestDB_log1, FILENAME ='d:\ TestDB_log1.ldf', SIZE = 30MB, MAXSIZE = 100, FILEGROWTH = 10), (NAME = TestDB_log2, FILENAME ='d:\ TestDB_log2.ldf', SIZE = 100MB, MAXSIZE = 500, FILEGROWTH = 50)

4. Create a database with filegroups the following statement creates the database Sales, which has the following filegroups.

USE master GO IF DB_ID (NationTestDB') is not null-to determine whether the database exists or not, delete DROP DATABASE TestDB GO CREATE DATABASE TestDB ON PRIMARY-- database master file (NAME = TestDB1, FILENAME ='d:\ TestDB1.mdf', SIZE = 20, MAXSIZE = 100, FILEGROWTH = 10%), (NAME = TestDB2, FILENAME ='d:\ TestDB2.ndf') SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5), FILEGROUP DBGroup1 (NAME = TestDB3, FILENAME = ID:\ TestDB3.ndf', SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5), (NAME = TestDB4, FILENAME = ID:\ TestDB4.ndf', SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5) FILEGROUP DBGroup2 (NAME = DB1_Group2, FILENAME = ID:\ DB1_Group2.ndf', SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5), (NAME = DB2_Group2, FILENAME = ID:\ DB2_Group2.ndf', SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5) LOG ON (NAME = TestDB_log FILENAME = 'd:\ TestDB_log.ldf', SIZE = 50MB, MAXSIZE = 500MB, FILEGROWTH = 10MB)

This is the end of the content of "how to create a database in SQL Server". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.