Thursday, December 18, 2003

Is sorting possible in Views?

As we all know, for sorting the values while fetching records we need to make use of ORDER BY clause.

That said, would the below code snippet work?


CREATE VIEW SampleView 
AS 
SELECT Sno, Firstname 
FROM employeeTable 
ORDER BY Firstname
GO


Nope it won't!!!

The work around is, we need to use "TOP 100 PERCENT" in our query if we want to use ORDER BY clause in a View (or) a Subquery (or) a derived table. (BTW this is possible in SQL Server 7.0 and SQL Server 2000).

See below the modified code snippet:


CREATE VIEW SampleView 
AS 
SELECT 
TOP 100 PERCENT Sno, 
Firstname
FROM
employeeTable 
ORDER BY Firstname

2 comments:

An innocents thought said...

Hi Vadivel,iam a frequent reader of your blogs. Is there any way to determine what are all the procedures that are updating or inserting records to a particular table.

Vadivel said...

Right click on the table and check out the View Dependency option. It would list all its dependencies.