SQL Server 2000 Administration in 15 Minutes a Week: More Database Creation Topics
June 7, 2002
- 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.