Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Jun 7, 2002

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

By Michael Aubert

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 ]

< filespec > ::=

[ 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
  NAME = DBbySQL_data2,
  FILENAME = 'c:\DBbySQL_data2.ndf',
  SIZE = 5MB,

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.

Page 3: Understanding Transaction Logs

 » See All Articles by Columnist Michael Aubert

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM