Intro to SSMS and T-SQL

Microsoft's Database Software

SQL Server is the primary database product maintained by Microsoft. "SQL" standards for "Structured Query Language". SQL (pronounced "S-Q-L" or "sequel") is actually an international standard database language maintained by organizations such as the ISO. "SQL" is the generic name for the language used to work with and program the database. SQL Server is not the only SQL database. MySQL, Oracle, Postgres, Firebird and others are SQL databases. Most of these databases have their own extensions and special keywords in their product – but they also support the basic ISO definition of an SQL database.

Learn more here: https://en.wikipedia.org/wiki/SQL (opens in a new tab)

There have been many versions of SQL Server. The most current version is SQL Server 2017. Versions 2005, 2008, 2010, 2012, 2014, 2016 are still in use by many organizations.

SMS: SQL Server Management Studio

The way we interact with SQL Server, as developers, is through an Interactive Development Environment (IDE), named SQL Server Management Studio (SSMS).

An IDE is an application that helps you create, compile, test and deploy software or other types of code. An IDE provides a User Interface (UI) to assist with editing and other tasks.

Read more here: https://en.wikipedia.org/wiki/Integrated_development_environment (opens in a new tab)

SSMS is the IDE for SQL Server. SSMS is NOT the database. The database is a set of specialized files and programs to store and present data from the database tables. SSMS is an IDE that simplifies how developers interact with the database. There are other IDE’s that are capable of interacting with SQL Server. SSMS is Microsoft’s product and the most popular IDE for SQL Server.

Starting SSMS and Connecting to a Database

On Windows, SSMS is usually found under the Start Menu, under Microsoft SQL Server <version>, such as Microsoft SQL Server Management Studio 2017.

After starting SSMS, you must login to a specific SQL Server database before continuing. The initial Connect to Server dialog window looks like this:

Connect to SSMS

Figure 1: Connect to Server Dialog Window

  1. Enter the Server name (or URL). Your instructor will provide it.
  2. [If a database is on your local machine, you are able to use Windows Authentication to access it.]
  3. Enter the Login and Password. Your instructor will the credentials.
  4. Do NOT mark the [Remember Password] option on public computers, such as in a classroom
  5. Click [Connect]

Once connected, the user interface (UI) may only display an Object Explorer panel on the left side. This is a view of the server objects. Click the + next to Databases and then the + next to the database name to expand and display the database objects. In this example, “DB_9B95CC_2019U1” is the database name.

SSMS Object Explorer Server View

Figure 2: SSMS Object Explorer - Server View

If you close the Object Explorer, it can always be redisplayed by using the View Menu and selecting Object Explorer.

Viewing More Objects

As we start working in SSMS, the main thing we are interested in are tables. In the Object Explorer panel, click on the + icon next to the Tables folder and the folder will expand to display any existing tables. In the screenshot below, you can that there are several folders and a single table, named "dbo.people".

SSMS Object Explorer Table View

Figure 3: SSMS Object Explorer - Table View

Tables are names using a prefix, period, and a simple name. The default table prefix is "dbo". As you can see, the table in this same database is named "dbo.people". Click the + icon next to the table name, and the expand the Columns folder, to display the table column names. You may have to drag the right border of the Object Explorer panel to see the column names.

For now, the main thing we are interested in a just the column names and the data types. The list of columns provide more information about each column, which will be useful later as we dig deeper into designing tables.

SSMS Table Column List

Figure 4: SSMS Object Explorer - Table Column List

Interacting with SSMS Using the Query Manager

The Object Explorer lets us display the database structure, but it is a read-only view of the tables and other objects. In order to interact with the database, we need to open the Query Manager, which is the main coding panel we will work with in SSMS.

To open the Query Manager (QM) the first time, right click on the database name and select [New Query]. Although there is a [New Query] selection on the menu, that will not work correctly when opening the QM the first time.

Open Query Manager

Figure 5: How to Open the Query Manager

The Query Manager (QM) is a large panel where you can interact directly with the database. It is a coding space where we can enter T-SQL statements and run them to see the results.

First, let's enter a comment that explains what we want to do. In T-SQL, comments are displayed in green and are not executed; they are just notes or reminders. There are two types of comments: inline (single line) comments and block comments (which can cover multiple lines)

In-line comments are prefixed with two hyphens. An inline comment is entered like this:

-- simple query to see the data in the people table

Block comments have a prefix (/*) and suffix (*/) surrounding the rest of the comment text. A block comment is entered like this:

/* a block comment can be spread across multiple lines */

Now let's enter a simple T-SQL query. We will use the SELECT statement, which is the only statement used to request data from one or more tables:

SELECT * from dbo.people;

After you have entered the statement, highlight that line (using the mouse or cursor) and then execute the statement by either clicking the Execute button on the menu, OR by pressing F5.

Syntax for a SELECT statement

Figure 6: Syntax for a Simple Select Statement

T-SQL Statement Syntax Rules

Although this is a simple T-SQL SELECT statement, there are several things to understand about the syntax and about executing the statement.

First, T-SQL is NOT case sensitive. T-SQL does not care if the keywords are upper-case, lowercase, or some mixture. That means that when entering keywords, entering this statement is the same as the one above:

select     *    FROM dbo.People;

T-SQL also does not care about extra spaces, or even if you enter the statement on multiple lines. It does care that the keywords are in the correct order, spelled correctly, and separated by spaces.

Second, the keywords and table name must be spelled correctly. (This is one of the most common mistakes that result in an error.)

Third, the statement should end with a semi-colon.

Fourth, the table name should include the prefix, separated by period.

To run or execute the T-SQL statement, you should always highlight the statement before executing it. This will be explained later in more detail, but for now, always follow that process. Executing a script means submitting it to the Query Manager to run or process the script and exchange data with the database.

The SELECT statement is also called a query. A query is a request, and you are requesting data from the database, via the Query Manager. The results of the query are displayed in a grid at the bottom of the Query Manager panel. From the SELECT statement that was executed in this example, the database returned a table with 4 rows and 6 columns. Because we used the "*" to specify all rows and columns, that is what was returned. As you can see in the grid, there is a column header for each column, using the name of the column as we saw it in the table view. The column are also in the same order. You will also notice that the values for the "dateofbirth" column are in the ISO date format (yyyy-mm-dd); this is the default.

Saving T-SQL Code

Before we go any further, let's discuss the query you just created. Notice that your code in the Query Manager is in a tab (a yellow tab). The tab includes a long, generic name, ending in an asterisk ("*"). The asterisk means the code in the tab has not been saved or has not been saved after changes.

A nice feature of an IDE is the ability to save your work and load it again later to do the same things, or simply to reference and copy code you have used before. These sets of T-SQL code, which might include comments and multiple queries are called a scripts. Each tab in the Query Manager is considered one script

To save your script, select [File/Save tabname As] on the SSMS main menu. Give your script a meaningful name, such as "M01_select_example" and hit <Save>. It is not necessary to include the extension on the filename. SSMS will add ".sql", by default, so that the file can be opened and executed by SSMS.

Query Manager: Save Script As

Figure 7: QM Save Script As

(When in class, save your scripts to a USB drive. While saving them to a network or cloud location is also handy, those resources may be unreachable if there are problems with the class network or those external resources.)

Once a script has been saved, the QM tab will display the actual file name and path.

To retrieve and reuse a script, select File/Open/File on the SSMS menu, navigate to the folder containing your script, and open or double-click the file. You can also load a file directly from a folder, by double-clicking it or right-clicking the file name and selecting <Open>. Because the file contains a ".sql" extension, the file will automatically be loaded in a new QM tab.

Figure 8: QM Script Saved