The power of a database management system such as SQL Server is the ability to create both simple and complex queries. A query interrogates one or more tables, finding all of the rows that match the specified criteria. The rows are gathered into a set of results, formatted into rows and columns, which are returned to the process that performed the query.
Querying of data tables in SQL Server is achieved using the SELECT statement. The command appears deceptively simple, allowing basic queries to be generated very quickly. However, its flexibility permits the creation of very complex queries that combine information from many tables that match detailed criteria. It allows calculations to be performed upon the data as it is gathered, including row-by-row calculations and aggregation of information based upon groups of rows.
In this article we will concentrate on simple querying of a single table. More advanced querying techniques that join the results of multiple tables, calculate aggregate information and perform grouping will be described in later articles.
This article's examples require that you are using the JoBS tutorial database and that the database is correctly populated with information. If you have not already created this database, download the script from the link at the top of the page and use it to create the database and sample data. If you have used the tutorial database, you should drop the old version and replace it using the downloaded script to ensure that the data is correct.
The SELECT Statement
The SELECT statement can include a variety of clauses that control the behaviour of the command and the results that are returned. The most basic form of the statement simply returns literal values that are not sourced from a table. These values are provided after the command as a comma-separated list.
Try executing the following command in a SQL Server Management Studio query window:
SELECT 1, 2, 3.4, 'Hello'
The results area shows a row of data containing the four values specified. The value of such a statement may not immediately be apparent. However, there are various reasons that you may wish to select a fixed row of data or include literal values within other styles of query. For example, when combining the results of multiple queries you may want to add a predefined row or a column categorising the results according to the query from which they originated.
Selecting Data From a Table
When selecting information from a table, the name of the table must be provided. This is specified using the FROM clause. You must also provide details of which columns to return from the database. If you wish to return data from every column in the table, you can use an asterisk (*) as shown below. This query retrieves every column of each of the twenty-five rows in the Engineers table.
NB: Note that the results are returned as rows and columns, in a similar format to the storage of information in a table.
Specifying a Column List
Although using the previous syntax to retrieve all of the columns for a query is useful for testing and maintenance tasks, it is not best practise for production systems. The main problem is that changes to the underlying structure of the table mean that the results may change. If the software using the results assumes the position of the columns, this could lead to incorrect data being used. A second issue is that the query may return many columns that are not required by the calling function. In this case, unnecessary network traffic is generated and the query's performance is impacted.
A more effective and efficient use of the SELECT statement is with the inclusion of a comma-separated column list in place of the asterisk. In this case, only the columns that are required are retrieved from the database and passed back to the function that initiated the query.
The following statement retrieves only the unique identifier, name and hourly rate for engineers. As the other column in the table holds binary data for a photograph of the engineer, omitting it from the list when it is not needed could vastly reduce the network traffic and time required to return the data.
SELECT EngineerId, EngineerName, HourlyRate FROM Engineers
When using a column list, the information does not need to be ordered in the same manner as the underlying table. If you change the column list, the results are modified accordingly.
SELECT HourlyRate, EngineerName, EngineerId FROM Engineers
The JoBS tutorial database does not include any columns that include spaces in their names. If you are using a database with columns that have names that include spaces, the name in the column list must be delimited using square brackets. For example:
SELECT [Engineer Name] FROM Engineers
NB: This notation can be used elsewhere in SQL Server where names include spaces. The brackets can even be included where spaces are not present.
Using Column Aliases
Sometimes you will want to change the names of the columns in the set of results. To do so, a column alias can be provided in the column list using the AS keyword. The AS keyword appears after the real column name and before the alias. In the following example, all three columns have aliases, one including a space.
EngineerId AS Id,
EngineerName AS [Engineer Name],
HourlyRate AS Cost
3 August 2008