How to improve SQL Select statement

The following is an example of typical and inefficient Select statement. It works with the Clients table:


-The asterisk (*) retrieves all the columns in the table. This isn’t the best approach for a large table if a Web developer does not need all the information. It increases the amount of data that has to be transferred and can slow down used server.
-The From clause identifies that the Clients table is being used for this statement
-The statement does not have a Where clause. This means all the records will be retrieved from the database, regardless of whether it has 10 or 10 million records. This is a poor design practice, because it often leads to applications that appear to work fine when they’re first deployed but gradually slow down as the database grows. In general, Web developer should always include a Where clause to limit the possible number of rows (unless he/she absolutely needs them all). Often, queries are limited by a date field (for example, including all orders that were placed in the last three months).
-The statement doesn’t have an Order By clause. This is an acceptable approach, especially if order doesn’t matter or Web developers plans to sort the data on his/her own using the tools provided in ADO.NET

Here’s another example that retrieves a list of client names:

SELECT cli_lname, cli_fname FROM Clients WHERE State=’NY’ ORDER BY cli_lname ASC

Web developer has done the following improvements:

-Only two columns are retrieved (cli_lname and cli_fname). They correspond to the first and last names of client
-A Where clause restricts results to those clients who live in the specified state (New York). Note: The Where clause requires apostrophes around the value, Web developer wants to match, because it’s a text value.
-An Order By clause sorts the information alphabetically by the client’s last name. The ASC part (for ascending) is optional, because that’s the default sort order.