CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
kmp
kmp

Reputation: 10865

Is it better to order a view than the query calling it?

I am using SQL Server 2008 and I have a situation where I have view that within my application code I always order by a date contained therein, so I was wondering about efficiency etc.

I came across this blog post SQL Server - Order a View that explains that it is possible to order a view so instead of:

CREATE VIEW v
AS
    SELECT a,b,d FROM t
GO
SELECT a,b,d FROM v ORDER BY d

I could do:

CREATE VIEW v
AS
    SELECT TOP(100) PERCENT a,b,d FROM t ORDER BY d
GO
SELECT a,b,d FROM v

and get the same result.

My question is, which of the above is the more efficient?

If I want it to be as fast as possible would I be better off with the second approach or does it make no difference and sql server will work it all out the same regardless?

Upvotes: 1

Views: 96

Answers (3)

Diego
Diego

Reputation: 36156

complementing Remus answer, if you have a view v like your that contains:

SELECT a,b,d FROM t

once you do

SELECT a,b,d FROM v ORDER BY d

you are actually doing

SELECT a,b,d FROM t ORDER BY d

because a regular (non-indexed) view exists only as s SQL statement on the DB, so once SQL sees select * from view, it replaces with the view definition before generating the execution plan that will be run

Upvotes: 1

user359040
user359040

Reputation:

I would expect performance to be pretty similar from both of these - if anything, the select from the simpler version of the view might be slightly faster, since the two queries are essentially identical, but the more complex version of the query includes TOP N processing as well as sorting.

The surest way to find the answer is to try it and see.

Upvotes: 0

Remus Rusanu
Remus Rusanu

Reputation: 294317

This is a moot point:

The ORDER BY clause is not valid in views, inline functions, derived tables, and subqueries

The trick of using TOP(100) PERCENT is a bad practice. Trying to enforce order by adding ORDER BY to a view is really barking up the wrong tree. If you need ORDER BY, ask for it in the query. end of story.

Upvotes: 5

Related Questions