Microsoft SQL Server - Lesson 04: Introduction to SQL Code

	Introduction to SQL Code

The Structured Query Language

Introduction to Code

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

Introduction to Code

To open the editor:

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

This would create a new window and position it on the right side of the interface.

Saving Code

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.

Introduction to SQL

After establishing a connection, you can take actions, such as creating a database and/or manipulating data. To provide the ability to create and manipulate a database, you use a data manipulation language (DML). The Structured Query Language, known as SQL, is a DML 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. Although Microsoft 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.

Throughout our lessons, we will use "SQL" and "Transact-SQL". Most of the time:

SQL (used by itself) refers to a way the issue is used in most implementations of the language (Microsoft Access, MySQL, Oracle, DB2, etc)
Transact-SQL may refer to a particular way the topic is used in Microsoft SQL Server, which means it may not work like that in other implementations

Practical Learning: Introducing Transact-SQL Code

Start the computer
Log in with the account you used to install Microsoft SQL Server
To launch Microsoft SQL Server, click Start -> (All) Programs -> Microsoft SQL Server 2008 -> SQL Server Management Studio
On the Connect to Server dialog box, click Connect

The SQL Interpreter

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 of this site, 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, in the Object Explorer, right-click the name of the server and click New Query. In the same way, you can open as many instances of the New Query window as you want.

When the Query window comes up, it displays a blank child window in which you can write your code. 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, save it.

Executing a Statement

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, it is made of a wide white area where you write your statements:

The Code Editor

After writing a statement, you can execute it, either to make it active or simply to test it. To execute a statement:

Press F5
On the main menu, click Query -> Execute
On the SQL Editor toolbar, click the Execute button
Right-click somewhere in the code editor and click Execute

When you execute code, code editor becomes divided into two horizontal sections:

Microsoft SQL Server Manadement Studio

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

Microsoft SQL Server Management Studio: An error in the Query window

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

Accessories for SQL Code Writing

Code Colors

To make your code less boring, you can ask the Code Editor to apply colors to it. To do this, on the main menu, click tools -> Options... In the left tree, expand Environment and click Fonts and Colors:

Options

To specify a color, in the Display Items list box, click an option, then change its colors in the Item Foreground and the Item Background combo boxes. Both check boxes are filled with colors. If you want to use a color that is not in the list, click the corresponding Custom button and create a color.

Code Templates

To assist you with writing code, Microsoft SQL Server provides many templates you can customize. These are available in the Template Explorer. To access it, on the main menu, click View -> Template Explorer.

The Template Explorer shows its item in a tree list. Like most windows in Microsoft SQL Server, it is floatable and dockable:

Template Explorer

To use a code template, first create Query window. In the Template Explorer, expand the category you want. Then drag the desired option and drop it in the Query window. The Query window doesn't have to be empty. This means that, if it contains some code already, you can drag a node from the Template Explorer and drop it where it would add to the existing code. You can then edit the code any way you like.

Comments

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

The End of a Statement

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.

Prolonging a Line of Code

Sometimes you will write a long line of code that may disappear on the right side of the Code Editor. To interrupt a line and continue the code on the next line, the formula to use is:

Section 1 \
Section 2

You start a section of code, add a backslash \, and continue the code on the next line.

Time to GO

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). The option to use GO is specified in the Batch Separator text box of the Query Execution section of the Options dialog box:

Options

The Properties Window

Besides the Template Explorer, Microsoft SQL Server provides the Properties window that can assist you with some issues. To get the Properties window if it is not visible:

On the main menu, you can click View -> Properties Window
Right-click the window on the right side and click Properties Window

The Properties window uses the common behaviors of other windows: it can auto-hide, it can be docked, or it can float.

The Properties window is divided in 5 sections:

Sections of the Properties window

The Properties window starts on top with a title bar, which displays the string Properties. If the window is docked somewhere, it displays the Window Position , the Auto-Hide , and the Close buttons on its right side. If the window is floating, it would display only the Close button .

Under the title bar, the Properties window displays a combo box. The content of the combo box depends on the contents of the main window and on what is going on. Under the combo box, the Properties window displays a toolbar with three buttons: Categorized button , Alphabetical , and Property Pages .

Under the toolbar, the Properties window displays a list of fields. This list depends on the contents of the main window and on what is going on. Here is an example when a Query window has been created:

Sections of the Properties window

On the right side, the list may be equipped with a vertical scroll bar. To rearrange the list, you can click the Alphabetical button .

Each field in the Properties window has two sections: the property's name and the property's value:

The box on the right side of each property name represents the value of the property. Some values can be changed and some others not. When a property can be changed, its name appears in normal characters:

Enabled Property

When the name of a property appears disabled, it means the value cannot be changed.

Under the list of properties, there is a long bar that displays some messages. The area is actually a help section that displays a short description of the property that is selected in the main area of the Properties window.

Microsoft SQL Server Logins

Introduction

In order to access something (such as a computer or a database), you or the user must be authenticated. The authentication is done using a username and a password. In the absence of this, a user would receive an error:

The user can also take advantage of the group he or she belongs to. For this reason, the ability to be authenticated in order to access the database or resource is called a login.

Creating a Login

Before creating a login, the person for whom you want to create the account must have a user account. In some cases the account must be on the computer. In some other cases, the person must have an acccount on the network. In other words, the person must have an account that allows him or her to establish a connection either on the local computer or on the network.

If you are using Microsoft Windows XP-7, the person must have a local user account on the computer. If you are working on a server, the person must have an account in the domain. You can create a login either visually or with code.

To visually create a login in Microsoft SQL Server, in the Object Explorer, expand the Security node. Right-click Login and click New Login...

New Login

This would display the Login - New dialog box:

The formula to programmatically create a login is:

CREATE LOGIN loginName { WITH <option_list1> | FROM <sources> }

<option_list1> ::= 
    PASSWORD = { 'password' | hashed_password HASHED } [ MUST_CHANGE ]
    [ , <option_list2> [ ,... ] ]

<option_list2> ::=  
    SID = sid
    | DEFAULT_DATABASE =database    
    | DEFAULT_LANGUAGE =language
    | CHECK_EXPIRATION = { ON | OFF}
    | CHECK_POLICY = { ON | OFF}
    | CREDENTIAL =credential_name <sources> ::=
    WINDOWS [ WITH <windows_options>[ ,... ] ]
    | CERTIFICATE certname
    | ASYMMETRIC KEY asym_key_name<windows_options> ::=      
    DEFAULT_DATABASE =database
    | DEFAULT_LANGUAGE =language

To let Microsoft SQL Server generate skeleton code for you, on the Standard toolbar, click New Query to get a text editor. In the Template Explorer, expand the Login node, drag Create SQL Login Must Change Password and drop it in the empty text editor:

-- =================================================
-- Create SQL Login Must Change Password template
-- =================================================

CREATE LOGIN <SQL_login_name, sysname, login_name> 
	WITH PASSWORD = N'<password, sysname, Change_Password>' 
	MUST_CHANGE,
	CHECK_POLICY = <check_policy,ON or OFF, ON>;
GO

After creating a login, a user can use it to connect to the server. There are many options, as we will see in the next few sections.

The Server Type

After launching Microsoft SQL Server, in the Connect to Server dialog box, the user must select the type of server. This is done in the Server Type combo box:

Connect to Server

In most cases, and for our lessons, we will use the Database Engine option. If the SQL Server Management Studio is already opened, to log in, the user can click Connect -> Database Engine...

Connect

The Server Name

The user must specify what computer has Microsoft SQL Server. If working from the Connect to Server dialog box, the user can select a computer in the Server Name. If the user had previously used Microsoft SQL Server, the dialog box would display the name of the computer he previously connected to and he can simply click Connect.

If the user wants to connect to another computer on the network, that is, a computer other than the one selected, he can type the computer name or select it in the Server Name combo box. Another option is to click the arrow of the Server Name combo box and select <Browse for more...>:

Connect to Server

In this case, intermediary steps allow the user to select the computer. After selecting the computer, in the Authentication combo box, the user must specify the type of authentication to use. There are various choices.

If the user is working from PowerShell or from the Windows command prompt, to connect to a local computer, he can type SQLCMD -S followed by the name of the computer and press Enter. You can include space between -S and the name of the server. Here is an example:

Connect

You can also omit the space between -S and the name of the server. Here is an example:

Connect

The Type of Authentication

As we will see later on, to use Microsoft SQL Server, the user must provide a user name and a password. In reality, Microsoft SQL Server 2008 requires every user to log in but it provides various options. If the user already has a login, when he starts the computer an logs in to the operating system, when he launches Microsoft SQL Server, if the application recognizes his login account, he can log in without providing a user name and a password.

If you are visually creating a login and if you want the Microsoft Windows operating system to take care of logging, in which case the user will be able to connect to Microsoft SQL Server without providing a user name and a password, click the Windows Authentication radio button (it is the default and should be selected already:

If you are creating the login with code, add the FROM WINDOWS flag:

CREATE LOGIN See Below
FROM WINDOWS

When Windows Authentication is used or if you add the FROM WINDOWS flag, when the Connect to Server dialog box comes up, the user can select Windows authentication and click Connect.

The Login Name

If working from the Connect to Server dialog box, if the user selects SQL Server Authentication, he would have to provide a login name. Of course, the login must have been created.

If you are visually creating a login, in the Login Name text box, type the user name of the person whose account you are creating. If you are creating the login in the same computer where Microsoft SQL Server is installed (Microsoft Windows XP-7), type the user name as it was created in the computer. If you are creating the login for a user account on the server (domain), type the name of the domain, followed by \, and followed by the user name as it exists in the domain. Here is an example:

Of course, the user's account must have been created already in the computer (Microsoft Windows XP-7) or on the server (domain). If you provide a user name that cannot be found in the system, when trying to finalize, you would receive an error.

The Password

If the user decides to use SQL Server Authentication, after specifying a login name, he must also provide a password, which must be associated with the login. If you are visually creating the login, to provide a password, first click the SQL Server Authentication radio button. Then click the Password text box and type the desired password. Click the Confirm Password text box and type the same password.

If you are programmatically creating the login, to specify the password, after the login name, type WITH PASSWORD = and provide the password in single-quotes. Remember that when Microsoft SQL Server is installed, it creates a default account named sa. You can also use it as a login and provide its password:

Connect to Server

Microsoft SQL Server allows a user to connect with many login account. After connecting, to make another connection, the user can click Connect -> Database Engine, and provide the necessary pieces of information as we had seen in this section. In the same way, you can create as many connections as possible, using valid login accounts.

Disconnecting From a Server

After using a server or to take a break (such as stepping out from the computer), the person can disconnect from it. To do this:

On the main menu, click File -> Disconnect Object Explorer
In the Object Explorer, right-click the connection node and click Disconnect

Disconnecting neither closes Microsoft SQL Server nor shuts down the server. It simply closes the connection to the server. If a persons wants to use the server again, she must connect.

Using the Microsoft SQL Server Management Studio

The Object Explorer

The Object Explorer displays a list of items as a tree. 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 servers. 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:

Microsoft SQL Server Management Studio

If you are connected to more than one server, each is represented by its own node:

Microsoft SQL Server Management Studio

The name of the server is followed by parentheses.

In the previous section, 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. When many connections have been made, each connection is represented in the Object Explorer by its own node and each connection has its own objects (sub-nodes):

Connection

To close a connection, you can right-click it and click Disconnect:

Disconnect

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 named Administrator is currently logged in to a server named Central:

Microsoft SQL Server Management Studio

Object Explorer Details

We saw that, by default, the right area of Microsoft SQL Server Management Studio displays an empty gray window. When you select something in the Object Explorer, you can use that right area to display more detailed information about the selected item. To do this, on the main menu, click View -> Object Explorer Details. The main area on the right side would then be filled with information:

Object Explorer Details

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 connected 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. For example, to start PowerShell, you can right-click the Databases node or the server name and click PowerShell:

Starting PowerShell from the Object Explorer

When the PowerShell comes up, what it displays depends on what you had right-clicked.

Practical Learning: Closing Microsoft SQL Server

To close Microsoft SQL Server, on the main menu, click File Exit.
If you are asked whether you want to save something, click No

Exercises

Lesson Summary Questions

Review Questions