Configure SQL Jobs in SQL Server using T-SQL

Total: 1 Average: 5

SQL Server Agent is a component used for the database tasks automation. For instance, we need to perform Index maintenance on Production servers during the non-business hours only. So, we create a SQL Server job of running index maintenance and schedule it for “off” hours.

When we install SQL Server, the SQL Server Agent service is disabled. First, we enable it and start it manually. Then, we configure the SQL Server job, using SQL Server Management Studio and the system stored procedures of the MSDB database.

This article explains how to create a SQL Server Job using the system stored procedures of the MSDB database.

Read More

Learn Basic Data Analysis with SQL Window Functions

Total: 10 Average: 3.8

This article is about T-SQL (Transact-SQL) Window functions and their basic use in day-to-day data analysis tasks.

There are many alternatives to T-SQL when it comes to data analysis. However, when improvements over time and introduction of Window functions are considered, T-SQL is capable of performing data analysis on a basic level and, in some cases, even beyond that. Read More

SQL Server Triggers: Understanding and Alternatives

Total: 77 Average: 4.1

SQL Server triggers types

The SQL Server trigger is a particular type of stored procedures – it runs automatically when an event occurs in a specific database server. There are two main types of triggers: DML Triggers and DDL triggers.

DML triggers

SQL Server DML Triggers are specific stored procedures designed to react to Data Manipulation Language (DML) events by performing a sequence of actions on a database table, to which the trigger is attached.

DML events are INSERT, UPDATE, or DELETE actions, which occur to modify the database tables’ content or views. Here, the DML triggers fire, no matter if the table rows get affected by those events. Though triggers belong to stored procedures, they have particular differences. The critical one is that triggers always work automatically when the predefined data modification occurs.

DML triggers apply to the business rules and data integrity enforcement, tables queries, and complex T-SQL statements. It is essential to note that the trigger and the statement provoking that trigger are a single transaction. The transaction will not get committed completely until the successful completion of a trigger statement. If the triggering statement fails, the transaction rolls back. For instance, a severe error rolls back the entire transaction automatically.

Note also that the special virtual tables type called Inserted and Deleted tables get into work when the trigger fires. We use these tables to keep the data values before and after the modification.

DML triggers types

We distinguish two types of DML triggers:

  • AFTER or FOR trigger
  • INSTEAD OF trigger.

The AFTER trigger executes after performing INSERT, UPDATE, or DELETE actions. It is essential to note the following aspects:

  • Any referential cascade actions and constraint checks should succeed before firing the trigger.
  • It is possible to define the AFTER trigger on the table level only, not on views.

The INSTEAD OF trigger allows you to INSERT, UPDATE, or DELETE data from views that reference data from multiple tables. Besides, it is possible to reject a part of a batch query and execute another part of that batch successfully.

  • The INSTEAD OF trigger overrides the statement of the action that fired it, with the statement provided in the trigger. Thus, it rolls back that statement after raising an error. It is helpful in case when someone tries to perform an action breaching a specific policy (changing critical financial columns or audit tables, etc.).
  • It is impossible to use the INSTEAD OF with updatable views, having WITH CHECK OPTION, and in tables with referential relationships, specifying cascade actions on DELETE or UPDATE.

DDL triggers

DDL triggers respond to numerous Data Definition Language (DDL) events. Such events mainly are T-SQL statements CREATE, ALTER, UPDATE STATISTICS, GRANT, DROP DENY, and REVOKE. Also, some system stored procedures can launch DDL triggers if they perform DDL-like operations.

DDL triggers apply to several cases, like:

  • Preventing changes to database schemas.
  • Setting the database response to the database schema change.
  • Database schema changes or events recording.

Logon triggers

Logon triggers are specific stored procedures responding to a LOGON event. In plain words, LOGON events take place when an instance of the SQL server establishes a user session. The trigger fires at the moment when the authentication phase completes successfully, but the user session is not yet established. If authentication is unsuccessful, the Logon trigger won’t fire.

These triggers are helpful for server sessions’ audit and control. They allow tracking login activity and restricting the number of sessions or logins to the SQL server for some specific users.

Now, let’s proceed to the practical usage of triggers in different situations.

Creating a trigger in SQL Server

AFTER… DML Trigger

Assume that we need to track DML actions performed on a specific table, and write these logs in a history table, where the ID of the inserted, updated, or deleted record and the action that is performed will be written to the history table.

The CREATE TABLE T-SQL statements below can be used to create both the source and history tables:

CREATE TABLE TriggerDemo_Parent
(
   ID INT IDENTITY (1,1) PRIMARY KEY,
   Emp_First_name VARCHAR (50),
   Emp_Last_name VARCHAR (50),
   Emp_Salary INT 
  )
GO

CREATE TABLE TriggerDemo_History
(
   ID INT IDENTITY (1,1) PRIMARY KEY,
   ParentID INT,
   PerformedAction VARCHAR (50),
  )
GO

To track the INSERT operation, we create a DML trigger that will fire after performing an INSERT operation on the parent table. This trigger will retrieve the last inserted ID value to that parent table from the virtual inserted table, as in the CREATE TRIGGER T-SQL statement below:

CREATE TRIGGER AfterInsertTrigger
ON TriggerDemo_Parent
AFTER INSERT
AS
INSERT INTO TriggerDemo_History VALUES ((SELECT TOP 1  inserted.ID FROM inserted), 'Insert')
GO

To track the DELETE operation, we create a DML trigger that fires after performing the DELETE operation on the parent table. Again, the trigger will retrieve the last deleted record ID value from the parent table of the virtual deleted table, as in the CREATE TRIGGER T-SQL statement below:

CREATE TRIGGER AfterDeleteTrigger
ON TriggerDemo_Parent
AFTER DELETE
AS
INSERT INTO TriggerDemo_History VALUES ((SELECT TOP 1  deleted.ID FROM deleted), 'Delete')
GO

Finally, we track the UPDATE operation by creating a DML trigger that fires after performing an UPDATE operation on the parent table.

Within this trigger, we retrieve the last updated ID value from the parent table of the virtual inserted table. Note that the UPDATE process is performed by deleting the record and inserting a new record with the updated values. See the CREATE TRIGGER T-SQL statement below:

CREATE TRIGGER AfterUPDATETrigger
ON TriggerDemo_Parent
AFTER UPDATE
AS
INSERT INTO TriggerDemo_History VALUES ((SELECT TOP 1  inserted.ID FROM inserted), 'UPDATE')
GO

Now, the tables and the triggers are ready for testing.

Try to insert a new record into the parent table using the INSERT INTO T-SQL statement below:

INSERT INTO TriggerDemo_Parent VALUES ('AAA','BBB',500)

Then, by checking the execution plan generated by executing the previous INSERT statement, you see that two insert operations’ performance will affect two tables. The first table is the parent table with the values specified in the INSERT statement. The second one is the history table. These tables get affected because of the AFTER INSERT trigger firing, as shown in the execution plan below:

Execution plan

It is also clear when you check the data inserted into both the parent and history tables using the SELECT statements below:

SELECT * FROM TriggerDemo_Parent
GO
SELECT * FROM TriggerDemo_History

The values specified in the INSERT statement will be inserted into the parent table, and the “insert” log containing the inserted record ID and the operation performed, will be inserted into the history table, as below:

History table

Now, try to update an existing record in the parent table using the UPDATE T-SQL statement below:

UPDATE TriggerDemo_Parent SET Emp_Salary=550 WHERE ID=1

Check the execution plan generated by the previous UPDATE statement execution. There, you will see that the update operation will be followed by an insert operation affecting two different tables: the parent table will be updated with the value specified in the UPDATE statement, and the insert operation into the history table due to firing the AFTER UPDATE trigger, as shown in the execution plan below:

Execution plan

Check both the parent and the history table records using the SELECT statements below:

SELECT * FROM TriggerDemo_Parent
GO
SELECT * FROM TriggerDemo_History

You will see that the update statement will modify the Emp_Salary value in the parent table with the value specified in the UPDATE statement. The update log containing the ID of the updated record and the operation performed will be inserted into the history table, as shown below:

Result grid

In the last scenario of the AFTER DML trigger, we track the existing record deletion from the parent table using the DELETE T-SQL statement below:

DELETE FROM  TriggerDemo_Parent WHERE ID=1

Check the execution plan generated by the previous DELETE statement execution. We see the DELETE operation followed by the insert operation, affecting two different tables: the parent table from which the record with the provided ID in the WHERE clause of the DELETE statement will be deleted and the insert operation into the history table due to firing the AFTER DELETE trigger, as shown in the execution plan below:

Execution plan

Check both the parent and history table records using the SELECT statements below:

SELECT * FROM TriggerDemo_Parent
GO
SELECT * FROM TriggerDemo_History

The record with the ID value equal to 1 was deleted from the parent table provided in the DELETE statement, and the delete log containing the ID of the deleted record and the operation performed will be inserted into the history table, as shown in the result below:

Result grid

INSTEAD OF… DML Trigger

The second type of DML trigger is the INSTEAD OF DML trigger. As mentioned previously, the INSTEAD OF trigger overrides the statement of the action that fires the trigger with the statement provided in the trigger.

Assume that we need to log the DML actions that users are trying to perform on a specific table, without allowing them to perform that action. The CREATE TABLE T-SQL statements below can create both the source and alternative tables:

CREATE TABLE TriggerDemo_NewParent
(
   ID INT IDENTITY (1,1) PRIMARY KEY,
   Emp_First_name VARCHAR (50),
   Emp_Last_name VARCHAR (50),
   Emp_Salary INT 
  )
GO


CREATE TABLE TriggerDemo_InsteadParent
(
   ID INT IDENTITY (1,1) PRIMARY KEY,
   ParentID INT,
   PerformedAction VARCHAR (50),
  )
GO

After creating the two tables, we insert a single record into the source table for our demo using the INSERT INTO statement below:

INSERT INTO TriggerDemo_NewParent VALUES ('AA','BB', 500)

For this demo, we create three triggers to override the INSERT, UPDATE, and DELETE operations.

The first trigger prevents any insert operation on the parent table and the log that changes into the alternative table. This trigger is created using the CREATE TRIGGER T-SQL statement below:

CREATE TRIGGER InsteadOfInsertTrigger
ON TriggerDemo_NewParent
INSTEAD OF INSERT
AS
INSERT INTO TriggerDemo_InsteadParent VALUES ((SELECT TOP 1  inserted.ID FROM inserted), 'Trying to Insert new ID')
GO

The second trigger prevents any update operation on the parent table and the log that changes into the alternative table. This trigger is created as below:

CREATE TRIGGER InsteadOfUpdateTrigger
ON TriggerDemo_NewParent
INSTEAD OF UPDATE
AS
INSERT INTO TriggerDemo_InsteadParent VALUES ((SELECT TOP 1  inserted.ID FROM inserted), 'Trying to Update an existing ID')
GO

The last trigger prevents any delete operation on the parent table and the log that changes into the alternative table. This trigger is created as follows:

CREATE TRIGGER InsteadOfDeleteTrigger
ON TriggerDemo_NewParent
INSTEAD OF DELETE
AS
INSERT INTO TriggerDemo_InsteadParent VALUES ((SELECT TOP 1  inserted.ID FROM inserted), 'Trying to Delete an existing ID')
GO

These two tables and three triggers are ready.

Try to insert a new value into the parent table using the INSERT INTO T-SQL statement below:

INSERT INTO TriggerDemo_NewParent VALUES ('CCC','DDD',500)

Then check both the parent and the alternative table records using the SELECT statements below:

SELECT * FROM TriggerDemo_NewParent
GO
SELECT * FROM TriggerDemo_InsteadParent

As we have the INSTEAD OF INSERT trigger in the parent table, the result shows no new record inserted into the parent table. The log for the insert operation is inserted into the alternative table, as below:

Result grid

Try to update an existing record in the parent table using the UPDATE T-SQL statement below:

UPDATE TriggerDemo_NewParent SET Emp_Salary=550 WHERE ID=1

Then check both the parent and the alternative table records using the SELECT statements below:

SELECT * FROM TriggerDemo_NewParent
GO
SELECT * FROM TriggerDemo_InsteadParent

The result displays that the Emp_Salary value of the record with the ID value equal to 1 from the parent table will not be changed. The log for the update operation is inserted into the alternative table due to having the INSTEAD OF UPDATE trigger in the parent table, as below:

Result grid

Finally, try to delete an existing record from the parent table using the DELETE T-SQL statement below:

DELETE FROM  TriggerDemo_NewParent  WHERE ID=1

Check both the parent and the alternative table records using the SELECT statements below:

SELECT * FROM TriggerDemo_NewParent
GO
SELECT * FROM TriggerDemo_InsteadParent

The results prove that the record with the ID value equal to 1 from the parent table will not be deleted. The log for the delete operation is inserted into the alternative table due to having the INSTEAD OF DELETE trigger in the parent table, as shown in the result below:

Result grid

AFTER… DML Trigger with Messages

The AFTER trigger can also raise a warning message for a user. In this case, the query will be an informational message not preventing the executing of the statement that fires that trigger.

Let us drop the previously created INSTEAD OF UPDATE trigger and replace it with another AFTER UPDATE trigger that will raise a warning error after performing any update operation using the DROP/CREATE TRIGGER T-SQL statements below:

DROP TRIGGER InsteadOfUpdateTrigger
CREATE TRIGGER ReminderTrigger  
ON TriggerDemo_NewParent  
AFTER  UPDATE   
AS RAISERROR ('An Update is performed on the TriggerDemo_NewParent table', 16, 10);  
GO  

Try to update the Emp_Salary value of the employee with the ID value equal to 1 using the UPDATE statement below:

UPDATE TriggerDemo_NewParent SET Emp_Salary=550 WHERE ID=1

An error message appears in the Messages tab, containing the message provided in the created trigger, as shown below:

Error message

Check the parent table data using the SELECT statement below:

SELECT * FROM TriggerDemo_NewParent

The result shows that Emp_Salary is updated successfully:

Result grid

If you need the AFTER UPDATE trigger to stop the update operation after raising the error message, add the ROLLBACK statement to the trigger. It will roll back the update operation that fired that trigger, recalling that the trigger and the statement that fires the trigger are executed in the same transaction.

This can be achieved using the ALTER TRIGGER T-SQL statement, see:

ALTER TRIGGER ReminderTrigger  
ON TriggerDemo_NewParent  
AFTER  UPDATE   
AS RAISERROR ('An Update is performed on the TriggerDemo_NewParent table', 16, 10);  
ROLLBACK
GO  

Try to update the Emp_Salary value of the employee with the ID equal to 1 using the UPDATE statement below:

UPDATE TriggerDemo_NewParent SET Emp_Salary=700 WHERE ID=1

Again, an error message appears in the Messages tab. But this time, the update operation rolls back completely, as in the error messages below:

Error message

Check the value from the source table using the SELECT statement below:

SELECT * FROM TriggerDemo_NewParent

You see that the Emp_Salary value has not changed, as the AFTER UPDATE trigger rolled back the overall transaction after raising the error message, as shown in the table result below:

Result grid

Trigger Disadvantages

With all advantages of SQL Server triggers, they have a number of cons too. The most apparent problem is that any trigger increases the database complexity. In case of bad design or being overused, the trigger can provoke performance issues. It can block sessions, extend the transaction lives too much, etc. Also, the following problems are quite common:

  1. It can be problematic to locate these triggers, as they are invisible to the client. You need to document all these triggers thoroughly. Otherwise, the information may be lost, resulting in annoying failures. For instance, the table might include triggers that disallow certain actions, while you can’t detect them and define why your attempts fail.
  2. The DML statements overhead are also caused by triggers. In case the statement has a trigger, you have to execute both this statement and a trigger, and the trigger must be executed and completed successfully before the statement. In production, it causes excessive problems.
  3. Triggers ignore the circumstances – they fire automatically. If you run some audit and need to modify the data, triggers might disallow it. Besides, as we already mentioned, you might be unaware of the trigger presence. The excessive number of triggers complicates troubleshooting and debugging.

There are many more cases when using SQL server triggers leads to troubles. In such cases, alternatives to triggers serve better.

Trigger Alternatives

If triggers are harming your SQL Server instance performance, you have to replace them with other solutions.

Enforce Integrity

For example, rather than use triggers to enforce entity integrity, you can apply the PRIMARY KEY and UNIQUE constraints at the lowest level. The same goes for domain integrity: you can enforce it through CHECK constraints. For referential integrity enforcement, use the FOREIGN KEY constraints. DML triggers are applicable only if the features supported by a specific constraint cannot meet your application requirements.

Let us compare enforcing the domain integrity using DML triggers versus the CHECK constraints. We need to enforce inserting positive values only to the Emp_Salary column.

Start with creating a simple table using the CREATE TABLE T-SQL statement below:

CREATE TABLE EmployeeSalaryTrigger
(
   ID INT IDENTITY (1,1) PRIMARY KEY,
   Emp_First_name VARCHAR (50),
   Emp_Last_name VARCHAR (50),
   Emp_Salary INT 
  )
GO

Define the AFTER INSERT DML trigger that ensures inserting a positive value to the Emp_Salary column. If a user inserts a negative salary value, it will roll back the transaction.

Use the CREATE TRIGGER T-SQL statement:

CREATE TRIGGER TRGR_EmployeeSalary ON EmployeeSalaryTrigger 
AFTER INSERT 
AS
DECLARE @EmpSal AS INT
SET @EmpSal = (SELECT TOP 1 inserted.Emp_Salary FROM inserted)
IF @EmpSal<0
BEGIN 
 RAISERROR  ('Cannot insert negative salary',16,10);
  ROLLBACK
END

For comparison purposes, we create another simple table with the same schema, and define a CHECK constraint within the CREATE TABLE statement: it accepts only positive values in the Emp_Salary column, as shown below:

CREATE TABLE EmployeeSalaryConstraint
(
   ID INT IDENTITY (1,1) PRIMARY KEY,
   Emp_First_name VARCHAR (50),
   Emp_Last_name VARCHAR (50),
   Emp_Salary INT CONSTRAINT EmpSal CHECK (Emp_Salary >=0)
  )
GO

Try to insert the below record that contains negative Emp_Salary value into the first table with a predefined trigger, using the INSERT INTO statement:

INSERT INTO EmployeeSalaryTrigger VALUES('Ali', 'Fadi',-4)
GO

The INSERT statement will fail and raise an error message showing that you can’t insert a negative value to the Emp_Salary column. It will rollback the overall transaction due to having an AFTER INSERT trigger, as shown in the error message below:

Error message

Now, try to insert the same record containing a negative Emp_Salary value into the second table with a predefined CHECK constraint. Use the INSERT INTO statement below:

INSERT INTO EmployeeSalaryConstraint VALUES ('Ali', 'Fadi',-4)

The INSERT statement will fail again and show that you are trying to insert the value conflicting with the CHECK constraint condition, as in the error message below:

Error message

Thus, you see that both the trigger and the CHECK constraint methods achieve the goal. They prevent you from inserting negative Emp_Salary values. But which one is better?

Let us compare the two methods’ performance by checking the execution plan weight for each one.

Review the generated execution plans after executing the two queries. As you see, the trigger method weight is three times the CHECK constraint method weight. See the execution plan comparison below:

Execution plan

Also, to compare the execution time consumed by each one, let us run each one 1000 times using the T-SQL statements below:

INSERT INTO EmployeeSalaryTrigger VALUES('Ali', 'Fadi',-4)
GO 10000  
INSERT INTO EmployeeSalaryConstraint VALUES ('Ali', 'Fadi',-4)
GO 10000 
  • The first method that uses the trigger will take about 31ms for complete execution.
  • The second method that is using the CHECK constraint takes 17ms, which is about 0.5 the time required in the method using the trigger.

The thing is, the trigger extends the transaction life and rolls back the query that fires the trigger. It happens after executing the trigger when an integrity violation is found, causing a performance degradation due to the rollback process.

The case is different with the CHECK constraint, as it does its job before doing any modification in the data. It does not require rollback in the case of violation.

Auditing

Triggers also apply to audit and changes-tracking performed on a specific table. If this auditing method causes performance degradation in your SQL Server instance, you can replace it with the OUTPUT clause.

The OUTPUT clause returns information about each row affected by the INSERT, UPDATE or DELETE operation, as a confirmation message or a value that can be inserted into the historical table. Also, this method provides us with more control over the executed code. It is added to the data insertion, modification, or deletion statements itself whenever you want. When you deal with triggers, they are always executed.

Let us compare logging the data insertion and modification into the history table using DML triggers versus using the OUTPUT clause.

First, we create the production and history tables using the CREATE TABLE T-SQL statement below:

CREATE TABLE TriggerDemo_Prod
(
   ID INT IDENTITY (1,1) PRIMARY KEY,
   Emp_First_name VARCHAR (50),
   Emp_Last_name VARCHAR (50),
   Emp_Salary INT 
  )
GO


CREATE TABLE TriggerDemo_ProdHistory
(
   ID INT IDENTITY (1,1) PRIMARY KEY,
   ProdID INT,
   ProdSalary INT,
   TS DATETIME,
  )
GO

Once we have both tables, we create the AFTER INSERT, UPDATE DML trigger. It will write a record into the history table if any new row is inserted into the production table or an existing record is modified.

Use the CREATE TRIGGER T-SQL statement:

CREATE TRIGGER ProdHistory
ON TriggerDemo_Prod
AFTER INSERT, UPDATE
AS
INSERT INTO TriggerDemo_ProdHistory  VALUES ( (SELECT TOP 1  inserted.ID FROM inserted),(SELECT TOP 1  inserted.Emp_Salary FROM inserted), GETDATE())
GO

To compare logging the changes with the trigger method and the OUTPUT clause, we need to create two new simple tables: the production and the history tables. The schema is the same as for the previous two tables, but this time we don’t define a trigger.

Use the CREATE TABLE T-SQL statements:

CREATE TABLE OutputDemo_Prod
(
   ID INT IDENTITY (1,1) PRIMARY KEY,
   Emp_First_name VARCHAR (50),
   Emp_Last_name VARCHAR (50),
   Emp_Salary INT 
  )
GO


CREATE TABLE OutputDemo_ProdHistory
(
   ID INT IDENTITY (1,1) PRIMARY KEY,
   ProdID INT,
   ProdSalary INT,
   TS DATETIME,
  )
  
GO

Now the four tables are ready for testing. We insert one record into the first production table that has a trigger. Use the INSERT INTO T-SQL statement:

INSERT INTO TriggerDemo_Prod values('AA','BB', 750)
GO 

Then we insert the same record into the second production table using the OUTPUT clause.

The INSERT INTO statement acts as two insert statements. The first one  inserts the same record into the production table, and the second insert statement beside the OUTPUT clause inserts the insertion log into the history table:

INSERT INTO OutputDemo_Prod  OUTPUT inserted.ID, inserted.Emp_Salary, GETDATE() 
INTO OutputDemo_ProdHistory	values('AA','BB', 750)
GO 

Checking the data inserted into four production and history tables, you see that both the trigger and OUTPUT methods write the same log into the history table successfully. It happens in the same way, as below:

Result grid

From the generated execution plans after executing two queries, you see that the trigger method weight is about (21%+36%) 57% of the overall weight. The OUTPUT method weight is about 43%. The weight difference is small, as shown in the execution plans comparison below:

Execution plan

The performance difference is clear when comparing the execution time consumed by each method.

  • Logging the changes using the trigger method consumes (114+125) 239ms to be executed completely.
  • The OUTPUT clause method consumes only 5ms, which is 2% of the time used in the trigger method.

We can see it clearly from the Time Statistics below:

Time statistics

It is clear now that the OUTPUT method is much better for changes auditing than using triggers.

Conclusion

SQL Server triggers are beneficial in many cases. They are similar to stored procedures, thus, they are easy to code. Triggers serve properly for auditing processes, or when you need to ensure certain actions under definite circumstances (update, delete, or insert). However, triggers can complicate the processes too. There are alternative means and the choice of method depends on each particular case.

Useful Links:

Methods to Rank Rows in SQL Server: ROW_NUMBER(), RANK(), DENSE_RANK() and NTILE()

Total: 71 Average: 4.2

SQL Server provides us with many window functions, helping to perform calculations across a set of rows without the need to repeat calls to the database. “Window” has no relations to Microsoft Windows. It is a term for the rows set that the function will process.

SQL Server provides us with many window functions, helping to perform calculations across a set of rows without the need to repeat calls to the database. 

Ranking Window Functions are among the most useful window functions types. They rank specific field values and categorize them according to each row’s rank. As a result, we get a single aggregated value for each participant-row. But unlike the standard aggregate functions, window functions don’t group rows into a single output row. Instead, they return a single aggregated value for each row, keeping separate identities for those rows.

Note that “Window” has no relations to Microsoft Windows. It is a term for the rows set that the function will process.

Read More

Introducing Common Table Expressions in SQL Server

Total: 1 Average: 5

Common Table Expressions, or CTE for short, is simply a technique to create a temporary set of records that can be referenced within an INSERT, SELECT, UPDATE or DELETE statement.

Common table expressions were introduced by Microsoft in SQL Server 2005. They are not stored as objects in the database memory as their lifespan is equal to the execution time of the query. As soon as a query completes they are removed from the database memory. CTE can be referenced in a query as many times as you want and they can also be self-referencing.

Read More