SQLConfiguration SQLConfiguration

Select Data  «Prev  Next»
Lesson 7 Additional keywords
Objective Use Additional Keywords in your Queries

Use Additional Keywords in your Queries

There are many additional keywords which your queries can contain. They are listed in a separate lesson because they are not necessarily used in everyday queries, but you need to know that they exist.
The following table outlines these additional keywords:
ASC Sorts records in one or more columns in ascending order
COMPUTE Generates additional summary totals at the end of the resultset
COMPUTE BY Generates additional summary totals and sub-totals at the end of the resultset
CONTAINS Allows you to specify a wildcard in searching for data in the database
DESC Sorts records in one or more columns in descending order
DISTINCT Considering all columns in the resultset, duplicate values will not be returned
EXISTS subquery Tests to see if rows exist in the database, based on the subquery specified by subquery
FOR BROWSE An application using DB-Library will be able to update data while it is being browsed, or viewed
HAVING Specifies a search condition for groups when using a GROUP BY clause. This is similar to a WHERE clause in a SELECT statement.
OPTION Indicates to SQL Server which Query Plan to use. Query Plans are discussed in greater detail in the last course in this series
TOP x Returns the top x records in the recordset
TOP x PERCENT Returns the top x percentage of records in the recordset

Rules for Naming

As I mentioned earlier, the rules for naming in SQL Server are fairly relaxed, allowing things like embedded spaces and even keywords in names. Like most freedoms, however, its easy to make some bad choices and get yourself into trouble. Here are the main rules:
  1. The name of your object must start with any letter, as defined by the specification for Unicode 3.2. This includes the letters most Westerners are used to: A-Z and a-z.
  2. Whether "A" is different from "a" depends on the way your server is configured, but either makes for a valid beginning to an object name.
  3. After that first letter, you are pretty much free to run wild; almost any character will do.
  4. The name can be up to 128 characters for normal objects and 116 for temporary objects.
  5. Any names that are the same as SQL Server keywords or contain embedded spaces must be enclosed in double quotes ("") or square brackets ([]).
  6. Which words are considered keywords varies depending on the compatibility level to which you have set your database.
Note that double quotes are acceptable as a delimiter for column names only if you have SET QUOTED_IDENTIFIER ON. Using square brackets ([ and ]) avoids the chance that your users will have the wrong setting.
These rules are generally referred to as the rules for identifiers and are in force for any objects you name in SQL Server, but may vary slightly if you have a localized version of SQL Server (one adapted for certain languages, dialects, or regions). Additional rules may exist for specific object types.

Naming of Objects.

SQL Server has the ability to embed spaces in names and, in some cases, to use keywords as names. One should resist the temptation to do either of these things.
Columns with embedded spaces in their names have nice headers when you make a SELECT statement, but there are other ways to achieve the same result. Using embedded spaces and keywords for column names is begging for bugs, confusion, and other disasters. I will discuss later why Microsoft has elected to allow this, but for now, just remember to associate embedded spaces or keywords in names with something that should be avoided.