Datasheet

It is worth noting that this shorthand notation can result in some (minor) overhead and reduction in per-
formance because the RDBMS must work out what the columns are for you. That isn’t something to
worry too much about, and certainly not when prototyping code, but it may be something you return to
when optimizing the performance of an application if you used it a lot when developing your code. The
best practice is to avoid using
*.
The next important thing you should know about is the capability to filter the data that you retrieve.
Again, this is something you saw in an earlier example, and involves the use of the
WHERE keyword to
add a so-called where clause to the
SELECT statement:
SELECT [Column(s)] FROM [Table] WHERE [Filter]
The filter used in a where clause may be a simple one, such as a equality between column values and a
literal value:
SELECT EntryName, PhoneNumber FROM PhoneBookEntry
WHERE PhoneBookEntryId = ‘f4367a70-9780-11da-a72b-0800200c9a66’
Here data is returned for any rows that have a PhoneBookEntry column containing the value
f4367a70-9780-11da-a72b-0800200c9a66. This is a GUID value, and is a unique, primary key value
if this table is defined as per the example given earlier in this chapter, so the example query will return a
single row of data containing the columns
EntryName and PhoneNumber.
Filters can also be made up of multiple parts, combined using Boolean operators such as
AND and OR,
and use other comparison operators such as
> for “greater than” or <= for “less than or equal to.”
Another common operator in where clauses is
LIKE, which you use to perform searches within text col-
umn values. When using
LIKE you must supply a string literal value for comparison, which may con-
tain wildcard symbols to widen the search criteria. In SQL Server, those symbols include
% to refer to any
string of zero or more characters,
_ to refer to any single character, and others. For example:
SELECT * FROM PhoneBookEntry
WHERE EntryName LIKE ‘%chaucer%‘ AND Address LIKE ‘%canterbury%‘
This statement returns all the rows in the PhoneBookEntry table whose EntryName column contains the
string
chaucer and whose Address column contains the string canterbury. The query performs a case-
insensitive search in SQL Server, unless the database is configured to enforce case-sensitive searching.
Combining multiple filter parts in a where clause is a flexible technique that you can use to obtain data
sets that are as specific as you like. Good use of filtering can be important in the performance of your
applications because minimizing the amount of data that is exchanged between your applications and
your databases will lessen the time taken to make these exchanges.
Using Joins to Retrieve Data
The SELECT keyword can also be used to obtain data from multiple tables. There are alternative ways of
doing this, of course, such as using complicated nested queries, but the simplest and most common way
is to perform a join between pairs of tables in the query. There are several join types, each of which is
20
Chapter 1
44063c01.qxd:WroxBeg 9/12/06 10:31 PM Page 20