|
Using the Microsoft SQL Server
Management Studio
|
|
The Object Explorer displays a list of items as a
tree-style. 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 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. 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 logged in to a
server
named Central:
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 select item. To do this, on the main menu, you
can
click View -> Object Explorer Details. The main are 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.
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:
- 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. 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
![Save]()
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.
|
The Structured Query Language |
|
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 data manipulation language (DML).
There are
many of them on the market. The Structured Query Language, known as SQL,
is a
DML used on various computer systems to create and manage databases.
![Author Note]() |
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
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, 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 ![Close]() .
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 don't have to save the
contents of the
window.
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:
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
![Execute]() - You can right-click somewhere in the code editor and click Execute
When you execute code, code editor 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.
|
Accessories for SQL Code Writing |
|
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). |