Additive, non-additive and semi additive facts in data warehouse fact table

In a data warehouse fact table, fact can be classified as additive, semi additive or non-additive based on their characteristics. Additive facts are measures that can be aggregated by ALL…

Read more »

DROP multiple tables using a single DROP statement

The DROP TABLE command can also be used to drop multiple tables in a single statement. Example, Separate each table with a comma in the DROP TABLE statement to drop…

Read more »

How to Convert Seconds to “HH:MM:SS” format in TSQL

Presenting the time/duration in HH:MM:SS format is important for good readability. There are situations where the duration of an event needs to be calculated between time intervals. The difference can…

Read more »

Synonym in SQL Server

Synonym A synonym is an alias or an alternative name given to the other objects in the database. The alternative name can be used to refer to the underlying objects…

Read more »

Can TRUNCATE be rolled back in SQL Server?

A database transaction, by definition, must be atomic (it must either complete in its entirety or do not affect whatsoever), consistent, isolated, and durable. These four properties are commonly referred…

Read more »

How to find a column is updated.

In application development, there are situations where an action is driven based on a change in data. A trigger can be used to perform an action based on the data…

Read more »

Grant permission to Integration Service Catalog folder

People often confused that having access to SSISDB will also provide access to the Integration Service Catalog folders but permission for the folder is handled separately. The following code can…

Read more »

Add a NOT NULL column to an existing table – Performance improvement SQL Server 2012

Why alter table add NOT NULL column takes long time? Adding a NOT NULL column to the existing table will run for longer time for high volume tables. Because SQL…

Read more »

CHECKPOINT in SQL Server

CHECKPOINT is an internal SQL Server process that runs periodically to write dirty pages (changed pages) and transaction log records from memory to disk. It then marks a point in…

Read more »

Identifying cached tables in SQL Server buffer

When a query is submitted it goes through a sequence of phases in SQL Server. Starting from parse, optimization, plan preparation until the execution of the plan. During the execution…

Read more »