SQL Server 2000 Administration in 15 Minutes a Week: More Database Creation Topics - Page 2

June 7, 2002

To create a database we use the CREATE DATABASE statement. Let's look at the syntax for this statement:

CREATE DATABASE database_name
[ ON
    [ < filespec > [ ,...n ] ]
    [ , < filegroup > [ ,...n ] ]
]
[ LOG ON { < filespec > [ ,...n ] } ]
[ COLLATE collation_name ]
[ FOR LOAD | FOR ATTACH ]

< filespec > ::=

[ PRIMARY ]
( [ NAME = logical_file_name , ]
    FILENAME = 'os_file_name'
    [ , SIZE = size ]
    [ , MAXSIZE = { max_size | UNLIMITED } ]
    [ , FILEGROWTH = growth_increment ] ) [ ,...n ]

< filegroup > ::=

FILEGROUP filegroup_name < filespec > [ ,...n ]

Confusing isn't it? To brake it down line by line have a look at the following link: Transact-SQL Reference: CREATE DATABASE You can also find this information in the  SQL Server Books Online under "CREATE DATABASE".

For example we are going to create a database called DBbySQL with a 5 MB data file and 1 MB log file. Enter the following statement into the query window:

There are a few things you should note here. First, the USE statement changes the Database context (the database we are working with). In this case, we are adding a new database so we use the master database. Next, the GO command tells SQL Query Analyzer to execute the current batch of Transact-SQL statements. A batch is simply a set of Transact-SQL statements from the last GO command or from the start of the script. It is important to know that GO is not a SQL statement, rather it is a command that you can use to tell SQL Query Analyzer or OSQL (and ISQL for that madder) to send the current batch of Transact-SQL statements to SQL Server.

One last thing to notice about this script is the CREATE DATABASE statement itself. More specifically, we don't have to specify every option available. For the options we did not specify, SQL server will use default values. Also when we set the SIZE of the files, we don't have to specify MB because MB is the default for the SIZE value (although for readability you may want to add MB anyway).

Once you have entered the statement, select Execute from the Query menu. The output from Query Analyzer should look like the following:

That's it! We now have a new database created on our SQL Server.

You can also use the ALTER DATABASE statement to modify an existing database. For example, the following statement would add a new data file to the DBbySQL database with a size of 5MB and a Max size of 50MB:

USE master
GO
ALTER DATABASE DBbySQL
ADD FILE
(
  NAME = DBbySQL_data2,
  FILENAME = 'c:\DBbySQL_data2.ndf',
  SIZE = 5MB,
  MAXSIZE = 50MB
)
GO

For more information on the ALTER DATABASE statement see "ALTER DATABASE" in the SQL Server Books Online. This information can also be found on the MSDN website at: Transact-SQL Reference: ALTER DATABASE

Note: I plan to cover the Query Analyzer in more detail in later articles.