Don’t like database triggers? You just don’t know how to work with them!

Total: 2 Average: 4.5

When designing large relational databases, we often make a decision to diverge from a normal form, i.e. denormalization.

The reasons for this can be different, such as an attempt to speed up access to the specified data, constraints of the used platform/framework/development tools, and lack of skills of a database developer/designer.

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

Introduction to Temporary Tables in SQL Server

Total: 192 Average: 4.2

A temporary table in SQL Server, as the name suggests, is a database table that exists on the database server temporarily. It stores a subset of the normal table data for a certain period of time.

Temporary tables are particularly useful when you have a large number of records in a table and need to interact with small subsets of those records constantly. In such cases, instead of filtering the data again and again to fetch the subset, you can filter the data once, and store it in a temporary table. Then, you can execute queries on that temporary table.

Temporary tables are stored inside “tempdb,” which is a system database. Now, let’s take a look at how you can use temporary data in a simple scenario.

Read More