SQL Server 2000 Administration in 15 Minutes a Week: More Database Creation Topics


Welcome to the sixth article in my
series SQL Server Administration in 15 Minutes a Week. Last
week we created our first database in SQL Server. We looked
at what files make up a SQL Server database, how data is
stored inside these files, and how filegroups can be used
to manage files. This week we have a lot to cover, so let’s
get going. The topics for this article include:

– SQL and T-SQL
– Understanding Transaction Logs


SQL and T-SQL

Structured Query Language, also known
as SQL, is a query and programming language. It can be used
for accessing, updating, deleting, and adding data in a
database. SQL can also be used for managing the RDBMS
(Relational Database Management System) itself. Different
databases may use versions of SQL that vary slightly, but
most comply with the standard ANSI SQL-92 implementation of
SQL, commonly call ANSI SQL. You can group SQL statements
into two main categories: Data Definition Language (or DDL)
and Data Manipulation Language (or DML).

DDL statements, as the name suggests,
allow you to define the database structure. Most DDL
statements begin with CREATE, ALTER, or DROP. The two DDL
statements we are going to cover today are CREATE DATABASE
(used for creating new databases) and ALTER DATABASE (used
for altering existing databases). We will look at the exact
syntax of these two statements later on in this article.

DML statements, on the other hand, are
used for manipulating the data inside database objects. For
example, the SELECT statement allows you to query the data
inside a database, the INSERT statement allows for the
addition of new data, the UPDATE statement updates selected
data, and the DELETE statement allows you to remove
data. As this series progresses we will cover these
statements as well as many more DDL and DML statements in
greater detail.

We now know what SQL is, but what is
T-SQL? Simply, T-SQL is SQL Server’s enhanced version of
the standard SQL programming language. T-SQL in SQL Server
2000 allows for such things as stored procedures, IF and
WHILE statements, and additional functions/data types (we
will cover data types when we start creating tables) that
are not available in standard SQL.

To use T-SQL statements we need some
way of sending these statements to the RDBMS. One way is to
use the OSQL command line utility. If you are a DBA who has
worked with SQL Server 6.5, you will notice that the OSQL
command line utility has replaced the older ISQL utility
which does not support some of the new features of SQL
Server 2000. While supporting SQL Server 2000, OSQL uses
the command line interface and is therefore not very user
friendly. However, if you would like to learn more about
this utility, lookup "osql utility" in the SQL Server Books
Online.

The tool we are going to use
throughout this series to write, edit, and optimize our
T-SQL scripts is called the SQL Query Analyzer. This
graphical user interface tool lets you run T-SQL
statements, create scripts that can be saved and edited,
and will even provide information that can be used
to optimize both databases and T-SQL statements.

There are two main ways to open the
SQL Query Analyzer. First, you can open it directly by
selecting "Query Analyzer" from the Microsoft SQL Server
group on the Start Menu. When the SQL Query Analyzer opens,
the first thing you will be asked is to select a server and
provide the log on credentials. The following screen shows
a connection to the local installation of SQL Server using
Windows authentication.


Alternatively, you can access the
Query Analyzer from inside the Enterprise Manager. Let’s
look at how to do this by creating a new database using
T-SQL and the Query Analyzer. To start, open Enterprise
Manager and connect to your SQL Server by selecting it in
the tree pane.


Next, on the tools menu select Query
Analyzer. The first thing you will notice is that we are
not asked to select a server for login information. When
you start Query Analyzer from within the Enterprise
Manager, Query Analyzer automatically uses the server you
have selected in the tree pane. Additionally, it passes the
logon credentials that you used to connect to the server in
Enterprise Manager.


The status
bar at the bottom of the Query window gives us important
information about the server we are connected to, the
user we are connected as, the database we are using, as well as other status information.

Page 2: Creating a Database with T-SQL

 »


See All Articles by Columnist
Michael Aubert

Latest Articles