Post reply

Top 100 Percent in View

  • September 4, 2003 at 9:24 pm

    #81099

    A friend of mine with a SQL 7 server has an interesting thing with a view he has created.

    This view is based on two tables and another view. He has fields selected in each table and the view and criteria specified for the view and one of the tables.

    There is no GROUP BY, DISTINCT etc... but the view designer insists on automatically putting TOP 100 PERCENT in the view definition.

    I've had a bit of a look around but couldn't come up with any reasoning for this. Does anyone else have any ideas?

    Hope this helps

    Phill Carter

    --------------------

    Colt 45 - the original point and click interface

    --------------------
    Colt 45 - the original point and click interface

  • September 4, 2003 at 9:46 pm

    #472719

    Hi phillcart

    I think it is just a default set for views in SQL server. Does anyone know if this can be disabled.

    If he put SELECT * (in place of the select top 100 percent) , I dont think it would then put in the top 100 per cent clause?

    Probobly also a good check so you know that everything should be coming out based on criteria !!

    Have a nice day 🙂


    ------------------------------
    Life is far too important to be taken seriously

  • September 5, 2003 at 12:45 am

    #472743

    It's only for this one view. There are about 12 other views in the database and none of them have TOP 100 PERCENT.

    It's not causing any problems, just wanting to understand why its there.

    Hope this helps

    Phill Carter

    --------------------

    Colt 45 - the original point and click interface

    --------------------
    Colt 45 - the original point and click interface

  • September 5, 2003 at 3:43 am

    #472769

    Because you can not use an order by in a view without top 100 percent, give it a try.

    THis will not work

    use northwind

    create view blah as

    select * from customers

    order by customerid

    Wheras this will

    create view blahblah as

    select top 100 percent * from customers

    order by customerid

    HTH

    Ray Higdon MCSE, MCDBA, CCNA

    ------------

    Ray Higdon MCSE, MCDBA, CCNA

  • September 5, 2003 at 8:33 pm

    #472920

    Thanks for that, I'll check if he has ORDER BY in the view.

    Hope this helps

    Phill Carter

    --------------------

    Colt 45 - the original point and click interface

    --------------------
    Colt 45 - the original point and click interface

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply