How to stop the TOP clause being added to query when defining a view

Hi

I've just switched over to SQL2005 Server Management Studio although most of my databases are SQL2000. Apart from noticing that everything takes longer than with Enterprise Manager I have one specific problem.

When I save a view that I have created in the visual toolbox it gets saved with the TOP 100 Percent clause added, even if I take the clause out of the SQL statement it gets put right back in when I save it. This is causing an application which worked fine before to fail with the error "cannot update a view with TOP clause"

Anyone any ideas how I can stop this clause being added?

Many thanks

? Hi Ian, >>Anyone any ideas how I can stop this clause being added? Take out the ORDER BY clause as well. An ORDER BY in a view is only allowed if there is also a TOP clause. Also, the only guaranteed effect the ORDER BY in the view will have is to define what rows to select and what rows to discard for satisfying the TOP requirement. No guarantee is made wrt the order of rows returned, unless you add an ORDER BY to the final SELECT statement you use to query your data. SQL Server 2000 did, usually, return the rows in the order that was specified in the view. New optimizing techniques in SQL Server 2005 have changed that behaviour. -- Hugo Kornelis, SQL Server MVP