ANSI-Compliant SQL - Microsoft SQL Server Black Book

SQL has been developed to provide a uniform method for accessing data stored in a database. Even the desktop and shared-file applications like Microsoft Access and Microsoft Visual FoxPro support SQL queries against their database files. In 1992 the American National Standards Institute (ANSI) published a set of standard SQL statements that should be supported by

database applications. This standard was intended to provide functionality that can be depended on to run on many different platforms. Microsoft SQL Server, as well as other databases, supports this standard at some level or another. If you write your SQL code to follow this ANSI standard, you will be able to run your scripts and queries against a broad range of databases without modifications. This ability has thrust database development into the forefront of application development in today’s businesses. Coupled with the

development of ODBC, your client applications can now take advantage of the features of many large database systems without your having to change client code.

Microsoft SQL Server is ANSI-SQL 92-compliant and will support ANSI queries. In addition, extensions to this ANSI standard allow you more freedom in your code. These extensions, along with different syntax that can be used to write queries, are available on Microsoft SQL Server and may not be available on all systems you run your code against. Be careful if you use non-ANSI standard queries in your code. You might have to modify your code slightly to run on another system.

I will focus on the Microsoft SQL Server version of SQL (Transact-SQL), since that product is the focus of this book. For the differences in syntax, check Microsoft SQL Server Books Online.

In addition, some very good quick-reference guides for writing

ANSI-compliant code are available, including SQL Instant Reference by Martin Gruber, published by Sybex. This manual is a desktop reference all DBAs should own. It illustrates the differences between ANSI 89 and 92 SQL and has very good code examples for writing most any query you require. Now back to this book. In this chapter, I’ll walk you through the process of writing queries, from the simplest to the most advanced queries written against multiple tables with complex formatting and output. As an example, I’ll use the Pubs database installed on your server. You can run the queries I have given you here against your own system to test them and determine if they are useful. Most production systems will not have the Pubs database installed; in this case, run the queries against a development system for test purposes.

Syntax

SQL is a very readable language once you get to know the different statements

involved. You’ll find the basics of SQL easy to memorize, but the more elaborate statements and functions require a quick lookup in Microsoft SQL Server Books Online.

Comments

Do not fall into the trap of leaving comments out of your code. Comment your code in blocks so that at a minimum you can look back on it six months later and remember what you were trying to do.

Comments are placed in your code in two ways: using the forward slash and asterisk (/*) at the beginning of a comment and an asterisk and forward slash (*/) to close the comment out; or beginning and ending inline comments with a dash, dash (SQL code--comment). Whether you use the slashes or the dashes, you will be doing yourself a favor by having good notes in your code for later.

NOTE: The slashes will comment out a single line of code or multiple lines

of code, similar to other major programming languages. Dashes are used for inline comments. Use a style that makes sense to you and stick with it. Adopting a standard for comments in a production environment can save many hours of work.

SQL statements ignore white space. You can therefore use spaces freely to make your code more legible. As you go through this chapter, you will find places where I have lined different sections of code up with other sections in the code. This is done for clarity. You do not have to follow this convention; however, some kind of standard should be adopted in a multiple-programmer shop for consistency and easy understanding. When you wish to see all

columns in a table, use an asterisk (*) in place of each column name. This can make it easy to get all columns back in a query. Keep in mind, however, that this method can also cause problems. If a client software package expects five columns to be returned from a query and uses the asterisk to get the columns, a schema change can break the client. As a rule, specify the names of the

columns you wish returned with your queries.

Pubs Database

We’ll start our tour of SQL with a quick look at the Pubs database. I’ll use a few of the tables in Pubs to illustrate the data manipulation techniques you need to write effective queries. These tables are loaded automatically by the installation program and are placed inside the master device along with the master database.

Previous Table of Contents Next

Brief Full Advanced

Search Search Tips

To access the contents, click the chapter and section titles. Microsoft SQL Server Black Book

(Publisher: The Coriolis Group)

Author(s): Patrick Dalton ISBN: 1576101495 Publication Date: 07/01/97

Search this book:

Previous Table of Contents Next

Authors

Let’s first look at the Authors table, shown in Table 5.1. This table has nine columns and two indexes. Like any other entity-tracking table, the Author table holds address

information. Microsoft has populated this table with sample data. Upon installation of SQL Server, 23 rows are in this table.

Table 5.1 Authors table schema.

Column Data Type

Au_id VARCHAR(11) Au_lname VARCHAR(40) Au_fname VARCHAR(20) Phone CHAR(12) Address VARCHAR(40) City VARCHAR(20) State CHAR(2) Zip CHAR(5) Contract BIT

Indexes are on the au_id column and the au_lname and au_fname columns.

Sales

Next is the Sales table, shown in Table 5.2. This table has six columns and two indexes.

http://www.itknowledge.com/reference/standard/1576101495/ch05/142-145.html (1 of 4) [1/27/2000 6:16:03 PM]

Go!

Keyword

---

The Sales table holds sales information for each title sold for a particular store. Microsoft has populated this table with sample data. Upon installation of SQL Server, this table contains 21 rows.

Table 5.2 Sales table schema.

Column Data Type

Store_id CHAR(4) Ord_num VARCHAR(20) Ord_date DATETIME Qty SMALLINT Payterms VARCHAR(12) Title_id VARCHAR(6)

Indexes are on the title_id column and the store_id, ord_num, and title_id columns.

Titleauthor

Of the example tables, the Titleauthor table is probably the most confusing. Shown in Table 5.3, this table has four columns and three indexes. There is one record for each title that each author has written. It does not hold a lot of meaningful data, except that each book can have many authors and each author is responsible for a percentage of each title’s royalties. This table holds the ID values for each author and title. Microsoft has populated this table with sample data. There are 25 rows in this table when you install Microsoft SQL Server.

Table 5.3 Titleauthor table schema.

Column Data Type

au_id VARCHAR(11)

title_id VARCHAR(6)

au_ord TINYINT

royaltyper INT

Indexes are on the au_id and the title_id columns, and another index that combines the two columns au_id and title_id.

In document Microsoft SQL Server Black Book (Page 123-127)