Make SQL Server Respond to an ORDER BY Clause

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:


    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:
    Heading photo, Metadata.

    What is Metadata?

    What is metadata? Well, It’s an odd concept to wrap your head around. Metadata is essentially the secondary layer of data that tracks details about the “regular” data. The regular

    XDR solutions

    The Benefits of Using XDR Solutions

    Cybercriminals constantly adapt their strategies, developing newer, more powerful, and intelligent ways to attack your network. Since security professionals must innovate as well, more conventional endpoint detection solutions have evolved

    AI is revolutionizing fraud detection

    How AI is Revolutionizing Fraud Detection

    Artificial intelligence – commonly known as AI – means a form of technology with multiple uses. As a result, it has become extremely valuable to a number of businesses across

    AI innovation

    Companies Leading AI Innovation in 2023

    Artificial intelligence (AI) has been transforming industries and revolutionizing business operations. AI’s potential to enhance efficiency and productivity has become crucial to many businesses. As we move into 2023, several

    data fivetran pricing

    Fivetran Pricing Explained

    One of the biggest trends of the 21st century is the massive surge in analytics. Analytics is the process of utilizing data to drive future decision-making. With so much of

    kubernetes logging

    Kubernetes Logging: What You Need to Know

    Kubernetes from Google is one of the most popular open-source and free container management solutions made to make managing and deploying applications easier. It has a solid architecture that makes

    ransomware cyber attack

    Why Is Ransomware Such a Major Threat?

    One of the most significant cyber threats faced by modern organizations is a ransomware attack. Ransomware attacks have grown in both sophistication and frequency over the past few years, forcing