how to

Estimate Data Compression Savings in SQL Server

by

SQL Server has a system stored procedure called sp_estimate_data_compression_savings, which allows you to check an object’s size and its estimated size with various levels of compression.

If the object is already compressed, you can use this procedure to estimate its size when recompressed.

Objects can be compressed by using row, page, columnstore or columnstore archive compression.

Compression can be evaluated for whole tables or parts of tables. This includes heaps, clustered indexes, nonclustered indexes, columnstore indexes, indexed views, and table and index partitions.

Read more

How to Execute a Trigger Only When a Specific Column is Updated (SQL Server)

by

In SQL Server, you can create DML triggers that execute code only when a specific column is updated.

The trigger still fires, but you can test whether or not a specific column was updated, and then run code only if that column was updated.

You can do this by using the UPDATE() function inside your trigger. This function accepts the column name as its argument. It returns a boolean.

Read more

Send Email from a Trigger in SQL Server (T-SQL)

by

If you find yourself needing to send an email automatically upon certain events occurring in SQL Server, you can do this via a trigger.

For example, you could automatically send an email when somebody deletes or updates a record from a table, etc.

To do this, you need to create a trigger that includes code for sending the email upon the required event.

Read more

Create an “Instead Of” Trigger in SQL Server

by

When you create a trigger in SQL Server, you have the option of firing it in conjunction with the triggering statement (i.e. the SQL statement that fired the trigger), or firing it instead of that statement.

To fire the trigger instead of the triggering statement, use INSTEAD OF argument.

This is in contrast to using the FOR or AFTER arguments. When you use those arguments, the trigger fires only when all operations specified in the triggering SQL statement have launched successfully.

Read more

Create a DML Trigger in SQL Server

by

In SQL Server, you can use the CREATE TRIGGER statement to create a trigger.

A trigger is a special type of stored procedure that automatically runs when an event occurs in the database server.

You can create a DML trigger, a DDL trigger, or a logon trigger.

This article provides an example of creating a DML trigger.

Read more

Create a Partitioned Table in SQL Server (T-SQL)

by

SQL Server supports partitioned tables and indexes. When a partitioned table or index is partitioned, its data is divided into units that can be spread across more than one filegroup.

Therefore, to create a partitioned table in SQL Server, you first need to create the filegroup/s that will hold each partition. You also need to create a partition function and a partition scheme.

So it goes like this:

  1. Create filegroup/s
  2. Create a partition function
  3. Create a partition scheme
  4. Create the partitioned table

Below is an example of using these steps to create a table with four partitions.

Read more