Edurb 02 – Simple Database Create, Delete, and Rename with code

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:

  1. Data Definition Language [DDL]- for creating, changing (altering), and deleting database objects.
  2. Data Manipulation Language [DML]- for inserting, updating, and deleting data in tables.
  3. 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

If you recall from our previous session you use the 01 icon to start SSMS and arrive at this screen:

02Just 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.

04

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.

11Shown above is one way to open a query. Or –

12

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.

13

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.

14

Shown above the command typed in to the query.  You can select the code and press the  15 button, select the code and press F5, or select the code and press ctrl-e.

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.

16

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.

17

Right click on the Databases node and click on refresh and you will see your new database.

18

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.

19

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.

Edurb 01 – Simple Database Create, Delete, and Rename in SSMS

Today’s brief lesson (edurb? blured?) is on database manipulation in SQL Server.  This functionality has largely remained the same over the past few versions of Microsoft SQL Server.  We won’t be going through immense detail as these should be short 5 to 10 minute lessons.

The pre-requisite to this lesson is that you have Microsoft SQL Server Express Installed.  My previous post guides you to two locations for directions on how to do this.

If you have installed SQL Server properly you should have this icon available to you.01   It is the Microsoft SQL Server Management Studio (SSMS) for your resume.  Open SSMS up.

02

There should only be one database engine set up on your PC with the SQL Server Express Install and your Windows Login should let your login with administrator (‘sa’) rights. Click on the Connect button.

03

Once connecting to the database engine has been completed you should have a window called “Object Explorer”.

We’re not going to go into detail about each section at this moment and skip to the databases.  The database node contains all the databases that are attached to the database server.  A database is just a logical collection of objects that hopefully are aligned to a specific purpose or functionality.  Click on the plus sign next to databases.

04

There are a few things of note here.  First, there is another folder here called “System Databases” as we are approaching this from the point of view of a neophyte – please don’t touch this.  The system needs to use information and depends on the contents of the databases located in this folder.  The second thing to note is that disk symbol next to names – these are the customer databases.  You will most likely not have any listed here at this particular moment from a fresh install of SQL Server Express.

Creating a database through the SSMS GUI

05

Right click on the Databases node and click on “New Database…”.

06

For now, as we are still learning the very basics we will fill in the only required field “Database name:”.  Later on we’ll be using an example on fuel economy so I’m naming the database FuelEconomy.  After you have typed in FuelEconomy press the OK button.

07

There you go!  You have done well.  We have created our first database.  That wasn’t so hard, was it.

Deleting a Database through SSMS GUI

08

To delete a database through SSMS (SQL Server Management Studio) simply right-click on the database you want to delete and then click on the “Delete” menu item.

09

The above dialog box appears after you clicked on delete from the previous menu.  If you are the only user of your database engine and this database specifically, you can just press the OK button and the database will be deleted.  If there is a chance that someone else might be logged in to the database (or you yourself have an open session you are unaware of) check the box “Close existing connections” and then click on OK.

Renaming a Database with SSMS GUI

10

Simply left-click on the database name that you want to change and overtype the name you want it to be.  Of course, if you are the only user of a database this isn’t a problem; however, if there are applications and other database users that could potentially be using the database it would not be recommended to change the database name.

And that’s the end of our first Edurb. 🙂  Have fun and be safe.

Installing Microsoft SQL Server Express

The idea behind the programming and educational portion of this blog is to have very brief sessions that convey a lot of information.  Bite size, five minute, educational blurbs.  Edurbs?  Blureds?

The platform I work on is Microsoft Windows and Microsoft SQL Server.  The reasoning is that most people have Microsoft Windows and are familiar with this environment and Microsoft SQL Server Express is a free product and easy to work – except for the install.

But first…  You have to install the free version of Microsoft SQL Server.

Fortunately, many people have covered the install of Microsoft SQL Server:

This is a good install for SQL Server Express.

This is another good install site, with a lot of information about the limits of SQL Server.

I think our 5 minutes are up.  It will more than likely take more than 5 minutes to install SQL Server Express Edition.

Next time, I will go over how to look at the databases on a database server (your SQL Server Install), how to create databases, delete databases and how to detach a database and a brief explanation of why you would do those things!