Creat DataBase Through Sql

This example creates a database called Sales. Because the keyword PRIMARY is not used, the first file (Sales_dat) becomes the primary file. Because neither MB or KB is specified in the SIZE parameter for the Sales_dat file, it defaults to MB and is allocated in megabytes. The Sales_log file is allocated in megabytes because the MB suffix is explicitly stated in the SIZE parameter.


USE master
GO
CREATE DATABASE Sales
ON
( NAME = Sales_dat,
FILENAME = 'c:\program files\microsoftsqlserver\mssql\data\saledat.mdf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 )
LOG ON
( NAME = 'Sales_log',
FILENAME = 'c:\program files\microsoftsqlserver\mssql\data\salelog.ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB )
GO

database_name
Sales is the name of the new database. Database names must be unique within a server and conform to the rules for identifiers. database_name can be a maximum of 128 characters, unless no logical name is specified for the log. If no logical log file name is specified, Microsoft® SQL Server™ generates a logical name by appending a suffix to database_name. This limits database_name to 123 characters so that the generated logical log file name is less than 128 characters.

ON

Specifies that the disk files used to store the data portions of the database (data files) are defined explicitly. The keyword is required when followed by a comma-separated list of items defining the data files for the primary filegroup. The list of files in the primary filegroup can be followed by an optional, comma-separated list of items defining user filegroups and their files.
n
Is a placeholder indicating that multiple files can be specified for the new database.

LOG ON
Specifies that the disk files used to store the database log (log files) are explicitly defined. The keyword is followed by a comma-separated list of items defining the log files. If LOG ON is not specified, a single log file is automatically created with a system-generated name and a size that is the larger of these values:.5 MB or 25 percent of the sum of the sizes of all the data files for the database.

PRIMARY
Specifies that the associated list defines the primary file. The primary filegroup contains all of the database system tables. It also contains all objects not assigned to user filegroups. The first entry in the primary filegroup becomes the primary file, which is the file containing the logical start of the database and its system tables. A database can have only one primary file. If PRIMARY is not specified, the first file listed in the CREATE DATABASE statement becomes the primary file.

NAME
Specifies the logical name for the file defined by the . The NAME parameter is not required when FOR ATTACH is specified.

FILENAME
Specifies the operating-system file name for the file defined by the .

SIZE
Specifies the size of the file defined in the . When a SIZE parameter is not supplied in the for a primary file, SQL Server uses the size of the primary file in the modeldatabase. When a SIZE parameter is not specified in the for a secondary or log file, SQL Server makes the file 1 MB.

size
Is the initial size of the file defined in the . The kilobyte (KB), megabyte (MB), gigabyte (GB), or terabyte (TB) suffixes can be used. The default is MB. Specify a whole number; do not include a decimal. The minimum value for size is 512 KB. If size is not specified, the default is 1 MB. The size specified for the primary file must be at least as large as the primary file of the model database.

MAXSIZE
Specifies the maximum size to which the file defined in the can grow.
max_size
Is the maximum size to which the file defined in the can grow. The kilobyte (KB), megabyte (MB), gigabyte (GB), or terabyte (TB) suffixes can be used. The default is MB. Specify a whole number; do not include a decimal. If max_size is not specified, the file grows until the disk is full.

FILEGROWTH
Specifies the growth increment of the file defined in the . The FILEGROWTH setting for a file cannot exceed the MAXSIZE setting.

growth_increment
Is the amount of space added to the file each time new space is needed. Specify a whole number; do not include a decimal. A value of 0 indicates no growth. The value can be specified in MB, KB, GB, TB, or percent (%). If a number is specified without an MB, KB, or % suffix, the default is MB. When % is specified, the growth increment size is the specified percentage of the size of the file at the time the increment occurs. If FILEGROWTH is not specified, the default value is 10 percent and the minimum value is 64 KB. The size specified is rounded to the nearest 64 KB.