The Microsoft SQL Server Management Studio

The top section of the Microsoft SQL Server Management Studio displays the classic title bar of a regular window, with an icon on the left, followed by the title of the application, and the system buttons on the right side. Under the title bar, the menu bar displays categories of menus that you will use to perform the various necessary operations. A toolbar displays under the main menu.

The left side of the interface displays the Object Explorer window, with its title bar labeled Object Explorer. On the right side of the Object Explorer title, there are three buttons. If you click the first button that points down, a menu would appear, which allows you to specify whether you want the window to be floated, docked, or tabbed.

The Object Explorer is a dockable window, meaning you can move it from the left side to another side on the screen. To do this, you can click and drag its title bar to a location of your choice.

The Object Explorer is also floatable, which means you can place it somewhere in the middle of the interface:

To place the window back to its previous position, you can double-click its title bar.

The Object Explorer can also be tabbed. This means that the window can be positioned either vertically or horizontally.

The right side of the window is equipped by a window whose tab is labeled Summary. This area will be used to display either the contents of what is selected in the Object Explorer, or to show a result of some operation. As you will see later on, many other windows will occupy the right section but they will share the same area. To make each known, it will be represented with a tab and the tab shows the name (or caption) of a window.

After installing Microsoft SQL Server, you can use it to create and manage databases. To assist you with this, you can use Microsoft SQL Server Management Studio.

Microsoft SQL Server Management Studio is a window you will use to create and manage databases. To assist you with this, it comes equipped with various tools, some are graphically-based and some others require code from you. To perform some operations, you will use the main menu. Some other operations will be available from clicking one of the buttons on the toolbar. Many other operations will start from the Object Explorer.

The Object Explorer displays a list of items as a tree-style of window. One of the most regularly used items will be the name of the server you are using. If you are just starting to learn database development or you are a junior database developer, you may use or see only one server. In some cases, you may be dealing with many. Regardless, you should always know what server you are currently connecting to. This is easy to check  with the first node of the Object Explorer. In the following example, the server is named Central:

The name of the server is followed by parentheses.

In previous lessons, we saw that, to establish a connection to a server, you must authenticate yourself. In some cases you may use the same account over and over again. In some other cases you may have different accounts that you use for different scenarios, such as one account for database development, one account for database management, and/or one account for database testing. Some operations cannot be performed by some accounts. When performing some operations, you should always know what account you are using. You can check this in the parentheses of the server name. In the following connection, an account called Administrator is currently connecting to a server named Central:

When the server name is selected in the Object Explorer, the whole caption of its node displays in the Summary window.

For our lessons, probably the most regular node you will be interested in, is labeled Databases. This node holds the names of databases on the server you are connecting to. Also, from that node, you can perform almost any necessary operation of a database. To see most of the regularly available actions, you can expand the Databases node and some of its children. You can then right-click either Databases or one of its child nodes.

Although you will perform many of your database operations visually, some other operations will require that you write code. To assist with with this, Microsoft SQL Server provides a code editor and various code templates.

To open the editor:

• You can press Ctrl + N
• On the main menu, you can click File -> New -> Query With Current Connection
• On the Standard toolbar, you can click the New Query button
• In the Object Explorer, you can right-click the name of the server and click New Query

This would open a new window and position it on the right side of the interface. Whether you have already written code or not, you can save the document of the code editor at any time. To save it:

• You can press Ctrl + S
• On the main menu, you can click File -> Save SQLQueryX.sql...
• On the Standard toolbar, you can click the Save button

You will be required to provide a name for the file. After saving the file, its name would appear on the tab of the document.

Besides the SQL Server Management Studio, you can also work on Microsoft SQL Server from the DOS command prompt. This is done using an application or command named SQLCMD.EXE. To use it, open the Command Prompt, type SQLCMD (case-insensitive) and press Enter:

You can then write SQL code.

After establishing a connection, you can take actions, such as creating a database and/or manipulating data. The Structured Query Language, known as SQL, is a universal language used on various computer systems to create and manage databases.

SQL can be pronounced Sequel or S. Q. L. In our lessons, we will consider the Sequel pronunciation. For this reason, the abbreviation will always be considered as a word, which would result in “A SQL statement” instead of "An SQL statement". Also, we will regularly write, “The SQL” instead of “The SQL language, as the L already represents Language.

Like other non-platform specific languages such as C/C++, Pascal, or Java, the SQL you learn can be applied to various database systems. To adapt the SQL to Microsoft SQL Server, the company developed Transact-SQL as Microsoft's implementation of SQL. Transact-SQL is the language used internally by Microsoft SQL Server and MSDE. Although SQL Server highly adheres to the SQL standards, it has some internal details that may not be applied to other database systems like MySQL, Oracle, or even Microsoft Access, etc; although they too fairly conform to the standard.

The SQL we will learn and use here is Transact-SQL. In other words, we will assume that you are using Microsoft SQL Server as your platform for learning about databases. This means that, unless specified otherwise, most of the time, on this site, the word SQL refers to Transact-SQL or the way the language is implemented in Microsoft SQL Server.

As a computer language, the SQL is used to give instructions to an internal program called an interpreter. As we will learn in various sections, you must make sure you give precise instructions. SQL is not case-sensitive. This means that CREATE, create, and Create mean the same thing. It is a tradition to write SQL's own words in uppercase. This helps to distinguish SQL instructions with the words you use for your database.

As we will learn in this and the other remaining lessons, you use SQL by writing statements. To help you with this, Microsoft SQL Server provides a window, also referred to as the Query Window, that you can use to write your SQL code. To access it, on the left side of the window, you can right-click the name of the server and click New Query. In the same way, you can open as many instances as the New Query as you want.

When the Query window comes up, it display a blank child window in which you can write your code:

The code you write is a document and it can be saved as a file. The file would have the extension .sql. Every time you open a new query, it is represented with a tab. To switch from one code part to another, you can click its tab. To dismiss an instance of the query, first access it (by clicking its tab), then, on the right side, click the close button . If you had written code in the query window, when you close it, you would be asked to save your code. If you want to preserve your code, then save it. If you had already executed the code in the window (we will learn how to write and execute SQL code), you do not have to save the contents of the window.

If you are working in a Windows Application, you can write the exact same code you would in a query window. Once your code is ready, you can pass it to a SqlCommand object you would have created as we saw already. This would be done as follows:

Imports System.Data.SqlClient

Public Class Exercise

    Private Sub Exercise_Load(ByVal sender As Object, _
                              ByVal e As System.EventArgs) _
                              Handles Me.Load
        Dim connection As SqlConnection = _
        New SqlConnection("Data Source=(local);Integrated Security=yes")
        Dim command As SqlCommand = New SqlCommand(SQL Code, connection)
    End Sub
End Class

In this example, the SQL Code factor represents a SQL statement you would write and pass it as a string.

In the next sections and lessons, we will learn various techniques of creating SQL statements with code. By default, when a new query window appears, after writing a statement, you can execute it, either to make it active or simply to test it. To execute a statement:

• You can press F5
• On the main menu, you can click Query -> Execute
• On the SQL Editor toolbar, you can click the Execute button
• You can right-click somewhere in the code editor and click Execute

When you execute code, the query window becomes divided into two horizontal sections:

Also, when you execute code, the interpreter would first analyze it. If there is an error, it would display one or more red lines of text in its bottom section. Here is an example:

If there is no error in the code, what happens when you execute a statement depends on the code and the type of statement.

If you are working in a Windows Forms Application, after passing the SQL code to a command, to execute it, as we saw in the previous lesson, you can call the ExecuteNonQuery() method of your SqlCommand object. This would be done as follows:

Private Sub Exercise_Load(ByVal sender As Object, _
                          ByVal e As System.EventArgs) _
                          Handles Me.Load
    Dim connection As SqlConnection = _
    New SqlConnection("Data Source=(local);Integrated Security=yes")
    Dim command As SqlCommand = New SqlCommand(SQL Code, connection)

    connection.Open()
    command.ExecuteNonQuery()
    connection.Close()
End Sub

A comment is text that the SQL interpreter would not consider as code. As such, a comment is written any way you like. What ever it is made of would not be read. Transact-SQL supports two types of comments. The style of comment that starts with /* and ends with */ can be used. To apply it, start a line with /*, include any kind of text you like, on as many lines as you want. To close the commented section, type */. Here is an example of a line of comment:

/* First find out if the database we want to create exists already */

A comment can also be spread on more than one line, like a paragraph. Here is an example:

/* First find out if the MotorVehicleDivision database we 
   want to create exists already.
   If that database exists, we don't want it anymore. So,
   delete it from the system. */

Transact-SQL also supports the double-dash comment. This comment applies to only one line of text. To use it, start the line with --. Anything on the right side of -- is part of a comment and would not be considered as code. Here is an example:

-- =============================================
-- Database: MotorVehicleDivision
-- =============================================

/* First find out if the MotorVehicleDivision database we 
   want to create exists already.
   If that database exists, we don't want it anymore. So,
   delete it from the system. */


-- Now that the database is not in the system, create it

In SQL, after writing a statement, you can end it with a semi-colon. In fact, if you plan to use many statements in one block, you should end each with a semi-colon. When many statements are used, some of them must come after others.

To separate statements, that is, to indicate when a statement ends, you can use the GO keyword (in reality and based on SQL standards, it is the semi-colon that would be required, but the Microsoft SQL Server interpreter accepts GO as the end of a statement).

Before using a database, you must first have one. A database is primarily a group of computer files that each has a name and a location. Just as there are different ways to connect to a server, there are also different ways to create a database. You can create a new database in Microsoft SQL Server Management Studio, in Microsoft Visual Studio, or on the Command Prompt.

To create a database in Microsoft SQL Server Management Studio, you can right-click the Databases node and click New Database...

If you are working from Microsoft Visual Studio, to create a new database, in the Server Explorer, you can right-click Data Connections and click Create New SQL Server Database...

To programmatically create a database, pass the necessary SQL code as the command text of the SqlCommand object:

Private Sub Exercise_Load(ByVal sender As Object, _
                          ByVal e As System.EventArgs) _
                          Handles Me.Load
    Dim connection As SqlConnection = _
    	New SqlConnection("Data Source=(local);Integrated Security=yes")
    Dim command As SqlCommand = _
	New SqlCommand(Database Creation Code, connection)

    connection.Open()
    Command.ExecuteNonQuery()
    connection.Close()
End Sub

To create a database from the Command Prompt, open the DOS window and use the SQLCMD program. Then write code as we will learn next.

The command used to create a database in SQL uses the following formula:

CREATE DATABASE DatabaseName

The CREATE DATABASE (remember that SQL is not case-sensitive) expression is required. The DatabaseName factor is the name that the new database will carry.

A statement in SQL can be terminated with a semi-colon. Although this is a requirement in many implementations of SQL, in Microsoft SQL Server, you can omit the semi-colon. Otherwise, the above formula would be

CREATE DATABASE DatabaseName;

Instead of manually writing all of your code, the Microsoft SQL Server Management Studio provides a code template you can use and customize. To access the Template Explorer, on the main menu, you can click View -> Template Explorer. Before creating a database, open a new query window. Then:

• To create a new database using sample code, in the Template Explorer, expand the Databases node, then drag the Create Database node and drop it in the query window. The new database would be created in the server that holds the current connection
• If you have access to more than one server, to create a database in another server or using a different connection, in the Template Explorer, expand the Databases node, right-click Create Database and click Open. In the Connect to Database Engine dialog box, select the appropriate options, and can click OK

With any of these actions, Microsoft SQL Server would generate sample code for you:

-- =============================================
-- Create database template
-- =============================================
USE master
GO

-- Drop the database if it already exists
IF  EXISTS (
	SELECT name 
		FROM sys.databases 
		WHERE name = N'<Database_Name, sysname, Database_Name>'
)

CREATE DATABASE <Database_Name, sysname, Database_Name>
GO

You would then need to edit the code and execute it to create the database. If some sections of this code are not familiar to you, we will cover them in subsequent lessons

Probably the most important requirement of creating a database is to give it a name. The SQL is very flexible when it comes to names. In fact, it is very less restrictive than most other computer languages. Still, there are rules you must follow when naming the objects in your databases:

• A name can start with either a letter (a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p, q, r, s, t, u, v, w, x, y, z, A, B, C, D, E, F, G, H, I, J, K, L, M, N, O, P, Q, R, S, T, U, V, W, X, Y, or Z), a digit (0, 1, 2, 3, 4, 5, 6, 7, 8, or 9), an underscore (_) or a non-readable character. Examples are _n, act, %783, Second
• After the first character (letter, digit, underscore, or symbol), the name can have combinations of underscores, letters, digits, or symbols. Examples are _n24 or act_52_t
• A name can include spaces. Example are c0untries st@ts, govmnt (records), or gl0b# $urvey||

Because of the flexibility of SQL, it can be difficult to maintain names in a database. Based on this, there are conventions we will use for our objects. In fact, we will adopt the rules used in C/C++, C#, Pascal, Java, and Visual Basic, etc languages. In our databases:

• Unless stated otherwise (we will mention the exception, for example with variables, tables, etc), a name will start with either a letter (a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p, q, r, s, t, u, v, w, x, y, z, A, B, C, D, E, F, G, H, I, J, K, L, M, N, O, P, Q, R, S, T, U, V, W, X, Y, or Z) or an underscore
• After the first character, we will use any combination of letters, digits, or underscores
• A name will not start with two underscores
• If the name is a combination of words, at least the second word will start in uppercase. Examples are Countries Statistics, Global Survey, _RealSport, FullName, or DriversLicenseNumber

After creating an object whose name includes space, whenever you use that object, include its name between [ and ]. Examples are [Countries Statistics], [Global Survey], or [Date of Birth]. Even if you had created an object with a name that does not include space, when using that name, you can still include it in square brackets. Examples are [UnitedStations], [FullName], [DriversLicenseNumber], and [Country].

1. Start Microsoft Visual Basic and create a new Windows Application named DatabaseManagement
2. In the Server Explorer, right-click Data Connections and click Create New SQL Server Database...
3. In the Server Name of the Create New SQL Server Database, type (local)
4. In the New Database Name text box, type World Hunger Statistics
    
   
5. Click OK (if you receive an error that the database was not created, open the Services in the Administrative Tools, check that SQL Server (server name) and SQL Server (MSSQLSERVER) services had started)
6. Design the form as follows:
    

   Control Text Name Label Database Name:   TextBox   txtCreateDatabase Button Create btnCreateDatabase

7. Double-click the Create button
8. Implement the Click event of the button as follows:
    

   Imports System.Data.SqlClient Public Class Exercise Private Sub btnCreateDatabase_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) _ Handles btnCreateDatabase.Click ' Make sure the user enters the name of the database If txtCreateDatabase.Text.Length = 0 Then MsgBox("You must specify the name of " & _ "the database you want to create") Exit Sub End If Dim strConnection As String = _ "Data Source=(local);Integrated Security=yes" Using connection As SqlConnection = New SqlConnection(strConnection) Dim strDatabase As String = txtCreateDatabase.Text Dim cmd As SqlCommand = _ New SqlCommand("CREATE DATABASE [" & strDatabase & "];", _ connection) connection.Open() cmd.ExecuteNonQuery() MsgBox("A database named """ & _ txtCreateDatabase.Text & _ """ has been created on the " & _ connection.DataSource & " server.") txtCreateDatabase.Text = "" End Using End Sub End Class

9. Execute the application
10. In the Database text box, type Red Oak High School1
11. Click the Create button
     
    
12. Close the form and return to your programming environment

As you should be aware already from your experience on using computers, every file must have a path. The path is where the file is located in one of the drives of the computer. This allows the operating system to know where the file is, so that when you or another application calls it, the operating system would not be confused.

By default, when you create a new database, Microsoft SQL Server assumed that it would be located at Drive:C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data folder. If you use the New Database dialog box of the SQL Server Management Studio, if you specify the name of the database and click OK, the interpreter automatically creates a new file, and appends the .MDF extension to the file: this is the (main) primary data file of your database.

When originally creating a database, you may or may not know how many lists, files, or objects the project would have. Still, as a user of computer memory, the database must use a certain portion, at least in the beginning. The amount of space that a database is using is referred to as its size. If you use the New Database dialog box in the Microsoft SQL Server Management Studio, after specifying the name of the database and clicking OK, the interpreter automatically specifies that the database would primarily use 2MB. This is enough for a starting database. Of course, you can either change this default later on or you can increase it when necessary.

If you want to specify a size different from the default, if you are using the New Database to create your database, in the Database Files section and under the Initial Size column, change the size as you wish.

This series of lessons is intended for (junior) beginning database developers. The target is for those who are learning to create applications and design graphical database applications, not for those who manage databases. Therefore, in our lessons, unless stated otherwise and unless we have to, we will use the default settings of both Microsoft SQL Server and of a regular database. Microsoft SQL Server is so huge that it would be hard to cover both database development (database creation, graphical application design, functions, stored procedures, parameters, views, etc) and database administration (file locations, file sizes, backups, triggers, distribution, user access, client/server issues, etc) in these lessons.

Once a database exists on the server, to use it, as we saw in the previous lesson, you must first establish a connection to it. We saw that, to programmatically connection to a Microsoft SQL Server database, you could use a SqlConnection variable. In the connection string, to specify the database, assign its name to the Database attribute. Here is an example:

Private Sub Exercise_Load(ByVal sender As Object, _
                          ByVal e As System.EventArgs) _
                          Handles Me.Load
    Dim connection As SqlConnection = _
    New SqlConnection("Data Source=(local);" & _
                      "Database='Exercise';" & _
                      "Integrated Security=yes;")
End Sub

EXEC sp_renamedb 'ExistingName', 'NewName'

EXEC sp_renamedb 'RentalCars', 'BethesdaCarRental
GO

DROP DATABASE DatabaseName

-- =============================================
-- Create database template
-- =============================================
USE master
GO

-- Drop the database if it already exists
IF  EXISTS (
	SELECT name 
		FROM sys.databases 
		WHERE name = N'<Database_Name, sysname, Database_Name>'
)
DROP DATABASE <Database_Name, sysname, Database_Name>
GO

USE DatabaseName;

USE GovernmentStatistics;

Schema1.Something1