SQL Scripts

A SQL script is a set of T-SQL statements composed of one to many batches, and usually saved as a file on the client side.

SQL scripts usually bear the .sql extension. When you run workshops, you have the ability to save your work as a .sql script. This script can be loaded at a later time and executed.

When many commands must be executed, those commands are usually prepared, debugged, and saved as a SQL script, in the same way that C programs are debugged and saved. An example of this is the instpubs.sql script, located in \mssql\install. This script contains all of the commands to create the pubs database and all of its tables, procedures, indexes, and other objects.

Scripts, like good C programs, contain comments to indicate the operations that are occurring.

If you want to run a script, you can load it into ISQL/w with the File | Open command. You may also use the command-line ISQL, like this:

isql -Usa -Pmerriwhether45 -SP1 -iinstpubs.sql
-opubsout.out -e

The arguments to isql include the username (sa), the password (merriwhether45), the server’s name (P1), the input file or SQL script (instpubs.sql—full pathnames may be provided here), the output file (pubsout.out), and the -e option, which means “Echo the input file in the output file so that I can tell which commands caused what output.”

Summary

Today you learned about databases and batches. Databases are objects that serve as a container for other, related objects. The full name of an object is composed of database.owner.object. If the owner is left out, the current user is assumed, then dbo. Dbo is a special user, the database owner.

Batches are one or more SQL statements that are sent to the server to be compiled and executed as a group. If a statement in a batch fails, the entire batch is rejected. Batches may be delineated in ISQL/w by using the word GO.

Q&A

Q Are the five steps of query execution always performed?

A Not always. Stored procedures, discussed on Day 16, are parsed when they are submitted. They are compiled once, the first time they are called by a user. Subsequent procedure calls are simply passed through the execute phase.

If you have an application program that uses ODBC, the ODBC driver may be set to generate stored procs for all of its queries. This causes the same query to be parsed and compiled once, and only executed thereafter.

Workshop

The Workshop provides quiz questions to help you solidify your understanding of the material covered, as well as exercises to provide you with experience in using what you’ve learned. Try to understand the quiz and exercise answers before continuing on to the next day’s lesson. Answers are provided in the appendix.

Quiz

1.  Interpret the names of these objects:

a.  exec john.calculate_total

b.  pubs.dbo.authors

c.  authors

d.  pubs..authors

2.  Name the five steps that the server must perform when a batch of SQL statements is sent for execution.

Exercises

1.  Find your current database.

2.  How big is your database? (Hint : The sp_helpdb proc will tell you this.)

.  Change to the master database.