Create Database for basic DDL statements

Create Database for basic DDL statements

In SQL terminology, a database is a container that contains the relevant base table View index stored procedures and its
Before creating these objects, the object must have a database that stores them.
Step by step, there is an owner for certain types of objects, such as tables, as long as they belong to different users, you can
The same name exists in the same database. However, it is recommended that duplicate names not appear in product systems.
Most product objects are owned by the database owner.
To create a database, the user must be a system administrator or be authorized to use the create database statement create
The simplest form of the database command is as follows:
Create Database appdta
This statement creates an appdta database and copies the SQL server model database definition to the new data.
Library, that isModelEmpty copies of each table view stored procedure in the database are copied in the new
Create SQL server in the database create two NT server files for this database appdta. MDF to save data
Appdta_log.ldf stores the transaction log content. The default initial size of the two files is set to the model database.
The size of the main file and log file. If you need to, SQL Server will automatically expand these files.
1.Specify location and size

Create   Database Appdta
On   Primary
(Name = Appdta1,
Filename =   ' E: sqk2005productiondataappdtal. MDF ' ,
Size = 10 MB,
Maxsize = 100 MB,
Filegrowth = 10 MB ),
(Name = Appdta2,
Filename =   ' E: sqk2005productiondataappdta2. MDF ' ,
Size = 10 MB,
Filegrowth = 10 MB)
Log   On
(Name = Appdtalog1,
Filename =   ' F: sqk2005productionlogappdtalog1. LDF ' ,
Size = 10 MB,
Maxsize = 100 MB,
Filegrowth = 10 MB)

2 Modify Database
After the database is created, you can use the alter database statement to add new files to delete existing files or modify them.
The following example shows how to add a new file. Alter   Database Appdta
Add   File
(Name = Appdta3,
Filename =   ' E: sqk2005productiondataappdta3. MDF ' ,
Size = 10 MB,
Filegrowth = 10 MB)
-- Alter database appdta
-- Remove File appdta2
-- Alter database appdta
-- Modify file
-- (Name = appdta1,
-- Filegrowth = 50 MB)

3,Define file groups
The database file does not include the transaction log file, which can be used to form a file group when a database is initially created.
The default file group of the database contains the master file and the subordinate files that are not explicitly assigned to the User-Defined file group.
The default file group is sufficient for some systems to create user-defined file groups on specified devices.
It can improve database performance or recoverability. Because you can specify the file group where the table or index is located, the file group provides
An indirect method is used to store tables and indexes on the specified device. In addition, when a file contains many files
SQL Server distributes the data in the file group in proportion to the file according to the free space available to the file.
The following is an example of creating a file group.

Alter   Database Appdta
Add Filegrouup appdtagroup1

Alter   Database Appdta
Add   File
(Name = Appdta4,
Filename =   ' E: sqk2005productiondataappdta4. MDF ' ,
Size = 10 MB,
Maxsize = 100 MB,
Filegrowth = 10 MB ),
(Name = Appdta5,
Filename =   ' E: sqk2005productiondataappdta5. MDF ' ,
Size = 10 MB,
Maxsize = 100 MB,
Filegrowth = 10 MB)
To Filegroup appdtagroup1

-- Alter database appdta
-- Remove File appdta4
-- Alter database appdta
-- Remove File appdta5
-- Alter database appdta
-- Remove filegroup appdtagroup1
-- Alter database appdata
-- Modify filegroup appdtagroupl default

They must be blank when deleting files or file groups.

You can also use the alter database statement to change the default file group of a database, for example, the following statement.Alter database appdata
Modify filegroup appdtagroupl default