SQL SERVER - How To Use ‘Order By’ Clause In a View

In general, we cannot use 'Order By' clause in a view Definition. If we try to use the 'Order By' clause in a view definition, it will give the following error message

'The ORDER BY clause is invalid in views, inline functions, derived tables, sub queries, and common table expressions, unless TOP or FOR XML is also specified'

In order to solve this, there are two alternatives.

First     One is Using 'Top' keyword in the view definition Second One is Using the 'Order By' Clause in the View Retrieval.

Let us see the First case through an example.

Method 1 : Creating a view using DimEmployee Dimension table with Top keyword

Syntax:

Create View vw_EmpDetails AS
Select Top 100 Percent EmployeeKey,FirstName,LastName,Title,DepartmentName From dbo.DimEmployee
Order By EmployeeKey

Method 2 : Using the 'Order By' Clause directly in the view

As a first step we will create a view with out using the 'TOP' keyword.

Syntax:

Create View vw_EmpDetails AS
Select EmployeeKey,FirstName,LastName,Title,DepartmentName From dbo.DimEmployee

Now, we will use the 'Order By' clause in the Select statement.

Syntax: 

Select * From vw_EmpDetails
Order By EmployeeKey