Make SQL Server Respond to an ORDER BY Clause

ometimes, how you present data is just as important as the data itself. One of the simplest presentation techniques is to sort the data in some meaningful way, but SQL Server Views don’t support the ORDER BY clause. In this article, you’ll learn how to sort the results of a View, despite this small limitation.

About Views
The SQL Server View is one of SQL Server’s simpler structures for displaying data. A View basically retrieves a limited recordset. You can retrieve all the data, but you’ll rarely need to do so. Views also are one of SQL Server’s more flexible tools because:

  • They allow users to see limited data while also restricting access to the actual tables, providing a pseudo-layer of security.
  • They enable you to present a simplified version of your data, which means your user really doesn’t need to be familiar with your database structure to use your application.
  • They can increase performance when transferring data across a network.
  • The View does have a few restrictions, however:

  • You can’t use the ORDER BY clause.
  • You can’t use parameters to pass criteria.
  • You can’t use a View with temporary tables.
  • To create a View, use the CREATE VIEW statement in the form (Arguments enclosed in brackets, [ ], are optional.):

    CREATE VIEW [databasename.][owner.]viewname[(column[,n])][WITH attribute[,n]]ASselectstatement[WITH CHECK OPTION]

    The WITH CHECK OPTION clause tracks changes to the data via the View to ensure that the View is still valid once the statement is executed. If a change invalidates the View, this option prohibits the change. Table 1 describes the CREATE VIEW arguments.

    Table 1: CREATE VIEW Arguments

    Argument Explanation
    databasename Identifies the database that contains the data you’re viewing
    owner Identifies the database owner, usually a three-letter prefix such as dbo
    column Identifies the columns from which you’re retrieving data (in selectstatement; generally used only if column is the result of an expression, function, or if two columns have the same name
    attribute One of the following:

  • ENCRYPTION: Encrypts the view definition with the SQL Server system tables
  • SCHEMABINDING: Restricts modifications to the underlying table
  • VIEW_METADATA: Makes the View updateable
  • selectstatement A valid SQL statement that fully defines the View

    The ORDER BY clause sorts the results of a SELECT statement. To apply a sort, add this clause in the form:

    SELECT column[,n]FROM datasourceORDER BY sortcolumn [ASC | DESC]

    Figure 1?
    Figure 1. The data doesn’t sort. (Click to enlarge)

    Where column identifies the columns you’re retrieving, datasource identifies the tables that contain the data, and sortcolumn is the column by which you want to sort the results. By default, the clause sorts in ascending order, but you can sort in ascending or descending order using ASC or DESC. (For documentation purposes, you might want to include ASC, but it isn’t necessary.) You can sort a text, numeric, or date column.

    The Simplest Solution
    Using a View is as simple as creating a SELECT statement. That statement can even further restrict the View’s results, and it even offers a simple solution to the ORDER BY clause limitation because you can include the ORDER BY clause in the calling SELECT statement. To use a view, create a SELECT statement using the following syntax:

    SELECT column[,n]FROM viewname[WHERE criteria]ORDER BY sortcolumn[,n]

    Figure 2?
    Figure 2. The ORDER BY clause will sort the results. (Click to enlarge)

    Where column identifies the columns you’re retrieving, viewname identifies the View from which you’re pulling those columns, criteria is an expression that limits the data, and sortcolumn identifies the column by which you want to sort the results. As you can see, in this form, you can include an ORDER BY clause, but you’re doing so indirectly?from the calling SELECT statement, which may not always be adequate.

    The Northwind sample database that comes with SQL Server can provide a quick example. Suppose you want to see all the products, sorted first by their categories and then by product name. To do so, you might create the following View:

    CREATE VIEW dbo.viewProductsASSELECT dbo.Products.ProductName, dbo.Products.CategoryIDFROM dbo.Products 

    Executing a simple SELECT statement returns the data, but it doesn’t sort it (see Figure 1).

    Call the View using a simple SELECT statement as follows:

    SELECT dbo.viewProducts.ProductName,	dbo.viewProducts.CategoryIDFROM dbo.viewProductsORDER BY dbo.viewProducts.CategoryID, 	dbo.viewProducts.ProductName

    The results aren’t limited by a WHERE clause, and they will include all the records from both the CategoryID and ProductName columns. However, the ORDER BY clause will sort the results, first by CategoryID and then by ProductName (see Figure 2).The previous solution works, but you lose a little control over the sort because you may forget to include the sort when calling the View. A more direct approach is to add the TOP predicate to the View’s SELECT statement in the form:

    CREATE VIEW [databasename.][owner.]viewname[(column[,n])]ASSELECT TOP 100 PERCENT column[,n]FROM datasource[WHERE criteria][ORDER BY sortcolumn[,n]

    Figure 3?
    Figure 3. The View sorts the results. (Click to enlarge)

    In the above context, the TOP predicate selects all the data?100 percent. You can use this predicate to select n percent or n records from the data source.

    Following the previous example, you can add the ORDER BY clause to the View as follows:

    CREATE VIEW dbo.viewProductsTop ASSELECT TOP 100 PERCENT dbo.Products.ProductName, 		dbo.Products.CategoryIDFROM dbo.ProductsORDER BY dbo.Products.CategoryID, dbo.Products.ProductName

    The results show the View sorted by CategoryID and then ProductName (see Figure 3). When calling the View with the following statement, the ORDER BY clause in the calling SELECT statement is no longer needed:

    SELECT dbo.viewProducts.ProductName,		dbo.viewProducts.CategoryIDFROM dbo.viewProductsTop 

    There’s one problem with the TOP 100 PERCENT solution. As is, the View could drop a record from the results. When the last value has two or more records with the same value, the View includes only one. To avoid this problem, add the WITH TIES clause to the TOP statement in the form:

    SELECT TOP 100 PERCENT WITH TIES

    Circumvent the Rules
    Rules are made to be broken. SQL Server doesn’t support the ORDER BY clause in a View, but you’ve seen how to get around that limitation by adding a TOP 100 PERCENT clause to the View. When you do, SQL Server interprets and correctly responds to an ORDER BY clause.

    Share the Post:
    Share on facebook
    Share on twitter
    Share on linkedin

    Overview

    The Latest

    technology leadership

    Why the World Needs More Technology Leadership

    As a fact, technology has touched every single aspect of our lives. And there are some technology giants in today’s world which have been frequently opined to have a strong influence on recent overall technological influence. Moreover, those tech giants have popular technology leaders leading the companies toward achieving greatness.

    iOS app development

    The Future of iOS App Development: Trends to Watch

    When it launched in 2008, the Apple App Store only had 500 apps available. By the first quarter of 2022, the store had about 2.18 million iOS-exclusive apps. Average monthly app releases for the platform reached 34,000 in the first half of 2022, indicating rapid growth in iOS app development.

    microsoft careers

    Top Careers at Microsoft

    Microsoft has gained its position as one of the top companies in the world, and Microsoft careers are flourishing. This multinational company is efficiently developing popular software and computers with other consumer electronics. It is a dream come true for so many people to acquire a high paid, high-prestige job