Understanding PIVOT, UNPIVOT, and Reverse PIVOT Statements

Understanding PIVOT, UNPIVOT, and Reverse PIVOT Statements
5 (100%) 1 vote

The PIVOT statement is used to convert table rows into columns, while the UNPIVOT operator converts columns back to rows. Reversing a PIVOT statement refers to the process of applying the  UNPIVOT operator to the already PIVOTED dataset in order to retrieve the original dataset.

In this article, we will study these three concepts on different examples.

Read More

SQL Server Triggers: Understanding and Alternatives

SQL Server Triggers: Understanding and Alternatives
4.7 (93.33%) 12 votes

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. Read More

Introducing Common Table Expressions in SQL Server

Introducing Common Table Expressions in SQL Server
Rate this post

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

Dynamic T-SQL and Benefits of its Usage

Dynamic T-SQL and Benefits of its Usage
Rate this post

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.

Read More

USE HINT and DISABLE_OPTIMIZED_NESTED_LOOP

USE HINT and DISABLE_OPTIMIZED_NESTED_LOOP
Rate this post

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.

Read More