XML FeedsI saw that some people are hitting our site with a search for how to create a sorted view in SQL Server 2008.
You all know that in SQL Server 2000 you can create a view and use TOP 100 PERCENT with ORDER By and it will be sorted. Since SQL server 2005 that doesn’t work anymore. I actually never understood the need for sorted views to begin with, how hard is it to do something like this
- SELECT *
- FROM View
- ORDER By Column
Not hard, I guess pople want the convenience of opening the view in SSMS and it is sorted ‘correctly’
There is a way to get this to work in SQL server 2005, there is a hotfix that will ‘fix’ this but you have to run in 2000 compatability mode.
The link to the fix is here: FIX: When you query through a view that uses the ORDER BY clause in SQL Server 2008, the result is still returned in random order
Now let’s get started with the code
Create this table
- create table TestSort (id int not null)
- insert TestSort values(1)
- insert TestSort values(3)
- insert TestSort values(4)
- insert TestSort values(5)
- insert TestSort values(2)
- insert TestSort values(7)
- insert TestSort values(9)
- insert TestSort values(6)
And create the view
- create view vTestSort
- as
- select top 100 percent id from TestSort
- order by id
Now do a select from the view
- select * from vTestSort
(result set)
1
3
4
5
2
7
9
6
Oops it is not sorted
Let’s try something else, we will use 99.99 percent
- create view vTestSort2
- as
- select top 99.99 percent id from TestSort
- order by id
Run the select against the view
- select * from vTestSort2
(result set)
1
2
3
4
5
6
7
9
look at that, magic! It works
Let’s try another way by using the max value of an integer
- create view vTestSort3
- as
- select top 2147483648 id from TestSort
- order by id
Run the select against the view
- select * from vTestSort3
(result set)
1
2
3
4
5
6
7
9
And bingo, it also works.
Now, just because this works right now it doesn’t mean that it will work after you apply the next hotfix or service pack. Why not doing this instead
- select * from vTestSort3
- order by id
That will always work and you don’t have to deal with unexpected results down the road
Denis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.
Hi:
>>with VIEWS SQL automatically updates the "virtual" table and the performance is improved.
Great Articles!! You elaborate it very nice and understandable. Here some good articles also helped me in completing my task. Check this helpful link too....
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.