Browse DevX
Sign up for e-mail newsletters from DevX


Make SQL Server Respond to an ORDER BY Clause

SQL Server Views don't support the ORDER BY clause, but that shouldn't stop you from bringing order to your data. This article teaches you how to sort the results of a View, despite this small limitation.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

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]] AS selectstatement [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

    Thanks for your registration, follow us on our social networks to keep up-to-date