one difference b/w CTE and Temp table

CTE can be Updated

Users often confuse CTE with Temp Table but technically they both are different, CTE are like Views and they can be updated just like views.

When you update temp table, it remains in the scope of the temp table and it does not propagate it to the table based on which temp table is built. However, this is not the case when it is about CTE, when you update CTE, it updates underlying table just like view does. Continue reading

Table valued function in sql server and advantages of TVF over Views

Scalar-valued functions return a single value. Table-valued functions return table, thus it can be used as a table in a query.

Function has the same benefits as a view when it comes to data protection enforced via privileges or simplifying a query. However, a Table-Valued Function has at least few advantages:

  • Parameterization, a function can receive parameters so the logic inside the function can be adjusted better than using traditional predicate pushing.
  • Programmability, a view can have certain amount of logic (calculations, case-structures etc.) but it’s still quite column bound so more complex logic is hard or impossible to create.

Continue reading