Most of the articles that I presented so far for database development have been centered around the exact Microsoft T-SQL syntax to perform a certain operation. At the University of Rhode Island, I learnt how to program in Pascal before typing in my first ANSI SQL statement. In short, I will always be some type of developer in my heart.
However, many people do not come from a computer science and math background. The SQL Server Management Studio (SSMS) has menus and dialog boxes to achieve many of the same results.
Today, I am going to start a series of articles that will recreate the [AUTOS] sample database while demonstrating how common T-SQL tasks in my script can be done with SSMS. I will be going over sections 1A to 1D in the sample code.
I want to explore how the CREATE DATABASE, ALTER DATABASE, and DROP DATABASE data definition language (DDL) constructs can be achieved with SSMS.
My development version of SQL Server already contains the [AUTOS] sample database seen below.
The first task is to drop the [AUTOS] database. Find the object explorer and right click the [AUTOS] database. Select the delete option off the sub-menu as seen below.
This action will bring up the delete object window. Choose the delete backup/restore history check box to remove maintenance information from the [MSDB] database. Checking the close existing connections check box makes sure we have the database all to ourselves. Clicking the OK button will cause the database to be dropped.
The second task is to create a new database with the same properties that were defined in the TSQL script. Right click the databases parent node in the object explorer tree. Choose the new database menu option.
Here comes the hardest part of this task, filling in all the items on this form with the information below.
- Database name = [AUTOS]
- Database owner = [sa]
- Logical Data File = AUTOS_DAT
- Physical Data File = C:\MSSQL\DATA\AUTOS-DAT.MDF
- File Size, Growth & Max = 16 MB, 4 MB, UNLIMITED
- Logical Log File = AUTOS_LOG
- Physical Log File = C:\MSSQL\LOG\AUTOS-LOG.LDF
- File Size, Growth & Max = 4 MB, 1 MB, UNLIMITED
Make sure the information is correctly entered as below. Clicking the OK button will cause the database to be created.
Most people including database administrators are not perfect. How do we change the database characteristics after creation?
The third task is to alter the database. This can be done by selecting the [AUTOS] database in the object explorer, right click and select the properties menu item.
There are nine different sub-pages that can be viewed. I will review the first four or commonly used ones today.
The general page has information about the last time a full or log backup was taken which are very important weekly and/or daily tasks. Database ownership and collation information is also shown. The current file allocated size versus used size is displayed. All information on this page is read only!
The filegroups page has one entry for the default PRIMARY file group. We want to add two more groups for active and staging data.
- File Group 2 = FG_AUTOS_ACTIVE
- File Group 3 = FG_AUTOS_STAGE
Click the add button and enter in the above information. Clicking the OK button will cause the file groups to be created.
The files page has the same information that was used to create the database. Unlike the general page, information can be changed on this page. Please add the following files attached to the new file groups.
- Logical Data File = FN_AUTOS_ACTIVE
- Physical Data File = C:\MSSQL\DATA\AUTOS-ACT.NDF
- File Size, Growth & Max = 16 MB, 4 MB, UNLIMITED
- Logical File Group = FG_AUTOS_ACTIVE
- Logical Data File = FN_AUTOS_STAGE
- Physical Data File = C:\MSSQL\DATA\AUTOS-STG.NDF
- File Size, Growth & Max = 16 MB, 4 MB, UNLIMITED
- Logical File Group = FG_AUTOS_STAGE
Click the add button and enter in the above information to create the files. Clicking the OK button executes the action or clicking the CANCEL button abandons the action.
A cool thing about this SSMS is that the script button will allow you to take the form entry and generate the correct T-SQL script.
The options page allows the database administrator to set various properties which have direct effect on the database.
For instance, changing the read only state to TRUE causes all INSERT, UPDATE, and DELETE statements to fail. Changing the restrict access to RESTRICTED USERS allows only database owners (db_owner) or system administrators (dbcreator or sysadmin) to access the database.
I think two most important settings are the recovery model and the compatibility setting. Choosing a correct recovery model is important to your disaster recovery plan. The compatibility setting is used for those pesky applications that are not compliant with your SQL Server version.
In summary, we can do many of the Data Definition Language (DDL) tasks that can be done in T-SQL. The cool script button shows you what SQL Server Management Studio (SSMS) will use for code to accomplish a particular task. Next time, I will be talking about designing tables with SSMS.
