4
Reply

What are the Advantages of using CTE in sql server?

Deepak  Kamboj

Deepak Kamboj

11y
7.3k
0
Reply

    Common table expression (CTE) is used for the temporary storage of filter data for processing .We can use temp table to temporary data which not cost effective. Another case views are more expensive than CTE.

    Advantages of using common table expression (CTE) http://www.codesolution.org/?p=529

    A CTE can be used to:Create a recursive query. For more information, see Recursive Queries Using Common Table Expressions.Substitute for a view when the general use of a view is not required; that is, you do not have to store the definition in metadata.Enable grouping by a column that is derived from a scalar subselect, or a function that is either not deterministic or has external access.Reference the resulting table multiple times in the same statement.Using a CTE offers the advantages of improved readability and ease in maintenance of complex queries. The query can be divided into separate, simple, logical building blocks. These simple blocks can then be used to build more complex, interim CTEs until the final result set is generated.CTEs can be defined in user-defined routines, such as functions, stored procedures, triggers, or views.

    http://careerride.com/SQL-Server-what-is-CTE-and-advantages.aspx