Composition of database

SQL Server database consists of a set of operating system files, which are divided into two categories: data files and log files.

1. Data file: contains data and objects, such as tables, indexes, stored procedures and views.

Data file type	characteristic
Master data file	It contains database startup information and the location of other files in the database. The extension is mdf
Secondary data files	Secondary data files are optional, user-defined and store user data. There can be no or multiple in the database. The extension is ndf

2. Log file: used to store all log information of the recovery database. The extension is ldf. Each database has at least one log file or multiple log files.

3. Logical file name and physical file name:

File name type	definition
Logical_file_name	The name used when referring to the physical file in all database statements. The name is unique and conforms to the identifier naming rules
Physical file name (os_file_name)	The name of the physical file containing the directory path must comply with the operating system naming rules

4. Classification of file groups:
There are two types of filegroups in SQL Server: primary filegroups and user-defined filegroups.

name	characteristic
Primary filegroup (default filegroup)	A file group defined by the system, which contains the main data files and all other data files that are not explicitly assigned to other file groups
User defined filegroups	It is created by users to organize relevant data files for easy management and distribution

5. Database file attributes:

1. File name and location
2. initial size
3. Growth mode
4. Maximum size

Create database

All default databases:

create database Database name;   --The default size of data files and log files is 8 MB

Database of one data file and one log file:

create database Database name
on
(	name = Logical file name,
	filename = 'Storage location\Logical file name',
	size = initial size,
	maxsize = Maximum size,
	filegrowth = Growth rate (unit can be MB，It can also be*%)  )
log on
(	name = Logical file name,
	filename = 'Storage location\Logical file name',
	size = initial size,
	maxsize = Maximum size,
	filegrowth = Growth rate (unit can be MB，It can also be*%)  )

Databases with multiple data files and multiple log files:

--Three data files, one of which is located in the main file group primary，The other two are located in datagroup File group
--Two log files
create database Database name
on primary
(	name = Logical file name 1,
	filename = 'Storage location\Physical file name 1',
	size = initial size,
	maxsize = Maximum size,
	filegrowth = Growth rate (unit can be MB，It can also be*%)  ),
	
filegroup datagroup
(	name = Logical file name 2,
	filename = 'Storage location\Physical file name 2',
	size = initial size,
	maxsize = Maximum size,
	filegrowth = Growth rate (unit can be MB，It can also be*%)  ),
(	name = Logical file name 3,
	filename = 'Storage location\Physical file name 3',
	size = initial size,
	maxsize = Maximum size,
	filegrowth = Growth rate (unit can be MB，It can also be*%)  ),
log on
(	name = Logical file name 4,
	filename = 'Storage location\Physical file name 4',
	size = initial size,
	maxsize = Maximum size,
	filegrowth = Growth rate (unit can be MB，It can also be*%)  ),
(	name = Logical file name 5,
	filename = 'Storage location\Physical file name 5',
	size = initial size,
	maxsize = Maximum size,
	filegrowth = Growth rate (unit can be MB，It can also be*%)  )

Operation database

1. Expand Database Space

Add data file:

alter dstabase Database name
add file      --When adding log files file Add before log
{
	name = Logical file name,
	filename = 'Storage location\Physical file name',
	size = initial size,
	maxsize = Maximum size,
	filegrowth = Growth rate  )

Modify initial file size:

alter database Database name
modify file
{
	name = Data file name,
	size = Modified size
	)

1. Shrink database space

Shrink database:

dbcc shrinkdatabase(Database name,Proportion of free space)

Shrink specified file:

dbcc shrinkfile(file name,Target size after shrink)

1. Manage database files

Delete data file:

alter database Database name
remove file file name

To create a filegroup:

alter database Database name
add filegroup New filegroup name

Modify filegroup to default filegroup:

alter database Database name
modify filegroup A default   --File group A Set as default filegroup

Move database files:

alter database Database name
	modify file ( name = File name,
				  filename  = '    ');

1. Delete database

To delete a single database:

drop database Database name

Delete multiple databases:

drop database database A,database B......

1. Detach and Attach Databases

Detach database:

execute sp_detach_db  '  Database name','true/false '    --The second parameter is whether to skip or run update statistics

Additional database:

create database Database name
  on(   filename = '        ')
  for attach

Summary

The operation of the database is the foundation. Given a database, completing the corresponding operation as required is the entry-level operation of the database. We do not need to deliberately remember the T-SQL statements of each operation. Strengthening in use through specific examples is the positive solution.