What Is Data Definition Language (DDL)?

Data Definition Language (DDL) comprises the SQL statements that are used to create, modify, and delete SQL-based databases and other SQL objects. DDL is used to define the properties of these objects at creation time, or to change the properties of any of these objects after they have been created. The database itself is considered just another object, and there can be many objects under or contained within the database. Some of these objects include

  • Tables

  • Views

  • Stored procedures

  • Triggers

  • Users

Creating a New Database

When you installed SQL Server, several databases were created automatically. One of these is the master database. The master database holds information about each database that is added to SQL Server. This information is stored in the sysdatabases table. Another database that is added at install time is the model database. The model database is used as the template whenever you add a new database to SQL Server. A copy of the model database is used and renamed to the new database you have just added. The current settings of the model database are used to initialize the new database. You then change these settings as needed.

Caution

The master database stores information about each database as well as the SQL Server environment itself. By making a backup of the master database before altering it, you can recover from any damage accidentally made to the master. Damage to the master database can affect other databases as well as keep the SQL Server from initializing. If this happens without a backup of the master database, you would have to reinstall SQL Server and then restore your database, if you even have a backup.


When you create a new database, you need the following information before starting:

  • Database name

  • Filename for the database and log file

  • Starting size of the database and log file

  • Maximum size of the database

  • Filegrowth for the database

The syntax of the CREATE DATABASE statement is as follows:

CREATE DATABASE database_name
[ On [PRIMARY]
[ <filespec> [,...n] ]
[, <filegroup> [,...n] ]
]
[ LOG ON [ <filespec> ] ]
[ FOR LOAD | FOR ATTACH ]

<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]

The arguments of the preceding syntax are described in Table 8.1.

Table 8.1. Definition of the CREATE DATABASE Arguments
ArgumentExplanation
database_nameThe friendly name for the new database as it will appear in SQL Server.
logical_file_nameThe logical or physical name used for the database file. It must be a unique name in the database.
os_file_nameActual location where the database will be stored on the computer and accessible by SQL Server.
SizeThe size of the database file to be created. KB or MB can be used to specify the size. MB is the default increment. Only whole numbers are allowed and the minimum value is 512KB. The default size is 1MB.
max_sizeThe maximum size to which the defined file can grow. This is also specified in either KB or MB. If the max_size is not specified or if it is set to UNLIMITED, the defined file will grow until there is no more free space available on the hard drive.
growth_incrementThe amount of space added to the file each time it needs to be expanded.
FOR LOADFor backward compatibility with versions of Microsoft SQL Server before 7.0. The database is created with the dbo use only database choice on, and the status set to loading.
FOR ATTACHIndicates that a database is attached from an existing set of operating system files. You must supply a <filespec> entry specifying the first primary file. You must also supply a <filespec> for each file with a different path than the path used when the database was first created or last attached.

Now that you have seen the syntax used to create a database, let's use it by actually creating a database. Listing 8.1 is an example of the CREATE DATABASE statement.

Code Listing 8.1. DDL Used to Create a Car Parts Database
 1: USE master
 2: GO
 3: CREATE DATABASE Parts
 4: ON
 5: (NAME = parts_dat,
 6:  FILENAME = 'c: SQL_Data partsdat.mdf',
 7:  SIZE = 70MB,
 8:  MAXSIZE = 500MB,
 9:  FILEGROWTH = 10MB )
10: LOG ON
11: (NAME = parts_log,
12:  FILENAME = 'c: SQL_Data partslog.log',
13:  SIZE = 25MB,
14:  MAXSIZE = 150MB,
15:  FILEGROWTH = 5MB )
16: GO

Caution

The directory path used in Listing 8.1 refers to the directory SQL DATA on the C drive. You must create this directory on your computer for the SQL procedure to execute properly.


This example will create a database named Parts using the file c: SQL_Data partsdat.mdf with an initial file size of 70MB. It can grow as large as 500MB by 10MB at a time. The log file for this database is stored in c: SQL_Data partsdat.log, is 25MB in size, has a maximum size of 150MB, and its growth size is set to 5MB. When you execute this statement in Query Analyzer, you should get the following output:

The CREATE DATABASE process is allocating 70.00 MB on disk 'parts_dat'.
The CREATE DATABASE process is allocating 25.00 MB on disk 'parts_log'.

To check the information for any of the databases in SQL Server, you can run the system stored procedure SP_HELPDB. In Query Analyzer, type in the command and execute it. The following type of results for the databases that are on your server:

name       db_size     owner    dbid   created     status
-------- ------------- -------- ------ ----------- --------------------
master        10.94 MB sa       1      Nov 13 1998 trunc. log on chkpt.
model          1.50 MB sa       3      Sep  3 1999 no options set
msdb          16.25 MB sa       4      Sep  3 1999 trunc. log on chkpt.
Northwind      3.94 MB sa       6      Sep  3 1999 select into/bulkcopy
                                                      , trunc. log on chkpt.
Parts         95.00 MB sa       9      Aug  7 2000 no options set
pubs           1.74 MB sa       5      Sep  3 1999 trunc. log on chkpt.
tempdb        11.44 MB sa       2      Aug  2 2000 select into/bulkcopy
                                                      , trunc. log on chkpt.

You should know that the db_size column shows the size of each database by combining the size of the data and log files. Also, notice that the new database you created, Parts, has no options set in the status column. These options are not set during the creation process, but can be set by using this stored procedure:

Sp_dboption

In addition, because the Model database is used as the template when you create a new database, if you want an option to be used by default, you should set it in the Model database.

Altering a Database

In any design, you can never anticipate everything that might be needed in a database. Fortunately, there is a way to change, or alter, the database after it has been created. The command ALTER DATABASE can be used to add files and filegroups and to change the name or size of existent database files. It can also be used to remove files and filegroups from the database. The ALTER DATABASE syntax is

ALTER DATABASE database_name
{    ADD FILE <filespec> [,...n] [TO FILEGROUP filegroup_name]
    | ADD LOG FILE <filespec> [,...n]
    | REMOVE FILE logical_file_name
    | ADD FILEGROUP filegroup_name
    | REMOVE FILEGROUP filegroup_name
    | MODIFY FILE <filespec>
    | MODIFY FILEGROUP filegroup_name filegroup_property}

<filespec> ::=
(NAME = logical_file_name
  [, FILENAME = 'os_file_name']
  [, SIZE = size]
  [, MAXSIZE =  ( max_size | UNLIMITED ) ]
  [, FILEGROWTH = growth_increment] )

The arguments for this command are described in Table 8.2.

Table 8.2. Definition of ALTER DATABASE Command Arguments
ArgumentExplanation
database_nameName of the database you are altering.
ADD FILESpecifies that you want to add a file.
logical_file_nameThe logical name used for the file. It must be unique in the database.
os_file_nameThe actual path and filename of the database.
sizeWith ADD FILE, size is the initial size for the file.
max_sizeThe maximum size to which the new file can grow.
growth_incrementSpecifies the growth increment of the defined file.
filegroup_nameName of the existing filegroup to add the specified file.
ADD LOG FILEAdd a log file to this database with the <filespec> info given.
REMOVE FILERemoves the file information from the database system tables and deletes the physical file. The file must be empty first.
filegroup_nameWith ADD FILEGROUP and REMOVE FILEGROUP, the name of the filegroup to add or remove.
MODIFY FILEModifies the given file settings for FILENAME, SIZE, FILEGROWTH, and MAXSIZE. Only one of these properties can be changed at a time.
MODIFY FILEGROUPSets the given filegroup name
filegroup_namewith the given filegroup property.
filegroup_propertySettings for filegroup_property are READONLY, READWRITE, and DEFAULT.

Let's look at a couple of different ALTER DATABASE examples. Listings 8.2 and 8.3 will show you how this command can be used. Using the new Parts database from the previous CREATE DATABASE command, you can increase the data file size from 70MB to 100MB.

Code Listing 8.2. Increasing the Size of a File in an Existing Database
1: Use Master
2: GO
3: ALTER DATABASE Parts
4: MODIFY FILE
5:  (NAME = 'parts_dat',
6:  SIZE = 100MB)
7: GO

When you execute this command, SQL Server will display the following output:

The command completed successfully.

You can use the sp_helpdb command to verify that the database size has been changed. Listing 8.3 shows how you can add a secondary file to the existing filegroup in the Parts database.

Code Listing 8.3. Adding a File to an Existing Database
 1: Use Master
 2: GO
 3: ALTER DATABASE Parts
 4: ADD FILE
 5:  (NAME = 'parts2_dat',
 6:  FILENAME = 'c: SQL_DATA parts2dat.ndf',
 7:  SIZE = 50MB,
 8:  MAXSIZE = 100MB,
 9:  FILEGROWTH = 5MB )
10: GO

SQL Server will display the following message after executing the preceding command.

Extending database by 50.00 MB on disk 'parts2_dat'.

Tip

The stored procedure sp_renamedb can be used to change the name of the database.


Dropping or Deleting a Database

Deleting or dropping a database is almost too easy. Before you start dropping databases, you should make sure that you have a backup of all your databases, including the master and model. SQL Server will not prompt you with the ever popular Are you sure you want to do this? message to stop you from making a potentially huge error. You would use the following syntax for the DROP DATABASE command:

DROP DATABASE database_name[,   n]

To drop the Parts database, you would use the following example:

DROP DATABASE Parts

The result of dropping the database you created in the previous sections would look like this:

Deleting database file 'c: SQL_data partsdat.mdf'.
Deleting database file 'c:  SQL_data  partslog.log'.
Deleting database file 'c:  SQL_data  parts2dat.ndf'.

If you need to delete more than one database, you can just type the database names separated by commas as shown:

DROP DATABASE Parts, Orders, Customers

DDL Versus the Enterprise Manager

You're probably thinking, "Why should I bother using the actual statements when I can just use the Enterprise Manager and do it graphically?" (see Figure 8.1). As a rule, you should use the Enterprise Manager whenever you can. However, from a development standpoint, you'll be glad that you know how to use the DDL statements.

Figure 8.1. Using the SQL Server Enterprise Manager.


Suppose that you have to set up two databases. Each database has 150 tables, and each table has several columns. That would be a lot of clicking in Enterprise Manager. If you're a developer, think about all the users of your software having to set up these objects. Having the code to generate objects on the SQL Server can save a lot of time and headaches for you and for your users. You can write programs that automatically build the entire database for new users. You can use code with loops that go through the same generic subroutines for adding certain objects. Then you just change the variables each time the loop is passed.

Knowing the Enterprise Manager is great. It's quick and it's easy. But knowing the code to create and alter the objects can save you a lot of redundant clicking and can get you out of some messy situations.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset