In this article, we will discover some best practices of T-SQL queries. Badly written queries can cause performance and I/O problems. For this reason, we should pay attention to keep some rules in our mind when writing T-SQL queries.
The SQL Server trigger is a special type of stored procedures that is automatically executed when an event occurs in a specific database server. SQL Server provides us with two main types of triggers: the DML Triggers and the DDL triggers. The DDL triggers will be fired in response to different Data Definition Language (DDL) events, such as executing CREATE, ALTER, DROP, GRANT, DENY, and REVOKE T-SQL statements. The DDL trigger can respond to the DDL actions by preventing these changes from affecting the database, perform another action in response to these DDL actions or recording these changes that are executed against the database. (more…)
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.
In our projects, we have to cope with different tasks. To solve some of them, we use dynamic T-SQL.
Why do we need dynamic T-SQL? Well, it is up to you.
In one of the projects, we have solved the task of building dynamic reports, and in others — data migration. Dynamic T-SQL is essential when you need to create, modify, get data or objects, but values or names come as parameters. For sure, it may seem unreasonable. Still, such tasks are possible. Later in the article, we will see several examples.
One of the available algorithms to join two tables together in SQL Server is Nested Loops. The nested loops join uses one join input as the outer input table and one as the inner input table. The outer loop iterates the outer input table row by row. The inner loop, executed for each outer row, searches for matching rows in the inner input table.
This article is an introduction to the query hint overview that appeared in SQL Server 2016 SP1.
There are many different thoughts on using hints in queries: some people say that you should never use them, while others insist on using hints if anything works incorrectly.