Transact-SQL User's Guide

Home

Transact-SQL User's Guide

Chapter 1 SQL Building Blocks

SQL in Adaptive Server

SQL (Structured Query Language) is a high-level language used in relational database systems. Originally developed by IBM's San Jose Research Laboratory in the late 1970s, SQL has been adopted by, and adapted for, many relational database management systems. It has been approved as the official relational query language standard by the American National Standards Institute (ANSI) and the International Organization for Standardization (ISO).

Transact-SQL, Sybase's extension of SQL, is compatible with IBM SQL and most other commercial implementations of SQL. It provides important extra capabilities and functions, such as summary calculations, stored procedures (predefined SQL statements), and error handling.

SQL includes commands not only for querying (retrieving data from) a database, but also for creating new databases and database objects, adding new data, modifying existing data, and other functions.

If Java is enabled on your Adaptive Server, you can install and use Java classes in the database. You can invoke Java operations and store Java classes using standard Transact-SQL commands. Refer to Java in Adaptive Server Enterprise for a complete description of this feature.

Queries, data modification, and commands

In SQL, a query requests data using the select command. For example, this query asks for a listing of authors who live in the state of California:

select au_lname, city, state 
from authors 
where state = "CA"

Data modification refers to the addition, deletion, or change of data using the insert, delete, or update commands. For example:

insert into authors (au_lname, au_fname, au_id)
values ("Smith", "Gabriella", "999-03-2346")

Other SQL commands, such as dropping tables or adding users, perform administrative operations. For example:

drop table authors

Each command or SQL statement begins with a keyword, such as insert, that names the basic operation performed. Many SQL commands also have one or more keyword phrases, or clauses, that tailor the command to meet a particular need. When you run a query, Transact-SQL displays the results. If no data meets the criteria specified in the query, the user gets a message to that effect. Data modification statements and administrative statements do not retrieve data, and therefore, do not display results. Transact-SQL provides a message to let the user know whether the data modification or other command has been performed.

Tables, columns, and rows

In relational database management systems, users access and modify data stored in tables. SQL is specifically designed for the relational model of database management.

Each row, or record, in a table describes one occurrence of a piece of data--a person, a company, a sale, or some other thing. Each column, or field, describes one characteristic of the data--a person's name or address, a company's name or president, quantity of items sold.

A relational database is made up of a set of tables that can be related to each other.

Figure 1-1: Multiple tables in a database raster

The relational operations

The basic query operations in a relational system are selection (also called restriction), projection, and join. These can all be combined in the SQL select command.

A selection is a subset of the rows in a table. You specify the limiting conditions in the select query. For example, you might want to look only at the rows for all authors who live in California.

select * 
from authors 
where state = "CA"

A projection is a subset of the columns in a table. For example, this query displays only the name and city of all authors, omitting the street address, the phone number, and other information.

select au_fname, au_lname, city
from authors

A join links the rows in two or more tables by comparing the values in specified fields. For example, suppose you have one table containing information about authors, including the columns au_id (author identification number) and au_lname (author's last name). A second table contains title information about books, including a column that gives the ID number of the book's author (au_id). You might join the authors table and the titles table, testing for equality of the values in the au_id columns of each table. Whenever there is a match, a new row--containing columns from both tables--is created and displayed as part of the result of the join. Joins are often combined with projections and selections so that only selected columns of selected matching rows display.

select * 
from authors, publishers
where authors.city = publishers.city

Compiled objects

Adaptive Server uses compiled objects to contain vital information about each database and to help you access and manipulate data. A compiled object is any object that requires entries in the sysprocedures table, including:

Check constraints
Defaults
Rules
Stored procedures
Extended stored procedures
Triggers
Views

Compiled objects are created from source text, which are SQL statements that describe and define the compiled object. When a compiled object is created, Adaptive Server:

Parses the source text, catching any syntactic errors, to generate a parsed tree.
Normalizes the parsed tree to create a normalized tree, which represents the user statements in a binary tree format. This is the compiled object.
Stores the compiled object in the sysprocedures table.
Stores the source text in the syscomments table.
If you are upgrading from Adaptive Server version 11.5 or earlier, and you have deleted source text from syscomments, you must restore the deleted portions before you upgrade. To do so, follow the process in the installation documentation for your platform.

Saving source text

In Adaptive Server version 11.5 and in SQL Server® versions prior to that release, the source text was saved in syscomments so it could be returned to a user who executed sp_helptext. Because this was the only purpose of saving the text, users often deleted the source text from the syscomments table to save disk space and to remove confidential information from this public area. However, you should not delete the source text because this may cause a problem for future upgrades of Adaptive Server. If you have removed the source text from syscomments, use the procedures in this section to restore the source text.

Restoring source text

If a compiled object does not have matching source text in the syscomments table, you can restore the source text to syscomments using any of the following methods:

Load the source text from a backup.
Re-create the source text manually.
Reinstall the application that created the compiled object.

Verifying and encrypting source text

Adaptive Server versions 11.5 and later allow you to verify the existence of source text, and encrypt the text if you choose. Use these commands when you are working with source text:

sp_checkresource - verifies that source text is present in syscomments for each compiled object.
sp_hidetext - encrypts the source text of a compiled object in the syscomments table to prevent casual viewing.
sp_helptext - displays the source text if it is present in syscomments, or notifies you of missing source text.
dbcc checkcatalog - notifies you of missing source text.


	Expand Search