Introduction to the Stairway
This Stairway will explore topics that go beyond TSQL basics. The articles will expand on the TSQL foundation that you learned in my Stairway to T-SQL DML which you can read at http://www.sqlservercentral.com/stairway/75773. This “Beyond T-SQL Basics” stairway will cover the following T-SQL topics:
- The TOP Clause
- Sub-Queries
- Correlated Sub-Queries
- Using Views to Simplify Querying Multiple tables
- Storing Data in Temporary Tables
- Controlling the Flow of your TSQL code
- Coding Short Cuts using += and -= Operations
- Dynamic SQL
Readers of this stairway should already have a basic understanding of how to use the SELECT, INSERT, UPDATE and DELETE statements. Additionally readers should also have a basic understanding of processing data with IF THEN ELSE logic, as well as concept of looping through data.
This stairway should help readers prepare for passing the Microsoft Certification exam 70-461: Querying Microsoft SQL Server 2012.
Understanding the TOP Clause
For the first topic in this stairway I will be discussing the TOP clause. The TOP clause allows you to control the number of rows to be returned or affected by a query.
You will find there are many times you want to return a subset of rows from a result set. The TOP clause allows you an easy way to meet the need of returning that subset. Here are a couple of examples of why you might want to use the TOP clause to return a subset of records within a set:
- You want to return just a subset of records to validate your code
- You only need to determine that at least one row meets a particular WHERE condition
- Your business requirements dictate that you only return the top so many records based on a specific WHERE criteria
In order to explain how the TOP clause works let me provide you the syntax and some examples. This will make it much easier for you to understand and see how the different nuances of using the TOP clause affect what is returned from TOP clause queries.
Syntax of the TOP Clause
The syntax for the TOP clause is relatively simple. You can use the TOP clause by placing this syntax in any SELECT, DELETE, INSERT, or UPDATE statement:
TOP (expression) [PERCENT] [WITH TIES]
The “expression” value is a numeric number. This numeric value will be converted to a FLOAT data type if the PERCENT option is used or a BIGINT data type if the PERCENT option is not used. The “expression” value can be represented as a number, or can be specified as a local variable.
The PERCENT option when specified identifies that SQL Server will return a percentage of rows from the result set based on the number represented by the “expression” value. When the PERCENT option is omitted SQL Server will return the number of rows represented by the “expression” value.
The WITH TIES option, which was introduced with SQL Server 2005, is used to tell SQL Server to return all the rows that have the same values for the ORDER BY columns as the last row. Using the WITH TIES option might cause SQL Server to return more rows than specified by the “expression” value. The WITH TIES option is only valid for SELECT statements that contain an ORDER BY clause.
Sample Data for Examples
Before I can show you example of the different ways to use the TOP clause I will need to create some sample data. In Listing 1 I have created a sample table name dbo.HectorSales that will be used in all of my examples.
SET NOCOUNT ON;
USE tempdb;
CREATE TABLE dbo.HectorSales (
SalesAgent varchar(30),
Region varchar(10),
SalesAmount decimal(10,2));
INSERT INTO dbo.HectorSales VALUES
('John Smith', 'West', 78931.01),
('Mary Johnson', 'West', 8723412.61),
('Doris Bean', 'West', 2000111.67),
('Martin Derrick', 'West', 120834.81),
('Don Olson', 'West', 508921.48),
('Sam Holder', 'East', 8723412.61),
('Nick Potts', 'East', 9834212.87),
('Lori Morin', 'East', 2000111.67),
('Stan Morris', 'East', 4562341.67),
('Jason Thomas', 'East',13424.51);
Listing 1: Sample Data for Examples
My sample table contains sales information for a fictitious company named Hector. The Hector Company has two regions, East and West. Each region has five different SalesAgents. In Listing 1 I created the SalesAmount information for each SalesAgent in each Region. If you want to follow along and run the examples I will be providing than you can use the code in Listing 1 to create my sample table.
Simple Example of the TOP Clause
For my first example I have the requirement find the top the two SalesAgent’s in the Hector Company based on SalesAmount. The code in Listing 2 contains my TOP clause solution.
-- Find Top Two SalesAgent's by SalesAmount USE tempdb; GO SELECT TOP(2) SalesAgent, Region, SalesAmount FROM dbo.HectorSales ORDER BY SalesAmount DESC;
Listing 2: Finding Top Two SalesAgent’s by SalesAmount
When I run the code in Listing 2 I get the two rows returned that are found in Report 1.
SalesAgent Region SalesAmount ------------------------------ ---------- -------------------------- Nick Potts East 9834212.87 Mary Johnson West 8723412.61
Report 1: Results from Running Query in Listing 2
In the example in Listing 2 I included the ORDER BY clause. This is required to make sure the TOP clause selects the SalesAgent’s that have the highest SalesAmount. When you use a TOP clause without an ORDER BY clause SQL Server returns a random set of records. Remove the ORDER BY clause and rerun the query in Listing 2 to see which random rows are returned.
Example Using the PRECENT option of the TOP Clause
If you want to return a percentage of the top records in a set then you need to use the TOP clause with the PERCENT option. To demonstrate using the PERCENT option look at Listing 3.
-- Find Top 50 Percent of SalesAgent's by SalesAmount USE tempdb; GO SELECT TOP(50) PERCENT SalesAgent, Region, SalesAmount FROM dbo.HectorSales ORDER BY SalesAmount DESC;
Listing 3: Returning the Top 50 Percent of the rows
When I run the code in Listing 3 the query returns 5 rows, which is 50 PERCENT of the rows. The results from this query can be found in Report 2.
SalesAgent Region SalesAmount ----------------------------- ---------- --------------------------- Nick Potts East 9834212.87 Mary Johnson West 8723412.61 Sam Holder East 8723412.61 Stan Morris East 4562341.67 Lori Morin East 2000111.67
Report 2: Results from Running Query in Listing 3
Note: I also included an ORDER BY clause in this example. Once again if you remove the ORDER BY clause to confirm that the code in Listing 3 will random rows will be returned.
Example Using a Variable to Control the TOP Clause
There are times when you do not know in advance the number of rows you want to return. When this is the case, you can use a variable to control identifying the number of rows you what to return. To show you how this works look at the example in Listing 4.
-- Find Top Two SalesAgent's by SalesAmount USE tempdb; GO DECLARE @Number INT = 2; SELECT TOP(@Number) SalesAgent, Region, SalesAmount FROM dbo.HectorSales ORDER BY SalesAmount DESC; -- Find Top 50 Percent of SalesAgent's by SalesAmount SET @Number = 50; SELECT TOP(@Number) PERCENT SalesAgent, Region, SalesAmount FROM dbo.HectorSales ORDER BY SalesAmount DESC;
Listing 4: Using a Variable to Identify the Number of Rows to Return
In my example in Listing 4 I declared a variable named @Number. I used this variable to control the number of rows returned from the two different SELECT queries. In the first SELECT statement I set the @Number variable to 2. This query returns the same results as found in Report 1. I then set the @Number variable in the second SELECT statement to 50 so it would return the same results as in Report 2.
USING the WITH TIES Option
If you order your data using an ORDER BY clause you might find your data has multiple rows that contain the same column values. If you to what to make sure your TOP query always returns all those rows that have column values that are equal to the last row, then you need to include the WITH TIES option. When using the WITH TIES option it is possible that you might bring back more rows than the number you specify with the TOP clause. Let’s look at Listing 5 to show how the WITH TIES option works.
-- Find Top Two SalesAgent's by SalesAmount using WITH TIES USE tempdb; GO SELECT TOP(2) WITH TIES SalesAgent, Region, SalesAmount FROM dbo.HectorSales ORDER BY SalesAmount DESC;
Listing 5: Using The WITH TIES option
When I run the code in Listing 5 I get results found in Report 3.
SalesAgent Region SalesAmount ------------------------------ ---------- -------------------------- Nick Potts East 9834212.87 Mary Johnson West 8723412.61 Sam Holder East 8723412.61
Report 3: Results from Running Query in Listing 5 that used the WITH TIES option
As you can see my results returned 3 rows instead of 2. The third row was returned because this row has the same SalesAmount value as the second row. The WITH TIES option requires that the third row be returned.
USING the TOP clause with an UPDATE Statement
You can limit the number of rows affected by an UPDATE statement by using the TOP clause. The code in Listing 6 updates 2 random rows in my HectorSales table.
USE tempdb; GO -- Records before Update SELECT * FROM dbo.HectorSales WHERE Region = 'West'; --Update using TOP clause UPDATE TOP (2) dbo.HectorSales SET SalesAmount = 100000.00 FROM dbo.HectorSales WHERE Region = 'West' -- Records after Update SELECT * FROM dbo.HectorSales WHERE Region = 'West';
Listing 6: UPDATE statement with TOP clause without ordering data
By reviewing the code in Listing 6 you can see I displayed the records in my dbo.HectorSales table before and after the update statement. The results in Report 4 contain my records prior to the UPDATE statement and the results in Report 5 contain my dbo.HectorSales records after the UPDATE statement.
SalesAgent Region SalesAmount ------------------------------ ---------- -------------------------- John Smith West 78931.01 Mary Johnson West 8723412.61 Doris Bean West 2000111.67 Martin Derrick West 120834.81 Don Olson West 508921.48
Report 4: HectorSales records prior to the UPDATE statement
SalesAgent Region SalesAmount ------------------------------ ---------- -------------------------- John Smith West 100000.00 Mary Johnson West 100000.00 Doris Bean West 2000111.67 Martin Derrick West 120834.81 Don Olson West 508921.48
Report 5: HectorSales records after to the UPDATE statement
By reviewing the results in Listing 5 you can see the UPDATE statement updated the SalesAmount valuesfor John Smith and Mary Johnson. If you compare the updated rows to their original rows prior to being updated you can see that the SalesAgent valuess that had the highest SalesAmount and the lowest SalesAmount in the West region were the ones that got updated. This isn’t what I wanted. The reason this happened is because my UDPATE statement randomly updated my table for the records that had a region value of “West”.
Since I really want to restrict the number of UPDATE statements using the TOP clause and I wanted to make sure that I updated records based on the SalesAmount, I need to write my code differently, like I have done in Listing 7.
USE tempdb; GO --Update using TOP clause based on SalesAmount UPDATE dbo.HectorSales SET SalesAmount = 6666666.00 FROM (SELECT TOP(2) SalesAgent FROM dbo.HectorSales WHERE Region = 'West' ORDER BY SalesAmount DESC) TS WHERE dbo.HectorSales.SalesAgent = TS.SalesAgent -- Records after Update SELECT * FROM dbo.HectorSales WHERE Region = 'West';
Listing 7: UPDATE statement with TOP clause that orders data
When I run the code in Listing 7 I get the output in Report 6.
SalesAgent Region SalesAmount ------------------------------ ---------- -------------------------- John Smith West 100000.00 Mary Johnson West 100000.00 Doris Bean West 6666666.00 Martin Derrick West 120834.81 Don Olson West 6666666.00
Report 6: HectorSales records after to the UPDATE statement with ORDER BY
By reviewing the output in Report 6 and comparing column values for SalesAgent Doris Bean and Don Olson, with the column values in Report 5 for the same SalesAgent valuess, you will see that these two SalesAgent values have the top sales for the West region. The query in Listing 7 accomplished this by creating a subquery that contained the top two SalesAgents values, which was represented by the TS alias. The SalesAgent values returned by this subquery were then used in the WHERE clause to constrain the UPDATE statement so only those two SalesAgent values got the updated SalesAmount to 6666666.00.
USING the TOP clause with an INSERT Statement
There may be times when you want to insert the top subset of result rows from a query into a table. This can be accomplished by using the TOP clause in conjunction with an INSERT statement. Care needs to be taken where you place the TOP clause, or you might not insert the records you are expecting.
For my example I want to INSERT the top two SalesAgent values by SalesAmount into a table named dbo.TopTwoSales agent. Listing 8 has my first attempt at meeting my requirements.
USE tempdb; GO CREATE TABLE dbo.TopTwoHectorSales ( SalesAgent varchar(30), Region varchar(10), SalesAmount decimal(10,2)); INSERT TOP(2) INTO dbo.TopTwoHectorSales SELECT * FROM dbo.HectorSales ORDER BY SalesAmount DESC; SELECT * FROM dbo.TopTwoHectorSales;
Listing 8: TOP clause on INSERT statement
SalesAgent Region SalesAmount ------------------------------ ---------- -------------------------- John Smith West 100000.00 Mary Johnson West 100000.00
Report 7: Random Rows inserted into TopTwoHectorSales table
Listing 8 doesn’t exactly meet the requirements stated above. By reviewing the output produced from this query, which can be found in Report 7, you can see that the two rows inserted didn’t contain the top 2 SalesAmount values. This is because when I placed the TOP clause right next to the INSERT clause it told SQL Server to randomly insert two rows from the results of the SELECT subquery. To correctly insert the top two SalesAmount’ values I need to write a SELECT subquery to only return the two rows I want to INSERT, as I have done in Listing 9.
USE tempdb; GO DROP TABLE dbo.TopTwoHectorSales; CREATE TABLE dbo.TopTwoHectorSales ( SalesAgent varchar(30), Region varchar(10), SalesAmount decimal(10,2)); INSERT INTO dbo.TopTwoHectorSales SELECT TOP(2) * FROM dbo.HectorSales ORDER BY SalesAmount DESC; SELECT * FROM dbo.TopTwoHectorSales;
Listing 9: Correctly Inserting Top Two SalesAgent’s into table
SalesAgent Region SalesAmount ------------------------------ ---------- -------------------------- Nick Potts East 9834212.87 Sam Holder East 8723412.61
Report 8: Rows inserted into TopTwoHectorSales table using ORDER BY
By reviewing the output found in Report 8, which was produced by the final SELECT statement in Listing 9, you can see that this INSERT statement placed the correct two rows into the dbo.TopTwoHectorSales table.
USING the TOP clause with an DELETE Statement
You can use the TOP clause on a DELETE statement just like the other statements that manipulate data in a table. Care needs to be taken when you use the TOP clause with just the DELETE statement or you might just randomly delete rows from a table. If you truly want to delete random rows then you can write a TOP clause like I did in Listing 10. When I run the code in Listing 10 it produces the output in Report 9 and 10.
USE tempdb; GO BEGIN TRANSACTION; SELECT * FROM dbo.HectorSales; DELETE TOP (2) FROM dbo.HectorSales WHERE SalesAmount < 6666667.00; SELECT * FROM dbo.HectorSales; ROLLBACK;
Listing 10: Code to randomly delete two rows
SalesAgent Region SalesAmount ------------------------------ ---------- -------------------------- John Smith West 100000.00 Mary Johnson West 100000.00 Doris Bean West 6666666.00 Martin Derrick West 120834.81 Don Olson West 6666666.00 Sam Holder East 8723412.61 Nick Potts East 9834212.87 Lori Morin East 2000111.67 Stan Morris East 4562341.67 Jason Thomas East 13424.51
Report 9: Rows in table prior to running delete statement in Listing 10
SalesAgent Region SalesAmount ------------------------------ ---------- -------------------------- Doris Bean West 6666666.00 Martin Derrick West 120834.81 Don Olson West 6666666.00 Sam Holder East 8723412.61 Nick Potts East 9834212.87 Lori Morin East 2000111.67 Stan Morris East 4562341.67 Jason Thomas East 13424.51
Report 10: Rows in table after delete statement in Listing 10 is run
Report 9 shows all the rows in my dbo.HectorSales data before I run the DELETE statement in Listing 10. Report 10 shows the rows that are left in my table after I delete the top 2 rows. By comparing Report 9 and Report 10 you can see that my DELETE statement removed the two rows that had a SalesAmount of 100000.00. These are not the rows that had the lowest SalesAmount. Based on my requirements I should have deleted only one of the rows that had a SalesAmount of 100000.00 and then deleted the row that had 13424.51 as the SalesAmount. Note I used a ROLLBACK statement in listing 10 so, no rows where actually deleted from my sample table. I did this so the code in the next listing will operate on the same record set as show in Report 9.
If I really want to delete the two rows that have the lowest SalesAmount I would need to use a subquery to select a unique key value that would identify those rows that contained the lowest 2 sales amounts, like I did in Listing 11.
USE tempdb;
GO
BEGIN TRANSACTION;
DELETE FROM dbo.HectorSales
WHERE SalesAgent in (SELECT TOP(2) SalesAgent FROM dbo.HectorSales
ORDER by SalesAmount ASC);
SELECT * FROM dbo.HectorSales;
ROLLBACK;Listing 11: Correctly Inserting Top Two SalesAgent’s into table
SalesAgent Region SalesAmount ------------------------------ ---------- -------------------------- John Smith West 100000.00 Doris Bean West 6666666.00 Martin Derrick West 120834.81 Don Olson West 6666666.00 Sam Holder East 8723412.61 Nick Potts East 9834212.87 Lori Morin East 2000111.67 Stan Morris East 4562341.67
Report 12: Rows inserted into dbo.TopTwoHectorSales table using ORDER BY
If you review the output in Report 12 and compare it with Report 9, you can see the code in Listing 11 deleted the rows for SalesAgent’ values “Jason Thomas” and “Mary Johnson”. These where the two SalesAgent values that had the lowest associated SalesAmount values. Once again I rolled back the changes introduced when I ran the DELETE statement in Listing 11, so my next DELETE statement can operate on the same data as Listing 11.
If you look at the results in Report 12, you can see that SalesAgent “John Smith” has the same low SalesAmount as “Mary Johnson” and it wasn’t deleted with the code in Listing 11. If you also wanted to remove all the records that have the same value as the last record identified by the TOP clause, you need to add the WITH TIES option. If I now run the code in Listing 12, it will have also deleted the dbo.HectorSales record for “John Smith”, in addition to the “Jason Thomas” and “Mary Johnson” record.
DELETE FROM dbo.HectorSales
WHERE SalesAgent in (SELECT TOP(2) WITH TIES SalesAgent FROM dbo.HectorSales
ORDER by SalesAmount ASC);
SELECT * FROM dbo.HectorSales;Listing 12: Correctly Inserting Top Two SalesAgent’s into table
After running this code you can that the SELECT statement showed that both rows that had a SalesAmount of1000000.00 were deleted.
Be Careful When Using TOP Clause in UNION, EXCEPT and INTERSECT Statements:
You need to be careful how you use the TOP clause when your write UNION, UNION ALL, EXCEPT or INTERSECT queries. If your individual queries do not contain the ORDER BY statement you may not get the results you expect. Let me demonstrate.
For this demonstration I will be using the code in Listing 13 to create my test data.
USE tempdb;
CREATE TABLE dbo.Sales (
AgentName varchar(30),
Region varchar(10),
SalesAmount decimal(10,2));
INSERT INTO dbo.Sales VALUES
('John Smith', 'West',1012302.01),
('Mary Johnson', 'West',2453202.89),
('Doris Bean', 'West',99001.43),
('Sam Holder', 'East',8723412.61),
('Nick Potts', 'East',9834212.87),
('Jason Thomas', 'East',13424.51);Listing 13: Sample data for Example
In the table created in listing 13, I have a set of sales records that track the total amount of sales for various sales agents. I want to write a query that returns the sales agent that has sold the most for each region. In order to do this I will be using the TOP clause, the WHERE clause and the UNION operator.
To demonstrate how the TOP clause in a UNION statement may not return the results expected I will run the code in Listing 14.
USE tempdb; GO SELECT TOP(1) AgentName, Region, SalesAmount FROM dbo.Sales WHERE Region = 'East' UNION SELECT TOP(1) AgentName, Region, SalesAmount FROM dbo.Sales WHERE Region = 'West' ORDER BY SalesAmount DESC;
Listing 14: TOP Clause the Returns Unpredictable results
The intent of this code is to return the AgentName value that has the highest associated SalesAmount for each region. But when I run this query it doesn’t return the results I am expecting. The output from the query can be found in Report 13.
AgentName Region SalesAmount ---------------------- ---------- --------------------------------- Sam Holder East 8723412.61 John Smith West 1012302.01
Report 13 Result Set Returned When Running Listing 14
By looking at this output you can see it is ordered by SalesAmount, but the SalesAmount from each region is not the highest. This is because each SELECT statement brought back a random SalesAmount because they didn’t contain an ORDER BY clause. To correctly return the dbo.Sales records for the top agents in each region I need to add an ORDER BY clause to each query as I did in Listing 15.
SELECT AgentName, Region, SalesAmount FROM ( SELECT TOP(1) AgentName, Region, SalesAmount FROM dbo.Sales WHERE Region = 'East' ORDER BY SalesAmount DESC ) East UNION SELECT AgentName, Region, SalesAmount FROM ( SELECT TOP(1) AgentName, Region, SalesAmount FROM dbo.Sales WHERE Region = 'West' ORDER BY SalesAmount DESC ) West ORDER BY SalesAmount DESC;
Listing 15: Correct to code a TOP Clause when used with the UNION operation
If you run the code in Listing 15 you will see the correct result set are returned from the UNION operation.
Best Practices
When using the TOP clause consider the following:
- In order to ensure consistent results always use an ORDER BY clause when using a TOP clause. This will make sure your queries correctly returns the top rows from an ordered set. If you don’t use the ORDER BY clause when using a TOP clause SQL Server returns a random set of rows. This is because a set that is produced without the ORDER BY clause has no order.
- Do not use the TOP clause to perform a windowing or paging function for your application. It is easier to implement a windowing or paging function by using the OFFSET and FETCH options of the ORDER BY clause.
- Make sure you uses an ORDER by clause on each individual SELECT TOP statement when using UNION, UNION ALL, INTERSECT and EXCEPT operators. If you don’t do this then your SELECT statements might return random rows which will give you different results then you expect.
Summary
The TOP clause allows you to return the TOP so many rows from a set. You can identify either the exact number to return or a percentage of rows based on the total rows in the result set. You need to be aware that the TOP clause will operate on a random set of rows if used without an ORDER BY clause. If your business requirement only requires you to return a few records from the beginning of an order set, then the TOP clause will make it easy to meet this requirement.
Question and Answer
In this section you can review how well you have comprehended using the TOP Clause by answering the following questions:
Question 1:
What are the different ways to represent the expression value for the TOP clause when the PERCENT option of the TOP clause is not used? (Pick all that apply)
- As an integer value
- As a text string
- As a decimal value
- As a variable that is declared as an integer data type
- As a variable that is declared as a varchar data type
- As a variable that is declared as a decimal data type
Question 2:
What value does using the WITH TIES option on the TOP clause provide? (Pick all the apply)
- It allows you to return any rows that have the same column values as the other rows based on the ORDER BY columns
- It allows you to return any rows that have the same column values as the last row based on the ORDER BY columns
- It allows you to potentially return more rows then the number of rows specified by the TOP clause provided the additional rows have the same column value as the other rows based on the ORDER BY clause
- It allows you to potentially return more rows then the number of rows specified by the TOP clause provided the additional rows have the same column value as the last row based on the ORDER BY clause
Question 3:
When the ORDER BY clause exists on a SELECT or INSERT statement that uses the TOP clause, SQL Server will always select records from the top of the ordered set? True, or False?
Answers
Question 1:
The correct answer is a, and d. When the PERCENT option is not used SQL Server expects that expression value to be an integer value, which is converted to a BIGINT value for processing. If the PERCENT option was used then you can additionally pass a decimal value to the TOP clause, because SQL Server converts the expression value to a FLOAT data type when the PERCENT option is used. Under no situations can you pass a character string to the TOP clause.
Question 2:
The correct answer is b and d. The WITH TIES option only allows the matching column values with the last row to be selected. This means that potentially you might return more rows than specified on the TOP clause.
Question 3:
The correct answer is FALSE. While it is TRUE that when using an ORDER BY on a TOP clause with a SELECT statement record will select records from the top of the ordered set. But this is not the case always for the INSERT statements (see the results of Listing 8). If you want the INSERT statement to INSERT rows based on an ORDER BY clause you need to place the TOP clause on the SELECT subqueries (see Listing 9).
