Replacing SQL Cursors with Alternatives to Avoid Performance Issues

Replacing SQL Cursors with Alternatives to Avoid Performance Issues
Rate this post

In this article, we’ll look at some alternatives to using SQL cursors which can help to avoid performance issues caused by using cursors.

Before discussing the alternatives, let’s review the general concept of SQL cursors.

Quick Overview of SQL Cursors

SQL cursors are primarily used where set-based operations are not applicable and you are required to access data and perform operations one row at a time rather than applying a single set-based operation to an entire object (such as a table or a set of tables).

Read More

How to use SQL Cursors for Special Purposes

How to use SQL Cursors for Special Purposes
5 (100%) 2 votes

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

Running SQL Database Maintenance Tasks Using SQLCMD
5 (100%) 1 vote

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

Basics of Running T-SQL Statements from Command Line using SQLCMD
3.6 (72%) 10 votes

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

Creating and Deploying Multiple Versions of Database through Schema Snapshots
5 (100%) 1 vote

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

Multiple Ways to Remove Duplicates from SQL Tables
3.8 (76%) 5 votes

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

Using DATEADD, DATEDIFF and DATEPART T-SQL Functions in Simple Terms
4.8 (96%) 5 votes

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

Tracking Database Changes Using Working Folder Source Control

Tracking Database Changes Using Working Folder Source Control
4.6 (92.5%) 8 votes

This article talks about a new method to version control a database using a working folder so that historical changes made to the database can be traced back.

Overview

Since this article is based on the new approach to source control a database by overcoming the working folder limitation, it is better to get some basic understanding of the working folder and related things.

Read More

Using Working Folder to Source Control Database

Using Working Folder to Source Control Database
4.1 (82.86%) 7 votes

This article is a walkthrough of how to use the working folder option of source control for managing SQL Server databases.

In this article, I am also underlining some of the benefits and limitations of using a working folder as compared to other available options to use with source control.

Let us discuss some key concepts before delving into the technical details of this article. Read More

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

Simplifying Unit Testing Main Stored Procedure Which Also Calls a Utility Procedure
3.5 (70%) 6 votes

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