Data Management

TechRepublic Tutorial: Creating new databases in SQL Server 2000

Learn how to create databases with SQL Server 2000

Before you create a database in SQL Server, you must come up with a design that fits the needs of your company. You should consider security, data integrity, permissions, database estimation, and maintenance. Let's examine these various design considerations and then look at the methods SQL Server offers for creating databases.

Database design key terms
Prior to creating a database, you should be familiar with the following terms:
  • Data represents the values stored in a database.
  • Tables are database objects that contain all the data in a database. Within a table, data is organized into rows and columns.
  • Null is defined as a missing or unknown value.
  • Indexes are objects that help speed up data retrieval. There are two types of indexes: clustered and nonclustered.
  • Constraints are conditions placed on data that help maintain data integrity. An example of a constraint would be the primary key. Adding a constraint helps keep all values in the primary key column unique.
  • Views look like database tables, but they're just virtual tables defined by a SELECT statement.
  • A primary key is a field that uniquely identifies a record within a table.
  • A foreign key is a field that establishes a relationship between two tables.
  • A one-to-one relationship is when a single record in one table is related to a single record in a second table.
  • A one-to-many relationship is when a single record in one table is related to one or more records in a second table.
  • A many-to-many relationship is when a single record in one table is related to one or more records in a second table and vice versa.

Designing databases
Before you create a database, you must first ask yourself the following questions:
  • Why am I creating a database?
  • Who are my Subject Matter Experts (SMEs)?
  • Who will access this database?
  • How will people access this database?

Once these questions are answered, you need to:
  • Define your objectives for creating this database. Your objectives should reflect the purpose of the database. It is a good idea to get your objectives on paper so you can refer to them if you ever lose focus on why you are creating this database.
  • Analyze your proposed database. This step might consist of talking to people to understand their needs and business requirements.
  • Create your tables and relationships. Use a modeling program to begin creating your tables, fields, primary, and foreign keys. Then, you can begin to create relationships between your tables and link them together.
  • Define your business rules. This process might consist of conducting more interviews to establish constraints on your database. The constraints will be directly based on the needs of the company.
  • Review your database. Review your database design, make sure it is correct, and go over the previous areas to make sure you've covered all the needs of the company.

Now that you have a basic knowledge of database design, let's look at some different methods for creating your database in SQL Server 2000.

Creating a database
With SQL Server 2000, you can create databases using one of these three methods:
  • The Create Database Wizard
  • The SQL Server Enterprise Manager console
  • Transact SQL (T-SQL)

Using the Create Database Wizard
If you're new to SQL Server, the best way to create a new database is to use the Create Database Wizard. Once you are comfortable with using SQL Server Enterprise Manager and/or T-SQL, you can move beyond this wizard to more advanced ways of creating databases.

To use the Create Database Wizard:
  1. Open the SQL Server Enterprise Manager from the Start menu.
  2. Click on the SQL Server instance where you want to create your new database and expand it (using the plus sign to the left).
  3. From the Tools menu, choose Wizards.
  4. Expand Database and highlight Create Database Wizard (Figure A) and click OK.

Figure A

  1. When the Welcome To The Create Database Wizard screen appears, click Next, type a database name, and browse to the location where you want to place your database file and transaction logs (Figure B).

Figure B

  1. Click Next and enter the initial size of your primary database (.mdf) file (Figure C). You can add secondary files as well; they will be given an .ndf extension.

Figure C

  1. Click Next and define how you want your database growth to occur (Figure D).

Figure D

  1. Click Next and enter the initial size of your transaction logs.
  2. Click Next and define your transaction log database growth.
  3. Click Next, review your database settings, and click Finish.

Manually create a database in Enterprise Manager
To manually create a database using SQL Server Enterprise Manager:
  1. Open Enterprise Manager and select and expand your SQL Server instance.
  2. Right-click on Databases and choose New Database.
  3. In the General tab, type a database name (Figure E).

Figure E

  1. In the Data Files tab, you can configure the auto growth size, database log location, and the secondary log location (Figure F).

Figure F

  1. In the Transaction Log tab, you can configure the auto growth size, database log location, and secondary location.
  2. Click OK, and your database is created.

Creating a database using T-SQL
In addition to the two methods mentioned above, you can use T-SQL to create a database. The syntax is as follows:
CREATE DATABASE database_name
[ ON
    [ < filespec > [ ,...n ] ]
[ COLLATE collation_name ]

< filespec > ::=
[ NAME =logical_file_name, ]
    FILENAME ='os_file_name'
    [ , SIZE =size ]
    [ , MAXSIZE = { max_size | UNLIMITED } ]

< filegroup > ::=
FILEGROUP filegroup_name < filespec > [ ,...n ]

Let’s say, for example, that we wanted to create a database that contains a primary data file called DDAT and one transaction log called DDAT_LOG. We would create this database using the T-SQL syntax shown in Figure G.

Figure G

Now, we will take it one step further and add a primary file, two secondary files, and two transaction logs (Figure H),

Figure H

Final word
In this article, we provided a list of key database terms and discussed basic database design considerations. We then worked through the process of creating a database using the three methods available in SQL Server 2000.

Editor's Picks