Creating Databases Without Using Transact-SQL

You can create a new database by using Object Explorer or the Transact-SQL language. As the name suggests, you also use Object Explorer to explore the objects within a server. From the Object Explorer pane, you can inspect all the objects within a server and manage your server and databases. The existing tree contains, among other folders, the Databases folder. This folder has several subfolders, including one for the system databases and one for each new database that is created by a user.

To create a database using Object Explorer, right-click Databases and select New Database. In the New Database dialog box (see Figure 3-5), type the name of the new database in the Database Name field and then click OK. Each database has several different properties, such as file type, initial size, and so on. Database properties can be selected from the left pane of the New Database dialog box. There are several different pages (property groups):

General
Files
Filegroups
Options
Change Tracking
Permissions
Extended Properties
Mirroring
Transaction Log Shipping

Note – For an existing database, the system displays all property groups in the preceding list. For a new database, as shown in Figure 3-5, there are only three groups: General, Options, and Filegroups.

The General page of the Database Properties dialog box (see Figure 3-6) displays, among other things, the database name, the owner of the database and its collation. The properties of the data files that belong to a particular database are listed in the Files page and comprise the name and initial size of the file, where the database will be stored, and the type of the file (PRIMARY, for instance). A database can be stored in multiple files.

Figure 3-6 – Database Properties dialog box: General page

Note – SQL Server has dynamic disk space management. This means that databases can be set up to automatically expand and shrink as needed. If you want to change the Autogrowth property in the Files page, click the ellipses (…) in the Autogrowth column and make your changes in the Change Autogrowth dialog box. The Enable Autogrowth check box should be checked to allow the database to autogrow. Each time there is insufficient space within the file when data is added to the database, the server will request the additional space from the operating system. The amount (in megabytes) of the additional space is set by the number in the File Growth frame of the same dialog box. You can also decide whether the file can grow without any restrictions(the default value) or not. If you restrict the file growth, you have to specify the maximum file size.

The Filegroups page of the Database Properties dialog box displays the name(s) of the filegroup(s) to which the database file belongs, the art of the filegroup (default or nondefault), and the allowed operation on the filegroup (read/write or read-only).

The Options page of the Database Properties dialog box enables you to display and modify all database-level options. There are several groups of options: Automatic, Containment, Cursor, Miscellaneous, Recovery, Service Broker, and State. For instance, the following four options exist for State:

Database Read-Only – Allows read-only access to the database. This prohibits users from modifying any data. (The default value is False.)
Database State – Describes the state of the database. (The default value is Normal.)
Restrict Access – Restricts the use of the database to one user at a time. (The default value is MULTI_USER.)
Encryption Enabled – Controls the database encryption state. (The default value is False.)

The Extended Properties page displays additional properties of the current database. Existing properties can be deleted and new properties can be added from this dialog box.

If you choose the Permissions page, the system opens the corresponding dialog box and displays all users and roles along with their permissions.

The rest of the pages (Change Tracking, Mirroring, and Transaction Log Shipping) describe the features which are related to data availability and are therefore explained in detail in coming chapters.

Modifying Databases Without Using Transact-SQL

Object Explorer can also be used to modify an existing database. Using this component, you can modify files and filegroups that belong to the database. To add new data files, right-click the database name and choose Properties. In the Database Properties dialog box, select Files, click Add, and type the name of the new file. You can also add a (secondary) filegroup for the database by selecting Filegroups and clicking Add.

Note – Only the system administrator or the database owner can modify the database properties just mentioned.

To delete a database using Object Explorer, right-click the database name and choose Delete.

Modifying Databases Without Using Transact-SQL

Related Lessons: