How to use SQL Cursors for Special Purposes

This article describes SQL cursors and how to use them for some special purposes. It highlights the importance of SQL cursors along with their downsides.

It is not always the case that you use SQL cursor in database programming, but their conceptual understanding and learning how to use them helps a lot to understand how to perform exceptional tasks in T-SQL programming.

SQL Cursors Overview

Let us go through some basics of SQL cursors if you are not familiar with them.

Read More

Running SQL Database Maintenance Tasks Using SQLCMD

This article is about developing an advanced understanding of the Sqlcmd utility which lets you run T-SQL commands directly from the command prompt without needing SSMS (SQL Server Management Studio).

The article also highlights the importance of using Sqlcmd to perform some advanced-level database tasks that would otherwise require additional steps, e.g. getting connected to the database via a pre-installed database tool such as SSMS (SQL Server Management Studio) or SSDT (SQL Server Data Tools) followed by getting it ready to run SQL scripts against the desired database(s).

The Sqlcmd utility can be a great time saver for Database developers and DBAs since they can run the required SQL scripts right from the command line. Read More

Basics of Running T-SQL Statements from Command Line using SQLCMD

This article is about developing a basic understanding of sqlcmd utility to run T-SQL commands directly from the command prompt without the need of SSMS (SQL Server Management Studio).

The article also highlights the importance of using a lightweight sqlcmd utility to perform some basic database tasks that would otherwise require getting connected to the database through a pre-installed database tool such as SSMS (SQL Server Management Studio) or SSDT (SQL Server Data Tools) followed by getting it ready to run SQL scripts against the desired database(s).

The sqlcmd utility can be a real time-saver for database developers and DBAs since they can straight away run the required SQL scripts from the command-line.

Read More

Creating and Deploying Multiple Versions of Database through Schema Snapshots

Overview

This article talks about using database schema snapshots to maintain different versions of a database to be deployed to different environments.

Database schema snapshots are point-in-time copies of the current state of the database which are normally used to reconcile the differences when deploying changes from one environment to another environment.

This article will be focused on a particular scenario where database schema snapshots are more than just point-in-time copies of the database rather they are used to create fresh versions of specific environments.

Read More

Multiple Ways to Remove Duplicates from SQL Tables

Overview

This article discusses two different approaches available to remove duplicate rows from SQL table(s) which often becomes difficult over time as data grows if this is not done on time.

The presence of duplicate rows is a common issue that SQL developers and testers face from time to time, however, these duplicate rows do fall into a number of different categories that we are going to discuss in this article.

This article focuses on a specific scenario, when data inserted into a database table, leads to the introduction of duplicate records and then we will take a closer look at methods for removing duplicates and finally remove the duplicates using these methods.

Read More

Using DATEADD, DATEDIFF and DATEPART T-SQL Functions in Simple Terms

This article focuses on developing a basic understanding of how to use one of the most common Transact-SQL date functions: DATEADD, DATEDIFF, and DATEPART.

In this article, I also stressed the importance of properly using these date functions in daily date manipulations followed by some interesting scenarios in which these date functions can be used in a collaborative way to solve slightly complex date calculations.

Since these functions are primarily used in date manipulations, let us first try to understand what we mean by date manipulation.

Read More

Simplifying Unit Testing Main Stored Procedure Which Also Calls a Utility Procedure

This article provides a walkthrough of database unit testing a stored procedure which contains a utility procedure within it.

In this article, I am going to discuss a database unit testing scenario when a main stored procedure depends on a utility procedure and the main procedure needs to be unit tested in order to make sure that the requirements are met. The key is to ensure that a unit test can only be written for a single unit of code which means we need one unit test for the main procedure and another unit test for the utility procedure. Read More