I get the result set that Figure 1 shows. For space
reasons, I included only three of the returned rows and
trimmed the length of the columns. (I also shortened and
trimmed the data in the other sample result sets shown
here.) If you try this query, don’t be surprised if you get
different results. The data returned depends on which
SQL Server version you’re using and what your tables
contain. Having different data doesn’t matter at this point
because the focus is on the mechanics of the query itself
and not on the details of the data returned.
If you want the result set to display different
names for the columns, you can use the AS clause in
a SELECT statement. For example, if you want to
retrieve the name, type, and crdate columns but want
to name them ObjectName, ObjectType, and Creation-
Date, respectively, you can run the query
SELECT name AS ‘ObjectName’,
type AS ‘ObjectType’,
crdate AS ‘CreationDate’
FROM sysobjects
Figure 2 shows the results from this query.
Filtering Results with WHERE
If you don’t want all the data from a column returned,
you can use a WHERE clause with operators to filter
the result set to get exactly what you need. For example,
in the last two queries, all the data (which in this case
are objects) in the three columns are being returned. If
you want only objects of type U (which stands for user
table) returned, you’d run the query
SELECT name AS ‘ObjectName’,
type AS ‘ObjectType’,
crdate AS ‘CreationDate’
FROM sysobjects
WHERE type = ‘U’
In this query, the = operator is being used to see
whether each value in the type column is equal to U. If
so, that object is returned in a result set that will look
something like the one in Figure 3. The = operator is
one of several comparison operators you can use in a
WHERE clause, as Table 1 shows.
You can combine comparison operators with
logical operators to further filter result sets. Table 2 outlines the logical operators that you can use in a
WHERE clause. For example, suppose you want to
see all objects except stored procedures (which have
the object type of P) and system tables (which have
the object type of S). You can use the <> and AND
operators in the query
SELECT name AS ‘ObjectName’,
type AS ‘ObjectType’,
crdate AS ‘CreationDate’
FROM sysobjects
WHERE type <> ‘P’
AND type <> ‘S’
As Figure 4 shows, the returned result set contains only
those objects that meet the criteria.
Let’s look at a more complex example that combines
multiple comparison and logical operators. Suppose
you want to see all the stored procedure objects
whose names begin with sp. You also want to see all the
stored procedure objects that were created in the year
2000. To get this data, you can run the query
SELECT name AS ‘ObjectName’,
type AS ‘ObjectType’,
crdate AS ‘CreationDate’
FROM sysobjects
WHERE (type = ‘P’ AND
name LIKE ‘sp%’)
OR (crdate
BETWEEN ‘2000-01-01’
AND ‘2000-12-31’)
The first component—(type = ‘P’ AND name LIKE
‘sp%’)—selects only those objects that are stored procedures
(type = ‘P’) and have names beginning with sp
(name LIKE ‘sp%’). The second component—(crdate
BETWEEN ‘2000-01-01’ AND ‘2000-12-31’)—selects
any object that was created between January 1, 2000,
and December 31, 2000. Note that parentheses separate
these two components, which are part of a large
OR operation. The parentheses indicate which logical
operators to evaluate first. Figure 5 shows sample
results from this query. If you execute this query, consider
running it against one of your own databases so
that the results will be more meaningful to you.
Sorting Results with ORDER BY
Besides using a WHERE clause in a SELECT statement
to filter results, you can use an ORDER BY
clause to sort results. By default, results are sorted in
ascending order (i.e., lowest value to highest value). If
you want the results sorted in descending order (i.e.,
highest value to lowest value), you must specify the
keyword DESC. Optionally, you can use the keyword
ASC to explicitly specify that you want the results
sorted in ascending order.
For example, suppose you want a query’s returned
objects to be sorted alphabetically by object type, then
by their creation date, with the most recent date first.
The query would look like
SELECT name AS ‘ObjectName’,
type AS ‘ObjectType’,
crdate AS ‘CreationDate’
FROM sysobjects
ORDER BY type ASC,
crdate DESC
and the result set would look like that in Figure 6.
You shouldn’t use the ORDER BY clause in views,
derived tables, inline functions, and subqueries, unless you also use the TOP command. Otherwise, you might
get an error message. (If you’d like to know why the
sometimes error occurs, see the Microsoft article at
support.microsoft.com/kb/841845.)
Limiting Results with TOP
To reduce strain on the server and network resources
(not to mention your own workstation), you should
limit the number of rows returned in a result set, especially
if you’re querying a large database just to explore
it. You can use the TOP command to limit result sets.
For example, the query
SELECT TOP (5)
type AS ‘ObjectType’,
crdate AS ‘CreationDate’
FROM sysobjects
ORDER BY crdate DESC
displays the first 5 rows returned by the ORDER BY operation. Alternatively, you can have the TOP command
return a percentage of rows. For example, if you
want to display the top 5 percent of the rows returned
by the ORDER BY operation, you’d use the query
SELECT TOP (5) PERCENT
type AS ‘ObjectType’,
crdate AS ‘CreationDate’
FROM sysobjects
ORDER BY crdate DESC
The Basics Revealed
In this lesson, I covered the basic components in a
simple SELECT statement. You learned how to specify
the columns that contain the data you need and how
to specify the table containing those columns. You
also learned how to order and rename columns and
how to filter and sort results. Armed with this information,
you’re ready to write your own queries and
explore your own databases. Be careful, though, when
querying that table with 100 million+ rows. If you
don’t remember to limit the number of returned rows
like you learned to do, you’ll find yourself under the
watchful eye of the friendly neighborhood DBA.
End of Article
Prev. page
1
[2]
next page -->
