what are CTE? When and Why To Use CTE in SQL ?

What are CTE in SQL ?

CTE are Common Table Expressions first introduced in Sql Server 2005. It is basically a result set or result holder or separate plugin / extension that can hold some complex query separately from main query and supply the named result set or result holder name in place of that complex query in main query.

It really improves complexity of complex queries. It is specially used in queries that contains complex sub-queries in them. Using them makes our query more concise, improves readability of query and makes it easy for maintainable for long time for future use.

CTE always start with Semicolon preceeded by 'with' keyword and CTE are temporary result holders and are removed form memory after query executed its life is only during query is executing.

When To Use CTE 

CTE is helpful to be used in Below two Conditions -

1.  In order to reduce complexity of query with complex subqueries that will make code easy to read and maintain.
   
2. Very helpful to create Recursive query if required in some cases.

you can read more about recursive queries with CTE on link below -
Read More About How To Use Recursive Query With CTE

Why We use ; ( Semicolon ) in start of CTE

It is actually to ensure previous statement before with / CTE are terminated. Most of times
we use semicolon before with keyword in CTE as we don't know what is before CTE.

Example - Without semicolon may create problem in below code in some StoredProcedure

DECLARE @foo int
WITH OrderedOrders AS
(
    SELECT SalesOrderID, OrderDate from Orders;
)

Above query will create error in Stored Procedure as DECLARE @foo int is not ended with
semicolon and CTE's with keyword is not ended with ; ( Semicolon ). Better way will  to write this query in stored procedure or anywhere is below -


DECLARE @foo int
;WITH OrderedOrders AS
(
    SELECT SalesOrderID, OrderDate from Orders;
)

Now in this case semicolon before WITH Keyword will terminate previous statement first and then execute CTE