I am creating Employee table in sql server:
CREATE TABLE Employee(
Id BIGINT IDENTITY PRIMARY KEY,
Name VARCHAR(50),
Location VARCHAR(20)
)
If we will create view
1.
CREATE VIEW vwEmployee
AS(
SELECT Id,Name
FROM Employee
WHERE Location = 'USA'
ORDER BY Name
)
Or we are writing select query either using common table expression or form derived table :
2.
WITH cteEmployee
AS(
SELECT Id,Name
FROM Employee
WHERE Location = 'USA'
ORDER BY Name
)SELECT * FROM cteEmployee
3.
SELECT * FROM(
SELECT Id,Name
FROM Employee
WHERE Location = 'USA'
ORDER BY Name
) AS DerivedTable
We will get error message like:
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.
Cause: It is due to ORDER BY clause.
Solution: If we want to use ORDER BY clause in view, cte or derived table we must have to user either TOP clause or FOR XML clause
Alternative to write above quires are:
1.
CREATE VIEW vwEmployee
AS(
SELECT TOP(100) PERCENT Id,Name
FROM Employee
WHERE Location = 'USA'
ORDER BY Name
)
2.
WITH cteEmployee
AS(
SELECT TOP(100) PERCENT Id,Name
FROM Employee
WHERE Location = 'USA'
ORDER BY Name
)SELECT * FROM cteEmployee
3.
SELECT Col FROM(
SELECT Id,Name
FROM Employee
WHERE Location = 'USA'
ORDER BY Name
FOR XML AUTO
) AS DerivedTable(Col)
Where is the exact reason?
ReplyDeleteYou have explained only work-around for OREDR BY.
;-)
Exact reason is it is meaningless to specify order by clause inside views, inline functions, derived tables, subqueries, and common table expressions since we can override this order by outer order by clause that is why sql server has restricted this.
Delete