| 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:
- 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.
- 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.
- After that first letter, you are pretty much free to run wild; almost any character will do.
- The name can be up to 128 characters for normal objects and 116 for temporary objects.
- Any names that are the same as SQL Server keywords or contain embedded spaces must be enclosed in double quotes ("") or square brackets ().
- 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.
SQL Server 2017 Query Performance Tuning
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.