TSQL View with ORDER BY Clause

Views in Microsoft SQL Server are generally not permitted to have an ORDER BY clause. This is a good default behavior because it can negatively impact the view performance and the order should be specified when the final result set is retrieved by the client query against the view.

If you are looking for a workaround to this restriction, there is a trick I’ve seen. The pattern follows this example:

CREATE VIEW My_View AS
SELECT TOP 100 PERCENT
  Col_1
  Col_2
FROM
  Table_1
ORDER BY
  Col_1

In the example, we’re trying to fool TSQL into thinking that the View will be returning a partial result at the top of an ordered result set. The trick is that 100% returns *all* records not just the top partial set. Unfortunately it appears that any query from the view will not receive an ordered result, SQL Server must be smart enough to know this 100 PERCENT trick does not actually limit the result set.

Advertisements

About notesbytom

Keeping technology notes on WordPress.com to free up my mind to solve new problems rather than figuring out the same ones repeatedly :-).
This entry was posted in Database and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s