T-SQL For Starters: Creating SQL Server 6.5 Databases

Several steps are necessary before you can create SQL Server tables. I covered selection of data types last month. This month, I look at another building block: creating the databases (and in SQL Server 6.5, the devices) to hold the tables. SQL Server 6.5 and 7.0 differ greatly in the way they store data; consequently, the way you construct databases in each is different. I'll address the two versions separately and point out some differences. Let's examine how to create, expand, and remove databases and devices in SQL Server 6.5. As usual, I concentrate on using the Transact SQL (T-SQL) syntax for creating objects. You can create your devices and databases entirely from within the graphical interface of SQL Server Enterprise Manager, but it helps to be familiar with the basic syntax and to understand what the GUI is doing for you behind the scenes.

Devices

In SQL Server 6.5, the first step in database creation is creating a device. This term goes back to the old days of SQL Server when you had to dedicate an entire hard disk to a database. More recent releases don't require an entire disk, but they require that you allocate disk space for the database or databases to use. This pre-allocated space is a disk file, with the default extension .DAT. A device can contain more than one database, and a database can span multiple devices. Because a device is a disk file, it can't cross from one disk to another. So if you have a very large database, you might have to create several devices to get enough space for your database.

SQL Server builds devices on logical disk drives, not physical drives. Thus, a RAID array appears as one logical disk drive, although it consists of multiple physical drives. In that case, you can spread the device across all the physical drives.

Creating Devices. To create a device, from the ISQL/w window or from the Enterprise Manager query window, use the DISK INIT command. The syntax is

DISK INIT    NAME = 'logical_name',   PHYSNAME = 'physical_name',   VDEVNO = virtual_device_number,   SIZE = number_of_2KB_blocks[, VSTART = virtual_address]

The device must have a logical name by which you can refer to it later. You also need to supply a physical name for the file, which includes the drive and directory path. The directory must already exist—the DISK INIT won't create the directory for you. The vdevno, or virtual device number, is a number from 0 to 255, which uniquely identifies this device. Vdevno 0 is already in use for the Master device which contains the system databases (Master, Model, and Tempdb). Numbers 126 and 127 are in use for the devices that hold the msdb database and its transaction log. (See Michael Otey, SQL Seven, "System-Supplied Databases," May 1999 for information on SQL Server databases.) You don't have to assign sequential numbers as you add devices, but doing so is easier.

How do you find out which virtual device numbers are in use? The sp_helpdevice stored procedure lists them; just run it from the query window. The output listing isn't well formatted, so you'll have to scroll all the way to the right to see the device numbers.

SQL Server 6.5 requires that you supply the size of the device, expressed in 2KB blocks. A megabyte has 512 2KB blocks, so just multiply the desired size in megabytes by 512. Thus, a 10MB database is 5120 blocks, a 100MB database is 51200 blocks, etc.

The vstart parameter is the starting virtual address, or starting offset, expressed in 2KB blocks. Leave this parameter at the default of zero, or just omit it. It's there only for backward compatibility.

Suppose you want to build a 100MB database to hold budget and planning data, with a transaction log of 20MB. Use the code in Screen 1 to build the devices.

Expanding Devices. If your device runs out of room and the same disk has enough space, you can expand the device. (If you don't have enough space, add another device on a different disk.) To expand a device, use the DISK RESIZE statement. The syntax is

DISK RESIZE    NAME = logical_device_name,    SIZE = final_size 

You don't have to give the physical name, just the logical name. Be careful to give the total final size in 2KB blocks. Be aware that you can never shrink a device. You can't reduce the size of the device from the original size; you can't even remove additional space after you add it.

Using Default Devices. If a device is a default device, you can use it for any database for which the database creator doesn't specify a device. You can designate any device as a default device, and you can have multiple default devices. If someone creates a database without specifying a device, SQL Server uses the default devices in alphabetical order. This rule can lead to some odd results. Suppose that you have three default devices, DEVA, DEVB, and DEVC. DEVA and DEVB are each 100MB, and DEVC is 300MB. You create a 250MB database and don't specify where you want it, or you use the ON DEFAULT option. SQL Server uses 100MB on DEVA, then 100MB on DEVB, then 50MB on DEVC. Sure, DEVC has enough space for the whole database, but if that's what you want, you have to say so. SQL Server won't depart from alphabetical order.

To find out which devices are defaults, run sp_helpdevice. You'll find that under the description column, Master has the entry disk default. In other words, the default default-device is your master device. This default isn't a good idea, because the master device probably doesn't have much space left. Even if you allocate more than the suggested 25MB during installation, keep Master free in case you need to expand the system databases.

You can run the stored procedure sp_diskdefault to prevent a device from being a default device. The syntax is

sp_diskdefault device_name, {defaulton | defaultoff}

So run

Exec sp_diskdefault master, defaultoff

to make sure that the master device isn't used accidentally for another database.

Usually, a good practice is to assign a database to a specific device, but sometimes a default device is useful. If you have a group of developers or testers who need to create and drop databases repeatedly, you might build a 1GB device and make it the default. Then the testers can create and drop databases without worrying about where the files are. If you need to move the files to a different disk, just change the default device, and as the testers drop and recreate databases, the files will migrate to the new disk. (This approach also means that the testers don't need authorization to create devices, just to create databases.)

Databases

In SQL Server 6.5, you need to create every database on at least one device. Any database (except the Master system database) can span multiple devices. A good idea is to place the transaction log on a separate device, to ensure recoverability and reduce contention.

Creating a Database. The T-SQL syntax to create a database is:

CREATE DATABASE database_name[ON {DEFAULT | database_device} [= size]   [, database_device [= size]]...][LOG ON database_device [= size]   [, database_device [= size]]...][FOR LOAD]

Don't put spaces in database names, and avoid non-alphanumeric characters (the only other characters that T-SQL recognizes are #, $, and _). You can create an annual budget database by simply stating:

CREATE DATABASE budget

This simple statement creates a database of the default size on the default device, which isn't a good idea. Unless you change the default device, it's the Master device, and it probably doesn't have much room left. Also, the default size of a new database is only 2MB. You can change that value in the server settings, but usually you won't need to. It's much better to specify on which devices you want to place each database (and its log). For example,

CREATE DATABASE budget ON budgetdata = 100 LOG ON budgetlog = 20

creates a 100MB database called budget on the budgetdata device and places the transaction log for the database on the budgetlog device. Notice that for databases, you must specify the size in MB, not 2KB blocks. If your database is big enough to require several devices, you can just add more devices to the statement:

CREATE DATABASE budget ON budgetdata = 100, budgetdata1 = 100,    budgetdata2 = 100 LOG ON budgetlog = 20

If you leave off the LOG ON, the log will be on the same device as the data. You don't need to place the log on a separate device, but doing so is a good idea. Also, put the log device on a different physical disk. Then if one disk crashes, you have the database, or the log plus a recent backup, intact—in either case, you can recover.

There are also performance issues: Log reads and writes are sequential, but database reads and writes are scattered randomly across the disk. So if the log and the data are on the same device, they compete for space. As the data grows, it crowds out the log, and eventually the log has so little space that it fills up rapidly.

The FOR LOAD option states that you are creating the database with the intent of loading data into it. Also, SQL Server creates the database with the dbo use only option set, so users can't get in and make changes unless you change this option.

Expanding Your Database. If necessary, you can add space to your database. The additional space can be on the same device, or on another device. To expand a device, use the ALTER DATABASE statement. The syntax is

ALTER DATABASE database_name[ON {DEFAULT | database_device} [= size] [, database_device [= size]]...][FOR LOAD]

If you originally created the budget database on device budgetdata and now want to expand it onto budgetdata1 and budgetdata2, enter the ALTER DATABASE statement you see in Screen 2. The potential trap here is that you must specify the incremental space to add. For devices, use 2KB blocks and final size. For databases, use megabytes and increments.

Moving the Log. What if you already have a database and its log on the same device? No problem: Build a device for the log. Use the ALTER DATABASE statement, and immediately run the stored procedure sp_logdevice. This procedure marks the new space as being dedicated to the log. The process takes a while, but after your log fills the eight-page extent it's currently writing to, it will jump to the new device. Then after your next transaction log backup, the log will disappear from the original device. So if you built the budget database on the budgetdata device and want to move the log, you run

ALTER DATABASE budget ON budgetlog = 20GOsp_logdevice budget, budgetlog

Removing Databases and Devices

Sometimes you need to get rid of test databases, or you might have moved a database to another server and now want to reclaim the space. The quickest way to remove a database is with the DROP DATABASE statement. The syntax is simple:

DROP DATABASE budget

You can drop several databases at the same time by listing them, separated by commas. SQL Server doesn't let you drop databases that are in use, including those the replication process is using.

Removing a device requires a stored procedure, rather than a T-SQL statement. To get rid of the budgetdata device, for example, you run

sp_dropdevice budgetdata, [delfile]

The optional delfile deletes the .DAT file and recovers the disk space. When you use Enterprise Manager to delete a device, the file remains in place. This approach not only clutters the disk, but it keeps you from recreating the device with the same physical file name because the file already exists.

Permissions

To create or drop a device, you must be logged on as a system administrator. The system administrator can grant other users the right to create databases. The person who creates the database is the database owner (dbo) for that database. Typically, in SQL Server 6.5, developers are all aliased into the database as dbo, so that the objects they create are jointly owned by all, and not by each programmer. Anyone who owns a database has permission to drop it.

Behind the Scenes

When you create a device, SQL Server places an entry in the Master database's sysdevices table. When you create a database, SQL Server lists the database in sysdatabases. As I mentioned, each device can contain multiple databases, and each database might reside on multiple devices. This is a classic many-to-many relationship, so it needs a third, join, table to show the relationship between devices and databases. This table is sysusages. Always back up the Master database immediately after creating or dropping devices and databases. If you have to restore from an older backup, the Master database won't include your new databases, or it might look for a database you just deleted.

Creating a database in SQL Server 6.5 is no trivial task. And there's more. Next month, I'll look at SQL Server 7.0, which doesn't use devices. However, it introduces the concept of files and filegroups, so don't plan on your life becoming simpler any time soon.