Creating a Database in SQL Server 2000
- Creating a Database in SQL Server 2000
- Creating a Database in Enterprise Manager
- About this Article
When you create a new database, you are really just making a copy of the model database. Remember that everything in the model database, including any database options that you may have set, will show up in any new databases you create. After you create the database by copying the model database, it expands to whatever size you've requested and fills the additional space with empty storage pages.
To create a new database in SQL Server 2000, you can use one of three methods:
-
The CREATE DATABASE statement
-
The SQL Server Enterprise Manager
-
The Database Creation Wizard
Databases need files to physically store their data on disk. When you create a new database, you should specify at least one file to store data and system tables, and a separate file to hold your transaction log. Your database and transaction log can span multiple files, as shown in Figure 1. The Trade database in this example has three separate data files and one file for its transaction log.
){kind=link}
A database and transaction log can span multiple database files.
NOTE
The database files you create can't be shared by any other database or transaction log.
Using the CREATE DATABASE Statement
Here, you will break down the CREATE DATABASE statement and learn what each different parameter means. When you understand what's being accomplished, you'll see how to create a database by using the SQL Server Enterprise Manager. The CREATE DATABASE statement is as follows:
CREATE DATABASE database_name
[ON {[PRIMARY]
(NAME = logical_name,
FILENAME ='physical_name'
[,SIZE = size]
[,MAXSIZE = max_size | UNLIMITED]
[,FILEGROWTH = growth_increment])
}[,...n]]
[LOG ON
{(NAME = logical_name,
FILENAME = 'physical_name'
[,SIZE=size | UNLIMITED]
[,MAXSIZE = max_size | UNLIMITED]
[,FILEGROWTH = growth_increment])}
[,...n]]
[,COLLATE collation_name]
[FOR LOAD | FOR ATTACH]
In SQL Server 2000, the only parameter that you need to include to create a database is the database's logical NAME. Although creating the database this way is possible in SQL Server 2000, it's not recommended. We suggest that you include the following parameters at a minimum: logical database name, filename and size for the data file, and transaction log filename and size. The following list describes the available CREATE DATABASE parameters:
-
database_name refers to the database as a whole.
-
ON PRIMARY specifies to which filegroup this database file is a member. The default filegroup is Primary.
-
NAME specifies the logical name you will use within SQL Server to refer to the physical database file on the hard disk.
-
FILENAME is the pathname and filename pertaining to the location of the data on hard disk. It must be a local hard drive.
-
SIZE specifies how big the database file should be. This value can be expressed in megabytes or kilobytes. The default size is the size of the model file. To specify megabytes or kilobytes, attach the MB or KB suffix to your size parameter. For example, 10MB would create a 10 megabyte file.
NOTE
You can specify megabytes only as whole numbers. To create a 2.5 megabyte database, you must use kilobytes, as in 2560KB.
-
MAXSIZE specifies the maximum size to which the database can dynamically grow. If you don't specify a size here and the autogrowth option is turned on, your database could grow to fill your entire hard disk. This parameter is also expressed in either megabytes or kilobytes.
-
FILEGROWTH specifies which increments are used for the autogrowth of this database file. It can be expressed as either a number of megabytes or kilobytes, or as a percentage of the size of the file at the time of the growth. The default, if not specified, is 1MB. The FILEGROWTH option can't exceed the MAXSIZE parameter.
-
LOG ON describes where the transaction log files are located and what size they are.
-
COLLATE, new to SQL Server 2000, specifies the collation sequence used for this particular database. It must be either a SQL Server collation name or a Windows collation name. If you don't specify this parameter, it defaults to the SQL Server 2000 instance's collation name. Collation sequences can also be specified at the table and individual column level.
-
FOR LOAD marks the database for DBO Use Only. The option is provided for backward compatibility with SQL Server 6.5 only, and it shouldn't be used in SQL Server 2000.
-
FOR ATTACH reattaches a set of files that make up a database. The files for the database must have been previously created and then detached from SQL Server 2000.
Listing 1 shows the code necessary for creating a database that starts out reserving 25MB—20MB for the data portion of the database and 5MB for the transaction log. The files could grow to a total of 115MB—100MB for data and 15MB for the transaction log. It also uses the default SQL Server 2000 collation sequence.
TIP
With SQL Server 2000, it might be better for you to specify the amount of space needed right now to store your data and logs rather than reserve the total amount of disk space you might need in the future. You can then take advantage of the FILEGROWTH and MAXSIZE parameters to let the database grow as needed and conserve hard disk space now.
Listing 1: Creating a Database Reserving 25MB
USE master
GO
CREATE DATABASE Frogger ON PRIMARY
( NAME = FroggerData,
FILENAME =
'D:\Program Files\Microsoft SQL Server\MSSQL\Data\FroggerData.mdf',
SIZE = 20MB,
MAXSIZE = 100MB,
FILEGROWTH = 10MB )
LOG ON
( NAME = FroggerLog,
FILENAME =
'D:\Program Files\Microsoft SQL Server\MSSQL\Data\FroggerLog.ldf',
SIZE = 5MB,
MAXSIZE = 15MB,
FILEGROWTH = 1MB )
GO
The CREATE DATABASE process is allocating 20.00 MB on disk 'FroggerData'.
The CREATE DATABASE process is allocating 5.00 MB on disk 'FroggerLog'.
Listing 2 shows how to create a database that spans multiple files for both the data and the log. Notice that the logs and data files use the suggested Microsoft extensions. The first data file should have an .MDF extension, and subsequent data files have the .NDF extension. Log files should use the .LDF extension. Again, the default SQL Server 2000 collation sequence is used.
Listing 2: Creating a Database That Spans Multiple Files
USE master
GO
CREATE DATABASE Leap ON PRIMARY
( NAME = LeapData1,
FILENAME =
'D:\Program Files\Microsoft SQL Server\MSSQL\Data\LeapData1.mdf',
SIZE = 5,
MAXSIZE = 20,
FILEGROWTH = 1 ),
( NAME = LeapData2,
FILENAME =
'D:\Program Files\Microsoft SQL Server\MSSQL\Data\LeapData2.ndf',
SIZE = 5,
MAXSIZE = 20,
FILEGROWTH = 5 )
LOG ON
( NAME = LeapLog1,
FILENAME =
'D:\Program Files\Microsoft SQL Server\MSSQL\Data\LeapLog1.ldf',
SIZE = 2,
MAXSIZE = 20,
FILEGROWTH = 1 ),
( NAME = LeapLog2,
FILENAME =
'D:\Program Files\Microsoft SQL Server\MSSQL\Data\LeapLog2.ldf',
SIZE = 2,
MAXSIZE = 10,
FILEGROWTH = 2 )
GO
The CREATE DATABASE process is allocating 5.00 MB on disk 'LeapData1'.
The CREATE DATABASE process is allocating 5.00 MB on disk 'LeapData2'.
The CREATE DATABASE process is allocating 2.00 MB on disk 'LeapLog1'.
The CREATE DATABASE process is allocating 2.00 MB on disk 'LeapLog2'.
When you specify the use of multiple data files, SQL Server automatically stripes information across all the data files specified. Striping can help reduce database contention and hotspots in your data. Note that SQL Server never stripes log files. The log files fill up with information sequentially, and when one log file is full, the data moves on to the next transaction log file.
TIP
If you aren't using RAID 5 (redundant array of inexpensive disks) or higher, it's strongly suggested that you place your transaction logs on separate physical hard disks. Setting them up this way allows for greater recoverability in the event of a hard disk failure. An additional benefit is that writes to the transaction log don't interfere with writes to the data files.
Listing 3 shows how to create a database that uses a collation sequence specified with the COLLATE command. In this case, you specify that SQL Server 2000 create a database that uses the Latin1 code page or code page 1251, dictionary sort order (General), case insensitive (CI), and accent insensitive (AI).
Listing 3: Creating a Database That Uses a Nondefault SQL Server 2000 Collation Sequence
USE master
GO
CREATE DATABASE Swim ON PRIMARY
( NAME = SwimData,
FILENAME =
'D:\Program Files\Microsoft SQL Server\MSSQL\Data\SwimData.mdf',
SIZE = 20MB,
MAXSIZE = 100MB,
FILEGROWTH = 10MB )
LOG ON
( NAME = SwimLog,
FILENAME =
'D:\Program Files\Microsoft SQL Server\MSSQL\Data\SwimLog.ldf',
SIZE = 5MB,
MAXSIZE = 15MB,
FILEGROWTH = 1MB )
COLLATE Latin1_General_CI_AI
GO
The CREATE DATABASE process is allocating 20.00 MB on disk 'SwimData'. The CREATE DATABASE process is allocating 5.00 MB on disk 'SwimLog'.