New SQL Server 2016 Online Alter column

Over the few months i have been playing with SQL Server 2016 new features. Microsoft SQL Server 2016 introduced new alter column with ONLINE option. Let’s explore this. Create a table and insert data    Find what page has been allocated for the above operation. I have used un documented function fn_PhysLocCracker to get the informationContinue reading “New SQL Server 2016 Online Alter column”

New SQL Server 2016 Truncate Table With Partitions

I have a table with different partitions and would like to truncate rows in the particular partitions in a table rather than truncating whole table. How can we do this? This task is fairly easy in sql server 2016. Microsoft introduced a new clause called “With Partitions” in truncate table command. Let’s see how it works. ForContinue reading “New SQL Server 2016 Truncate Table With Partitions”

Database size report for multiple database on multiple servers

As a DBA, you are responsible for monitoring the growth of the databases on multiple servers in different environments. There are many ways we can achieve this, my requirement was to use linked servers to get the databases size of all the linked servers and email the report to DBA group. Here is the script i haveContinue reading “Database size report for multiple database on multiple servers”

Merge Statement to perform insert , update and delete in one statement

This example, demonstrate how to perform insert, update and delete on single statement using MERGE. If you are a Database Developer/BI expert, you would need to refresh the target table to match the source tables periodically. Prior to SQL Server 2008, you would need to perform this task by writing separate T-SQL logic for insert, deleteContinue reading “Merge Statement to perform insert , update and delete in one statement”