Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Jun 7, 2002

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

By Michael Aubert

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


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

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM