Tag: sql

Replacing SQL Cursors with Alternatives to Avoid Performance Issues

Haroon Ashraf
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

SQL Server CRUD Operations – Part 2

Esat Erkeç
SQL Server CRUD Operations – Part 2
4.5 (90%) 2 votes

CRUD word is an acronym of the following essential database functions.

In the first part of CRUD article series, we mentioned the concept of the CRUD operations and then discussed the INSERT operation with examples. In this part, we will continue exploring the CRUD operations and will focus on the read, update and delete operation details of CRUD. Read More

Advanced SQL: CROSS APPLY and OUTER APPLY

Nisarg Upadhyay
Advanced SQL: CROSS APPLY and OUTER APPLY
5 (100%) 2 votes

In this article, we’ll look into the “APPLY” operator and its variations – CROSS APPLY and OUTER APPLY along with examples of how they can be used.

In particular, we will learn:

  • the difference between CROSS APPLY and the JOIN clause
  • how to join the output of SQL queries with table-evaluated functions
  • how to identify performance issues by querying dynamic management views and dynamic management functions.

Read More

How to use SQL Cursors for Special Purposes

Haroon Ashraf
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

Concatenation in Transact-SQL

Kenneth Igiri
Concatenation in Transact-SQL
5 (100%) 1 vote

Introduction

Assuming you maintain a table containing customer data, and your boss asks you to send him the current list of customers and their phone numbers. You would typically extract the data and send him a spreadsheet with rows and columns. You could also decide to be a bit stylish and send him the required information in a more people-friendly format. SQL Server provides functions that allow us to achieve this by combining English expressions with data within tables to present an output that is easier for non-technical people to read. These functions can also be put to more subtle uses.

Read More

Running SQL Database Maintenance Tasks Using SQLCMD

Haroon Ashraf
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

Transferring Jobs and Schedules between Instances using T-SQL

Evgeniy Gribkov
Transferring Jobs and Schedules between Instances using T-SQL
Rate this post

Introduction

Quite often, there is a need to transfer Agent jobs to a different instance of MS SQL Server. Restoring a msdb database will not always be the best decision – there are cases in which you will need to transfer only the Agent jobs specifically, and these cases are not uncommon. Also, this wouldn’t work if the recipient MS SQL Server instance is of a newer version than the one you’re transferring the jobs from. So, how can you transfer the Agent jobs without restoring the msdb database?

We will look at an example of a T-SQL script which copies the Agent jobs from one instance of MS SQL Server to another. This solution was tested by transferring jobs from MS SQL Server 2012-2016 to MS SQL Server 2017.

Read More

SQL Server Bulk Insert – Part 2

Esat Erkeç
SQL Server Bulk Insert – Part 2
Rate this post

In the previous part of this article, we discussed how to import CSV files to SQL Server with the help of BULK INSERT statement. We discussed the main methodology of bulk insert process and also the details of BATCHSIZE and MAXERRORS options in scenarios.  In this part, we will go through some other options (FIRE_TRIGGERS, CHECK_CONSTRAINTS and TABLOCK) of bulk insert process in various scenarios.

Read More

Automatic Deletion of Forgotten Transactions in MS SQL Server

Evgeniy Gribkov
Automatic Deletion of Forgotten Transactions in MS SQL Server
4 (80%) 4 votes

Introduction

It is often the case when an MS SQL Server transaction is being forgotten by the initiator. The best example would be the following: a script is executed in SSMS which, via the ‘begin tran’ instruction, starts a transaction and an error occurs; however, ‘commit’ or ‘rollback’ do not go through and the execution initiator has left this query for a long time. As a result, more and more fluctuation appears when it comes to blocking the queries which request access to closed-off resources (tables and server resources such as RAM, CPU and the input-output system).

In this article, we’ll look at one of the ways you can automate the forgotten transaction deletion process.

Read More

Advanced SQL: Insert output of the parameterized table-valued function in SQL table

Nisarg Upadhyay
Advanced SQL: Insert output of the parameterized table-valued function in SQL table
3 (60%) 3 votes

In this article, I am going to demonstrate the following:

  1. How to insert the output of a table-valued function in a SQL table.
  2. How to insert the output of a table-valued function which is created on the remote database server.

What is “Insert into” statement

In RDBMS, “Insert into” is one of the basic SQL statements. It is used to insert new records in a SQL table. Using the statement, we can perform the following tasks:

  • Insert new records in a table (Basic Insert).
  • Insert values of a specific column in a table.
  • Insert the output generated by a stored procedure in a SQL table. Read More