How to backup large databases

In an ideal world, hard drives and other hardware never fail, software is never defective, users do not make mistakes, and hackers are never successful. However, we live in a less than perfect world and we should plan and prepare to handle adverse events. In todays topic, I will focus on best practices for backing up large mission critical databases. Performing and maintaining good backups is one of the top priority for any DBA/Developer/Engineer working with... Read more

Write Queries Faster using T-SQL Snippets

T-SQL Snippets allow you to quickly build T-SQL statements without having to remember the commands or its syntax. This helps in reduced development time and in increased productivity for developers and DBAs. Snippet templates were introduced in SQL Server 2012 and are based on XML with predefined fields and values. When you use T-SQL snippet, fields are highlighted and the user can tab through each fields and change the values as required. Imagine a series of... Read more

Track DML Changes Without Triggers

SQL Server supports an OUTPUT clause as a part of DML statements that can help you in tracking changes made during any DML operation. The OUTPUT clause can save the result set in a table or table variable. The functionality is similar to what triggers had with INSERTED and DELETED tables which accesses the rows that have been modified during DML operation. Let’s take an example of changing the address by reversing it’s original value: Let’s... Read more

Increase Performance and Prevent Page Splits

Let’s say you have a glass filled with water and you try to put more water in that glass, what happens? Water in the glass will overflow. Exactly the same way, whenever a new row is added to a full index page, SQL Server moves around half of the rows to a new page to make room for the new row. This is known as PAGE SPLIT. Page splits can make room for new records but can be very resource intensive. Page-Splits can also incur fragmentation which may adversely... Read more