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.

Programming and Ignorance

Ignorance does not have to be good or bad.  It is.  No matter how much any single individual knows they are ignorant on some other topics.

In other words, there are no Renaissance men anymore.

Even within our chosen professions we can all suffer from ignorance.  Many years ago I was at a company and they asked a programmer to sort data that was retrieved from SQL Server.

Everyone connected back up sometime later to find out what was taking so long.  The programmer was writing a bubble sort and said it would be some more time before it could be finished.

We all had a big laugh and then taught the programmer the ORDER BY clause which sorts output from a query.

I had been programming in T-SQL under Microsoft SQL Server for many years before I decided to get a certification for Querying Microsoft SQL Server 2012.  I began to study and there were queries of mine in the past that I had always wondered why they didn’t work.

Queries using an expression twice in the select clause or using an aliased column name in the where clause.

It all came down to ignorance.  The order you write a SQL statement is different than the order it is processed.

Select Dept, Count(*) as CNT
From Employees
Where Sex = ‘F’
Group By Dept, Country
Having Country = ‘USA’
Order by CNT;

The above query is most frequently written top down just as you see it.  However; when it is process by SQL Server it is processed in this order:

  1. FROM
  2. WHERE
  3. GROUP BY
  4. HAVING
  5. SELECT
  6. ORDER BY

So, using the alias as above works since the ORDER BY clause is processed after the SELECT clause.  The below query fails because the WHERE clause contains an alias created in the SELECT clause, but the SELECT clause has not been processed yet.

Select Dept, Count(*) as CNT
From Employees
Where CNT > 1000
Group By Dept, Country
Having Country = ‘USA’
Order by CNT;

In addition, SQL Server processes clauses ‘all at once’ so you can’t even use the CNT alias in another part of the SELECT clause.  This type of error is shown below with the CNT + 1 in the SELECT clause.

Select Dept, Count(*) as CNT, CNT +1
From Employees
Where Sex = ‘F’
Group By Dept, Country
Having Country = ‘USA’
Order by CNT;