SQL - Structured Query Language

CREATE DATABASE



CREATE DATABASE keywords are used to create a new database in the SQL Server along with customized Primary Data File, Secondary Data File(s) and Log Information File(s). FILEGROUP keyword is used to combine Secondary Data Files to optimize Structure of Database.

Example 01

System Defined Data and Log Files

The following example is used to create eEducation database in the SQL Server. When execute this T-SQL statement, SQL Server should create system defined logical_file_name and os_file_name that combination of database_name with suffix extension file types.

Primary data file size is equal of the model database primary data file size. Log file size is 512 KB or 25% the size of the primary data file. This database MAXSIZE is not specified, the file can grow to fill all available disk space of primary data file location.

-- Example 01 --

         

USE master;

GO

         

CREATE DATABASE eEducation

GO


Example 02

User Defined Data and Log Files

The following example is used to create eBusiness database in the SQL Server. When execute this T-SQL statement, SQL Server should create user defined eBusiness_data.mdf and eBusiness_log.ldf files along with specified file size information in the form of MB suffix.

These data and log files should create in the SQL Server default file path that is "C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\"

PRIMARY keyword is not defined in this T-SQL statement, SQL Server implicitly take the first data file is a Primary file of this database.

-- Example 02 --

       

USE master;

GO

       

CREATE DATABASE eBusiness

ON

( NAME = eBusiness_data,

  FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\eBusiness_data.mdf',

  SIZE = 10,

  MAXSIZE = 50,

  FILEGROWTH = 5 )

LOG ON

( NAME = eBusiness_log,

  FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\eBusiness_log.ldf',

  SIZE = 5MB,

  MAXSIZE = 25MB,

  FILEGROWTH = 5MB );

GO



DATABASE NAME

Database names must be unique within an instance of SQL Server and follow up the SQL Server Identifiers Rules. Database name can be a maximum of 128 characters.

SQL Server is implicitly generates logical_file_name and os_file_name that combination of database_name with suffix extension file types. SQL Server is recognizes user defined Primary, Secondary, Log and Filegroups that helps to optimize Physical Database Structure.

ON

ON Keyword is used to recognize user defined Primary and Secondary and Filegroups Information that configure with the Database.

PRIMARY

PRIMARY Keyword is used to define Physical Primary Data File from specified list of data files. If PRIMARY is not defined, SQL Server is implicitly configures the first data file as Physical Primary Data File from specified list of data files.

LOG ON

LOG ON Keywords are used to recognize user defined Log File Information that configure with the Database. If LOG file size is not defined, SQL Server is implicitly allocated the file size is 25% percent of the sum of the sizes of all the data files for the database or 512 KB, whichever is larger.

SIZE size

SIZE Keyword is used to define Primary, Secondary or Log File Size. When file size is not defined, SQL Server is implicitly allocated Model Database Primary File Size into User Defined Primary File Size and 1 MB is allocated for Secondary and Log File Size.

The kilobyte (KB), megabyte (MB), gigabyte (GB), or terabyte (TB) suffixes can be used to define file size that default is MB.

MAXSIZE max_size

MAXSIZE Keyword is used to define Primary, Secondary or Log Max File Size in the form of kilobyte (KB), megabyte (MB), gigabyte (GB), or terabyte (TB) suffixes can be used to define file size that default is MB.

Email Your Comment To AUTHOR