Using Custum Table Expressions

builck

17y3.3kLearn iOS Programming

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

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Roei Bar17y ago
Accepted 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"
0
Kirtan Patel17y ago
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 :)
0
builck17y ago
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
0
Kirtan Patel17y ago

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 .

0