Using Database Snapshot for Mirror Server

In the database mirroring environment Principal interacts with all the users but the mirror server only receives transaction log data from the principal server and other than that mirror database doesnt do anything as its in RECOVERING state all the time. In this...

Using Synonyms for Creating Shortcuts

What would you do when an object that is referenced in several places and in several databases (Stored Procedures, Functions, etc.) is being moved to a different database or is being renamed due to a new object naming policy? Think of how much work this change would...

Best Practices for SQL Server Service Account

Securing SQL Server is one of the top priorities of any DBA. It is important for DBA's to make sure that the database server is TIGHTLY SECURED and is not exposed to users that do not need access to SQL Server. Only valid and approved users MUST be granted required...

Using CONCAT Function for String Concatenation

CONCAT() string function allows you to concatenate up to 255 string or variable values in to one single string. It requires a minimum of two input values when calling the function. CONCAT takes care of implicitly converting the data values to a string by following the data type conversion rules of SQL Server. This eliminates the need of explicit data conversions when concatenating two values.

Data Pagination Using OFFSET and FETCH

Let’s assume you have 50,000 records in a table and you want to query 1200 rows starting from 25000. Traditionally you would use a cursor to implement this solution however with the introduction of OFFSET and FETCH its much easier.

Improve Index Performance using MAXDOP

Ever noticed that even though you have a beefy server, SQL Server index operations sometime takes time? There are many things that happen behind the scenes that could contribute to the slowness of index operations. Some of the reasons include: Load on SQL Server Out...

Using SEQUENCE for Incremental Numbers

There are many applications that require incremental numbers for maintaining unique rows in a table. For several years, weve been using IDENTITY() column as our primary option to generate incremental numbers for unique records or for Primary key values and it has been the first choice for developers. Youve tried IDENTITY() and now try the new feature added in SQL Server 2012 and experience the difference.

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...

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...