Using Custum Table Expressions

Question

i am writting a new procedure and i wonder when is it a good idea to use custum table expressions

Roei Bar · Answer

i think what you are asking is when and not how by the way i see you wrote it. well the when is pretty much whenever you need temp tables CTE is actually a table in memory the access time and performace time is a lot better then tempdb. hope this helps if it does dont forget to mark Accept as answer

Kirtan Patel · Answer

mate I m also explaining about that thing CTE it is used to perform complex query like when you use count(*) and want to filter you need to write complex correlated queries to get the the result but using CTE you can get the same result by using Understandable Simple code like consider the following query that uses count(*) WITH Sales_CTE (SalesPersonID NumberOfOrders MaxDate) AS ( SELECT SalesPersonID COUNT (*) MAX (OrderDate) FROM Sales.SalesOrderHeader GROUP BY SalesPersonID ) SELECT E.EmployeeID OS.NumberOfOrders OS.MaxDate E.ManagerID OM.NumberOfOrders OM.MaxDate FROM HumanResources.Employee AS E JOIN Sales_CTE AS OS ON E.EmployeeID = OS.SalesPersonID LEFT OUTER JOIN Sales_CTE AS OM ON E.ManagerID = OM.SalesPersonID ORDER BY E.EmployeeID; When you construct simple Query to achieve the same query gets very complex and with using CTE it make it simple one Hope this will be Help ful to understand the usage of CTE Dont Forget to mark  Do you like Answer  will earn me some credit :) .csharpcode .csharpcode pre { font-size: small; color: black; font-family: Consolas Courier New Courier Monospace; background-color: #ffffff; /*white-space: pre;*/ } .csharpcode pre { margin: 0em; } .csharpcode .rem { color: #008000; } .csharpcode .kwrd { color: #0000ff; } .csharpcode .str { color: #006080; } .csharpcode .op { color: #0000c0; } .csharpcode .preproc { color: #cc6633; } .csharpcode .asp { background-color: #ffff00; } .csharpcode .html { color: #800000; } .csharpcode .attr { color: #ff0000; } .csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .csharpcode .lnum { color: #606060; }

builck · Answer

thx mate but this is not the question i am talking about what microsoft calls CTE here is a sample ;with BlaBla as { select * from bla } select * from BlaBla when should i use this type of select

Kirtan Patel · Answer

when Using scalar subqueries (such as the (COUNT()) cannot be grouped or filtered directly in the containing query. Similarly when using SQL Server 2005's ranking functions - ROW_NUMBER()  RANK()  DENSE_RANK()  and so on - the containing query cannot include a filter or grouping expression to return only a subset of the ranked results. For both of these .