So a month ago in an non critical application I set up a trigger for a stored procedure to copy records (about 3000) from one table to another 6 times a day.
Approximately 38 days in and I have 100% reliability.
I have firewalled the required IP addresses listed by Microsoft
Azure Logic Apps list of IPs for Firewalls
I will keep running this for testing purposes to identify the reliability and report back
A common table expression (CTE) is a temporary result set that you can reference within a
SELECT, INSERT, UPDATE, DELETE, or
CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and exists only for the duration of the query.
I’m finding them useful as you can use them within a view to contain all the references required for that particular view. This I feel makes things more maintainable.
An example of a simple common table expression (CTE) in T-SQL:
WITH CTE_EmployeeSalaries AS
SELECT EmployeeID, Salary
WHERE Salary > 50000
This CTE defines a virtual table named CTE_EmployeeSalaries that contains all employees with a salary greater than 50,000. The CTE is then used to select all rows from this virtual table.
Common table expressions (CTEs) have several advantages:
1.Improved readability: can make complex queries easier to read and understand by breaking them up into smaller, more manageable pieces.
2.Improved maintainability: Because CTEs are self-contained, they can be easily modified or replaced without affecting the rest of the query.
3.Improved performance: In some cases can improve query performance by reducing the need for subqueries.
4.Recursive queries: CTEs can be used to perform recursive queries, which are useful for querying hierarchical data such as an organizational chart or a bill of materials.
5.Temporary results: can be used to store intermediate results that are used multiple times within a single query. This can be more efficient than repeatedly calculating the same intermediate results.
6.Better alternative to view: CTEs can be used as an alternative to views, which can be difficult to modify and are not always optimized for performance.
7.Reusable: CTEs can be defined once and used multiple times within a single query or in multiple queries within the same session.
In TSQL, comments can be added to code in two ways:
1.Single-line comments: These start with two hyphens (–) and continue until the end of the line. For example:
-- This is a single-line comment
2.Multi-line comments: These start with a forward slash and an asterisk (/) and continue until the closing asterisk and forward slash (/). For example:
This is a multi-line comment. It can span
multiple lines and is often used to provide
detailed explanations or to comment out large
blocks of code.
When writing comments, it’s important to keep them clear and concise, and to use them to explain why something is being done rather than how it is being done. This helps to make your code more readable and easier to understand for other developers who may be reading it.