Can we use ORDER BY clause in SQL Server view?

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

🧩 1. Create Sample Table and Insert Data

-- Step 1: Create a sample table

CREATE TABLE Employees (     EmployeeID INT PRIMARY KEY,     EmployeeName NVARCHAR(100),     Department NVARCHAR(100),     Salary DECIMAL(10,2) );  -- Step 2: Insert sample data INSERT INTO Employees (EmployeeID, EmployeeName, Department, Salary) VALUES (1, 'John Doe', 'HR', 60000), (2, 'Jane Smith', 'IT', 80000), (3, 'Mike Johnson', 'Finance', 75000), (4, 'Emily Davis', 'IT', 82000), (5, 'Robert Brown', 'HR', 58000); 

✅ Now we have a simple Employees table with data.

🧩 2. Try Creating a View With ORDER BY ❌ (Invalid)

-- This will throw an error!

CREATE VIEW vw_EmployeesOrdered AS SELECT EmployeeID, EmployeeName, Department, Salary FROM Employees ORDER BY Salary DESC; 

❌ Error:

"ORDER BY is not allowed in views unless FOR XML, OFFSET, or TOP is also specified."

🧩 3. Correct Way: Use TOP 100 PERCENT ✅

You can trick SQL Server by using TOP 100 PERCENT:

-- Correct View Creation

CREATE VIEW vw_EmployeesOrdered AS SELECT TOP 100 PERCENT EmployeeID, EmployeeName, Department, Salary FROM Employees ORDER BY Salary DESC; 

✅ Now the view will be created successfully!

🧩 4. Important Point ⚡

Even though the ORDER BY is present inside the view definition, when you SELECT from the view, SQL Server doesn't guarantee the result order unless you order it again.

-- Selecting from the view

SELECT * FROM vw_EmployeesOrdered; 

👉 To guarantee the order when querying the view:

SELECT * FROM vw_EmployeesOrdered ORDER BY Salary DESC; 

✅ Best practice: Always use ORDER BY outside the view when fetching.

🧩 5. Another Way: Using FOR XML (Rarely Used for This)

CREATE VIEW vw_EmployeesForXML AS SELECT EmployeeID, EmployeeName, Department, Salary FROM Employees ORDER BY Salary DESC FOR XML RAW; 

(Note: FOR XML changes the output format to XML, so this is not commonly used for just ordering rows.)