Bite size Educational Blurbs (Edurbs).
SSMS (SQL Server Management Studio) isn’t always available or efficient for the tasks that you have to do. Creating one database might be faster to do manually in SSMS; however, if you are rebuilding a system that has dozens of databases and hundreds of objects you would do it with code.
In Microsoft SQL Server that code is called T-SQL.
There are three primary types of commands in T-SQL:
- Data Definition Language [DDL]- for creating, changing (altering), and deleting database objects.
- Data Manipulation Language [DML]- for inserting, updating, and deleting data in tables.
- Data Query Language [DQL]- for selecting information
In Edurb 01 we created a database, deleted a database and renamed a database using SSMS GUI. Today we are going to do the same three commands – with T-SQL.
DEFINITION: T-SQL – Transact – Structured Query Language
Creating a Database with T-SQL
Just like in the previous lesson click the connect button to logon to your SQL Server Database Engine. In later sessions it will be assumed that you know how to logon to SQL Server and the steps won’t be shown. Of course, you can always come back to previous lessons if too much time has passed and you have forgotten how.
Remember, a database engine is what you installed from Microsoft, and that there can be many logical divisions of this database engine into databases.
The object explorer is shown above with the Databases node already expanded and you can see the list of databases. Next we have to open a query.
Another way is to right-click on any database and click on “New Query” as shown above.
There is a slight difference in the two procedures. The file menu method opens the query connected to the master database, which is a system database. When you right-click on a database and click “New Query” the query connected to the database that you right-clicked.
Since we are creating a database, deleting a database, and renaming a database it does not matter which database our query window is connected. However, you do not want to change anything in the master database.
CREATE DATABASE [TestingDB];
Above is the bare minimum T-SQL DDL (Data Definition Language) required to create a database. Type the above text in to the query window. The basic format of DDL is COMMAND objectname and a semi-colon to terminate the statement. The brackets are optional unless the identifier (the name) of the object doesn’t obey SQL Server’s rules for creating identifiers.
As you can see there are multiple ways to do any specific task in SQL Server. In addition, SQL Server is fairly robust in terms of default values. There are many settings that can be set at the time of database creation, but we are not specifying any of them. The defaults work well enough for the purposes of creating a database for learning and some other relatively non-production activities.
After running the above command you might look over at the object explorer and notice that you do not see your new database. Perhaps the command failed.
If you look at your query window you’ll see that after you executed the query you see the message “Command(s) completed successfully.” So, we know our command worked, or at least didn’t return any error codes.
Right click on the Databases node and click on refresh and you will see your new database.
Changing a Database Name with T-SQL
ALTER DATABASE [TestingDB] MODIFY NAME = [TestingDB2];
The above does the change of database name that we did in the first edurb. Type in the above code underneath the create database T-SQL. Select it and execute it. (see above if you do not remember how to execute T-SQL in the query window.
The above message circled in green means that we succeeded in changing the database name. NOTE: the object explorer on the left still reflects the old database name. How do you make it so you can see the new database name? (See above if you don’t remember)
Deleting a Database
DROP DATABASE [TestingDB2];
The above code deletes a database. In typical usage in production systems I do not recommend using this statement. In a development database or a sandbox database as long as you don’t care about the contents of the database you can drop it if you want.
That’s it for this Edurb or lesson. I hope it didn’t take too long to go through and you have learned one thing today.