none
why dont we use ORDER BY clause while creating VIEWS in sql

    Question

  • i am using sql server 2008. I know that we do not use ORDER BY clause when we create views but i want to know the axact reason behind it??? why we cant??
    Wednesday, April 03, 2013 7:35 PM

Answers

  • we do not use ORDER BY clause when we create views

    Because it makes no sense to define a ORDER BY clause in a view. Always the "caller" of a table / view defines the order how he likes to retrieve the result; if a view would have an ORDER BY clause and the caller would like to have the result in a different order, SQL Server would have to order the result twice; and one would be redundant and so it would be inperformat; therefore it makes really no sense.

    Olaf Helper

    Blog Xing

    Wednesday, April 03, 2013 7:40 PM
  • Because it is absurd. A VIEW is a table. Tables are sets. Sets have no ordering. This concept is covered on the first day of any RDBMS class. Not understanding this like being in a geography class and thinking the Earth is flat. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    • Marked as answer by NK Choudhary Saturday, April 06, 2013 4:40 AM
    Wednesday, April 03, 2013 8:29 PM

All replies

  • we do not use ORDER BY clause when we create views

    Because it makes no sense to define a ORDER BY clause in a view. Always the "caller" of a table / view defines the order how he likes to retrieve the result; if a view would have an ORDER BY clause and the caller would like to have the result in a different order, SQL Server would have to order the result twice; and one would be redundant and so it would be inperformat; therefore it makes really no sense.

    Olaf Helper

    Blog Xing

    Wednesday, April 03, 2013 7:40 PM
  • Because it is absurd. A VIEW is a table. Tables are sets. Sets have no ordering. This concept is covered on the first day of any RDBMS class. Not understanding this like being in a geography class and thinking the Earth is flat. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    • Marked as answer by NK Choudhary Saturday, April 06, 2013 4:40 AM
    Wednesday, April 03, 2013 8:29 PM
  • thanx man.........
    Saturday, April 06, 2013 4:41 AM
  • i would like to thank you specially for making me aware about my basic knowledge...... thank you
    Saturday, April 06, 2013 4:43 AM