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:

    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.

    devx-admin

    devx-admin

    Share the Post:
    Battery Breakthrough

    Electric Vehicle Battery Breakthrough

    The prices of lithium-ion batteries have seen a considerable reduction, with the cost per kilowatt-hour dipping under $100 for the first occasion in two years,

    Economy Act Soars

    Virginia’s Clean Economy Act Soars Ahead

    Virginia has made significant strides towards achieving its short-term carbon-free objectives as outlined in the Clean Economy Act of 2020. Currently, about 44,000 megawatts (MW)

    Renewable Storage Innovation

    Innovative Energy Storage Solutions

    The Department of Energy recently revealed a significant investment of $325 million in advanced battery technologies to store excess renewable energy produced by solar and

    Development Project

    Thrilling East Windsor Mixed-Use Development

    Real estate developer James Cormier, in collaboration with a partnership, has purchased 137 acres of land in Connecticut for $1.15 million with the intention of

    USA Companies

    Top Software Development Companies in USA

    Navigating the tech landscape to find the right partner is crucial yet challenging. This article offers a comparative glimpse into the top software development companies

    Battery Breakthrough

    Electric Vehicle Battery Breakthrough

    The prices of lithium-ion batteries have seen a considerable reduction, with the cost per kilowatt-hour dipping under $100 for the first occasion in two years, as reported by energy analytics

    Economy Act Soars

    Virginia’s Clean Economy Act Soars Ahead

    Virginia has made significant strides towards achieving its short-term carbon-free objectives as outlined in the Clean Economy Act of 2020. Currently, about 44,000 megawatts (MW) of wind, solar, and energy

    Renewable Storage Innovation

    Innovative Energy Storage Solutions

    The Department of Energy recently revealed a significant investment of $325 million in advanced battery technologies to store excess renewable energy produced by solar and wind sources. This funding will

    Renesas Tech Revolution

    Revolutionizing India’s Tech Sector with Renesas

    Tushar Sharma, a semiconductor engineer at Renesas Electronics, met with Indian Prime Minister Narendra Modi to discuss the company’s support for India’s “Make in India” initiative. This initiative focuses on

    Development Project

    Thrilling East Windsor Mixed-Use Development

    Real estate developer James Cormier, in collaboration with a partnership, has purchased 137 acres of land in Connecticut for $1.15 million with the intention of constructing residential and commercial buildings.

    USA Companies

    Top Software Development Companies in USA

    Navigating the tech landscape to find the right partner is crucial yet challenging. This article offers a comparative glimpse into the top software development companies in the USA. Through a

    Software Development

    Top Software Development Companies

    Looking for the best in software development? Our list of Top Software Development Companies is your gateway to finding the right tech partner. Dive in and explore the leaders in

    India Web Development

    Top Web Development Companies in India

    In the digital race, the right web development partner is your winning edge. Dive into our curated list of top web development companies in India, and kickstart your journey to

    USA Web Development

    Top Web Development Companies in USA

    Looking for the best web development companies in the USA? We’ve got you covered! Check out our top 10 picks to find the right partner for your online project. Your

    Clean Energy Adoption

    Inside Michigan’s Clean Energy Revolution

    Democratic state legislators in Michigan continue to discuss and debate clean energy legislation in the hopes of establishing a comprehensive clean energy strategy for the state. A Senate committee meeting

    Chips Act Revolution

    European Chips Act: What is it?

    In response to the intensifying worldwide technology competition, Europe has unveiled the long-awaited European Chips Act. This daring legislative proposal aims to fortify Europe’s semiconductor supply chain and enhance its

    Revolutionized Low-Code

    You Should Use Low-Code Platforms for Apps

    As the demand for rapid software development increases, low-code platforms have emerged as a popular choice among developers for their ability to build applications with minimal coding. These platforms not

    Cybersecurity Strategy

    Five Powerful Strategies to Bolster Your Cybersecurity

    In today’s increasingly digital landscape, businesses of all sizes must prioritize cyber security measures to defend against potential dangers. Cyber security professionals suggest five simple technological strategies to help companies

    Global Layoffs

    Tech Layoffs Are Getting Worse Globally

    Since the start of 2023, the global technology sector has experienced a significant rise in layoffs, with over 236,000 workers being let go by 1,019 tech firms, as per data

    Huawei Electric Dazzle

    Huawei Dazzles with Electric Vehicles and Wireless Earbuds

    During a prominent unveiling event, Huawei, the Chinese telecommunications powerhouse, kept quiet about its enigmatic new 5G phone and alleged cutting-edge chip development. Instead, Huawei astounded the audience by presenting

    Cybersecurity Banking Revolution

    Digital Banking Needs Cybersecurity

    The banking, financial, and insurance (BFSI) sectors are pioneers in digital transformation, using web applications and application programming interfaces (APIs) to provide seamless services to customers around the world. Rising

    FinTech Leadership

    Terry Clune’s Fintech Empire

    Over the past 30 years, Terry Clune has built a remarkable business empire, with CluneTech at the helm. The CEO and Founder has successfully created eight fintech firms, attracting renowned

    The Role Of AI Within A Web Design Agency?

    In the digital age, the role of Artificial Intelligence (AI) in web design is rapidly evolving, transitioning from a futuristic concept to practical tools used in design, coding, content writing

    Generative AI Revolution

    Is Generative AI the Next Internet?

    The increasing demand for Generative AI models has led to a surge in its adoption across diverse sectors, with healthcare, automotive, and financial services being among the top beneficiaries. These

    Microsoft Laptop

    The New Surface Laptop Studio 2 Is Nuts

    The Surface Laptop Studio 2 is a dynamic and robust all-in-one laptop designed for creators and professionals alike. It features a 14.4″ touchscreen and a cutting-edge design that is over

    5G Innovations

    GPU-Accelerated 5G in Japan

    NTT DOCOMO, a global telecommunications giant, is set to break new ground in the industry as it prepares to launch a GPU-accelerated 5G network in Japan. This innovative approach will

    AI Ethics

    AI Journalism: Balancing Integrity and Innovation

    An op-ed, produced using Microsoft’s Bing Chat AI software, recently appeared in the St. Louis Post-Dispatch, discussing the potential concerns surrounding the employment of artificial intelligence (AI) in journalism. These

    Savings Extravaganza

    Big Deal Days Extravaganza

    The highly awaited Big Deal Days event for October 2023 is nearly here, scheduled for the 10th and 11th. Similar to the previous year, this autumn sale has already created

    Cisco Splunk Deal

    Cisco Splunk Deal Sparks Tech Acquisition Frenzy

    Cisco’s recent massive purchase of Splunk, an AI-powered cybersecurity firm, for $28 billion signals a potential boost in tech deals after a year of subdued mergers and acquisitions in the