MSSQL Basic Learning--database operation

1. Create a single database

CREATE DATABASETest on(NAME=Main_dat,--Database nameFILENAME= 'e:/program files/microsoft SQL Server/mssql/data/maindat.mdf',--Physical PathSIZE= Ten,--initial capacity, default unit is MBMAXSIZE=  -,--Maximum CapacityFileGrowth= 5 　　 --Growth Value) LOG  on(NAME= 'Main_log', FILENAME= 'e:/program files/microsoft SQL Server/mssql/data/maindat.ldf', SIZE=5mb,maxsize=25mb,filegrowth=5MB)

2. Create a multi-file database with filegroups

CREATE DATABASE Company onPRIMARY 　 --Master data filegroup, which can be directly used primary(NAME=Pri1_dat,filename= 'e:/program files/microsoft SQL Server/mssql/data/pri1dat.mdf', SIZE= Ten, MAXSIZE=  -, FileGrowth=  the%  　 --you can also use percentages as a growth rate.),--when there are multiple files in a group, separate them with ","(NAME=Pri2_dat,filename= 'e:/program files/microsoft SQL Server/mssql/data/pri2dat.ndf',--a database can have only 1 MDFSIZE= Ten, MAXSIZE=  -, FileGrowth=  the%), FILEGROUP ComGroup1 (NAME=Grp1file1_dat,filename= 'e:/program files/microsoft SQL Server/mssql/data/g1fi1dt.ndf', SIZE= Ten, MAXSIZE=  -, FileGrowth= 5), (NAME=Grp1file2_dat,filename= 'e:/program files/microsoft SQL Server/mssql/data/g1fi2dt.ndf', SIZE= Ten, MAXSIZE=  -, FileGrowth= 5), FILEGROUP ComGroup2 (NAME=Grp2file1_dat,filename= 'e:/program files/microsoft SQL Server/mssql/data/g2fi1dt.ndf', SIZE= Ten, MAXSIZE=  -, FileGrowth= 5), (NAME=Grp2file2_dat,filename= 'e:/program files/microsoft SQL Server/mssql/data/g2fi2dt.ndf', SIZE= Ten, MAXSIZE=  -, FileGrowth= 5 )LOG  on(NAME= 'Company_log', FILENAME= 'e:/program files/microsoft SQL Server/mssql/data/comlog.ldf', SIZE=5mb,maxsize=25mb,filegrowth=5MB)GO

3. View database Information

Use stored procedure sp_helpdb to view information for all databases on the current server, and to return the specified database information if the name of the database is specified

sp_helpdbsp_helpdb Pubs

Use Sp_databases to view all available databases on the current server

Sp_databases

Use sp_helpfile to view information about all the files on the current database, and if you specify the name of the file, the information for that file is returned

 Use pubs sp_helpfilesp_helpfile Pubs_log --

Use Sp_helpfilegroup to view information for all filegroups and, if the name of the filegroup is specified, returns information for that filegroup

Sp_helpfilegroupsp_helpfilegroup Own_group

4. Modify the database configuration

Most typically, the stored procedure is sp_dboption, you can see all the items you can set, you can modify their properties by specifying a specific item

' pubs ' ' Select Into/bulkcopy ' ' false '

5. Modify the size of the database

Modifying the size of a database essentially modifies the length of the data file and log file, or adds/deletes the operating system files.

Add data files

--This example adds a data file to the default filegroup of the company database primary file group Pri3_datALTER DATABASE CompanyADD FILE(NAME=Pri3_dat,filename= 'e:program files/microsoft SQL Server/mssql/data/pri3dat.ndf', SIZE= 5, MAXSIZE=  -, FileGrowth= 5 ) 

The new data file created above is placed in the default filegroup primary, and if you want to create a different filegroup, add the TO filegroup command

--This example adds a data file Pri4_dat to the group ComGroup1ALTER DATABASE CompanyADD FILE(NAME=Pri4_dat,filename= 'e:program files/microsoft SQL Server/mssql/data/pri4dat.ndf', SIZE= 5, MAXSIZE=  -, FileGrowth= 5 ) toFILEGROUP ComGroup1

Set a filegroup as the default file group

ALTER DATABASE  DEFAULT

Increase database file size (at the same time you can modify the maximum capacity and growth rate)

ALTER DATABASE    FILE= ==   =3%  )

Delete a database file

ALTER DATABASE  FILE

MSSQL Basic Learning--database operation