120
Part II: Building Databases and Working with Data
Best Practice
Using the two-part name, (that is, the schema and object name) is suffi cient and the best practice.
Including the server and database name would restrict moving code from one server to another (for
example, from development to production). The following code shows an example of using a two-
part name:USE DB1;SELECT * FROM dbo.[Order]In addition to writing cleaner code, using the qualifi ed name has two specifi c benefi ts:■ (^) The same table may exist in multiple schemas. If this is the case, then the schema
selected is based on the user’s default schema. Qualifying the name avoids acciden-
tally using the wrong table.
■ (^) Qualifi ed table names are required for the Query Engine to reuse the query execu-
tion plan, which is important for performance.
For more about schemas, scope, and permission issues, see Chapter 32, “Authentication Types in SQL
Server,” and Chapter 33, “Authorizing Securables.” Query plan reuse is discussed in Chapter 46,
“Maximizing Query Plan Reuse.”
WHERE Conditions .............................................................................................
The WHERE conditions fi lter the output of the FROM clause and restrict the returned rows in
the result set. The conditions can refer to the data within the tables, expressions, built-in
SQL Server scalar functions, other queries, or user-defi ned functions. The WHERE conditions
can also use several possible comparison operators and wildcards, as listed in Table 6-1.
In addition, you can combine multiple WHERE conditions using Boolean AND, OR, and NOT
operators.Best Practice
To improve the performance of a client/server database, let the database engine do the work of
restricting the rows returned, rather than make the client application wade through unnecessary data.c06.indd 120c06.indd 120 7/30/2012 4:16:00 PM7/30/2012 4:16:00 PM
http://www.it-ebooks.info