Several lines of code to create your own SQL database

There are multiple ways to create a database. For example, you can use the Enterprise Manager to create a database, or you can use the Wizard to create a database. The operations in the above two methods are very simple, but there is a major drawback that it is difficult for him to repeat them on a large scale. In other words, if you create a database to be re-distributed to all parts of the world, it is not easy for an inexperienced user to cope with SQL enterprise managers and establish databases. So in order to solve the problem we mentioned above, the method of creating database with T-SQL statement has become.

Before talking about the syntax, I will introduce you to what database files are. Database files are simply stored in the database data or transaction logs we need. Just like folders are used to store files.

There are three types of database files in SQL Server:

Database file type	Description
Master database file	Is the starting point of all databases. These files not only contain information collected by database users, but also information about all other files in the database. Each database has a primary database file. The default extension of the primary database file is. MDF.
Auxiliary database files	As the name suggests, it is actually the other database files that you add to the database. They can be used to increase the database capacity. Not all databases have auxiliary database files, but if you want your database files to be extended to multiple physical disks, you need auxiliary database files. Its Extension uses. NDF by default.
Log Files	Used to save the transaction logs of the database. Each database must have at least one log file. The log file extension uses. LDF.

 

Next, let's get to know the syntax of the create database statement:

Create database <database_name>

On [primary]

[<Filespec> [,... N]

[, <Filegroup> [,... N]

[Log on {<filespec> [,... N]}]

[For Load | for attch]

The above statements are the most basic syntax for creating a database. However, there are some options that need to be discussed slowly.

What do the two options <filespec> and <filegroup> mean? Simply put, they represent the information required to create a file and a file group, which may be difficult to understand. You don't have to worry about the following two syntaxes.

<Filespec >::=

([Name = logical_file_name,]

Filename = 'OS _ file_name'

[, Size = size]

[, Maxsize = {max_size | unlimited}]

[, Filegrowth = growth_increment]) [,… N]

<Filegroupspec >::=

Filegroup filegroup_name <filespec> [,... N]

In size, maxsize, and filegrowth, if the unit is MB, the Unit can be omitted, that is, the default unit is MB. However, other units cannot be omitted.

Don't get dizzy (sneer). It doesn't matter. Let's take a look at the example. In the following example, you can create a database test, a database file test_data, and a log file test_log:

Create Database Test         On primary         (      name=’Test_Data’,                 Filename=’c:\mssql17\data\Test_Data.mdf’,                Size=10[MB],                Maxsize=50[MB],                Filegrowth=5[MB]   )         LOG ON         (      name=’Test_Log’,                Filename=’c:\mssql17\data\Test_Log.ldf’,                Size=5[MB],                Maxsize=25[MB],                Filegrowth=10%    )          GO

After execution in the query analyzer in SQL 2000, you will see the following results:

The create database process is allocating 10.00 MB space on the disk 'test _ data.

The create database process is allocating 5.00 MB space on the disk 'test _ log.

The section in the brackets above is the <filespec> section, which describes the created database. How about it? It's easy to change.