Languages and coding

Read SQL Server error logs using the xp_readerrorlog command

November 14, 2019 by Rajendra Gupta

This article explores the xp_readerrorlog command for reading SQL Server error logs using T-SQL.

SQL OFFSET FETCH Feature: Loading Large Volumes of Data Using Limited Resources with SSIS

November 14, 2019 by Hadi Fadlallah

In this article, we illustrate how to use the OFFSET FETCH feature as a solution for loading large volumes of data from a relational database using a machine with limited memory and preventing an out of memory exception. We describe how to load data in batches to avoid placing a large amount of data into memory.

Performing CRUD operations with a Python SQL Library for SQL Server

November 13, 2019 by Ben Richardson

This article covers how to connect a Python application to Microsoft SQL Server using a 3^rd party Python SQL library. The library that we are going to use is called “pyodbc”, which is freely available. We will use “pyodbc” to perform CRUD (Create Read Update and Delete) operations on a Microsoft SQL Server database.

SQL DDL: Getting started with SQL DDL commands in SQL Server

November 8, 2019 by Ben Richardson

This article explains SQL DDL commands in Microsoft SQL Server using a few simple examples.

Manage Unicode Characters in Data Using T-SQL

November 7, 2019 by Jignesh Raiyani

In this article, I’ll provide some useful information to help you understand how to use Unicode in SQL Server and address various compilation problems that arise from the Unicode characters’ text with the help of T-SQL.

Overview of DBCC INPUTBUFFER and sys.dm_exec_input_buffer DMF

November 7, 2019 by Rajendra Gupta

This article gives an overview of the DBCC INPUTBUFFER and its replacement sys.dm_exec_input_buffer system dynamic management function .

SQL While loop: Understanding While loops in SQL Server

November 6, 2019 by Ben Richardson

The SQL While loop is used to repeatedly execute a certain piece of SQL script.

SQL Carriage Returns or Tabs in SQL Server strings

November 4, 2019 by Rajendra Gupta

This article explores inserting SQL carriage return AKA line break and tab in a string along with SSMS behavior while copying data to excel, notepad.

SQL WHILE loop with simple examples

October 25, 2019 by Esat Erkec

SQL WHILE loop provides us with the advantage to execute the SQL statement(s) repeatedly until the specified condition result turn out to be false.

Implementing SQL data types

October 23, 2019 by Bojan Petrovic

This article is about many different SQL data types that we use when working with SQL Server. We will start with a quick overview and go through some stuff like categories of data types, what objects we can work with, and how to create our own custom data types.

Overview of the Collate SQL command

October 22, 2019 by Ranga Babu

In this article, we will review the Collate SQL command. First, let us see what collation in the SQL Server is.

Prepare ZIP or RAR files in SQL Server Using xp_cmdshell (T-SQL)

October 21, 2019 by Jignesh Raiyani

Microsoft SQL Server offers the ability to export data into CSV, Excel, PDF and other formats with the help of xp_cmdshell in T-SQL.

Methods to avoid the SQL divide by zero error

October 21, 2019 by Rajendra Gupta

This article explores the SQL divide by zero error and various methods for eliminating this.

SET QUOTED_IDENTIFIER settings in SQL Server

October 17, 2019 by Rajendra Gupta

In this article, we will explore QUOTED_IDENTIFIER behavior, default value, and comparison with a different value. Set options at connection level control query behavior in SQL Server. The query might behave differently with different set options and their values.

SQL multiple joins for beginners with examples

October 16, 2019 by Esat Erkec

In this article, we will learn the SQL multiple joins concept and reinforce our learnings with pretty simple examples, which are explained with illustrations. In relational databases, data is stored in tables. Without a doubt, and most of the time, we need a result set that is formed combining data from several tables. The joins allow us to combine data from two or more tables so that we are able to join data of the tables so that we can easily retrieve data from multiple tables. You might ask yourself how many different types of join exist in SQL Server. The answer is there are four main types of joins that exist in SQL Server. First of all, we will briefly describe them using Venn diagram illustrations:

An overview of the SQL table variable

October 16, 2019 by Rajendra Gupta

This article explores the SQL Table variables and their usage using different examples.

SQL Lag function overview and examples

October 15, 2019 by Rajendra Gupta

In the article SQL Server Lead function overview and examples, we explored Lead function for performing computational operations on data. This article gives an overview of the SQL Lag function and its comparison with the SQL Lead function.

SSIS XML Destination

October 11, 2019 by Hadi Fadlallah

Until now, Microsoft has not included the XML Destination component in SQL Server Integration Services (SSIS). Many years ago, this component was requested on the Microsoft connect website, but it was closed as “Won’t fix.” For this reason, many workarounds and third-party components were created. In this article, we’ll talk about these components and some of the popular solutions for exporting data to XML using SSIS.

A Walkthrough of SQL Schema

October 9, 2019 by Rajendra Gupta

This article gives an overview of SQL Schema and its usage in SQL Server.

How to rename tables in SQL Server with the sp_rename command

October 7, 2019 by Esat Erkec

In this article, we will learn to rename tables in SQL Server. sp_rename is a built-in stored procedure which helps to rename user tables in the SQL Server. During the software development life cycle, we might be faced with various scenarios that required to rename the existing tables in the database. For example, the tables which are created in the SQL Server might be needed to be renamed, because of the software version upgrades, bug fixing or other various development requirements. These and similar reasons can make renaming tables important. So, in this article, we will particularly focus on how to rename the tables in SQL Server.

Database Level DDL Triggers for Views, Procedures and Functions

October 4, 2019 by Jignesh Raiyani

In this article, we will un-riddle the ways to make use of the data definition language trigger (DDL Trigger), in order to monitor the progressions made to the database programming objects, View, Procedure or Function with a few real-time examples.

SQL Server PRINT and SQL Server RAISERROR statements

October 2, 2019 by Rajendra Gupta

This article explores the SQL Server PRINT statements, and its alternative SQL Server RAISEERROR statements to print messages in a query.

MySQL Query (T-SQL) Tutorial for SQL Server

September 25, 2019 by Jignesh Raiyani

Microsoft SQL Server and MySQL, both are quite well-established database engines. In our role as a DBA, at some given time point we require T-SQL straddling between both database engines unites Select, Insert, Update, Delete and some other…

Truncate Table Operations in SQL Server

September 25, 2019 by Dinesh Asanka

Truncating a table is removing all the records in an entire table or a table partition. TRUNCATE table is functionally similar to DELETE table with no WHERE clause. However, TRUNCATE table is much faster than DELETE with respect to the time and the resource consumptions which we will look at in this article. TRUNCATE statement removes the data by de-allocating the data pages in the table data. This means that TRUNCATE is similar to drop and re-create the table. Also, it records only the page de-allocations in the transaction log, not the row-wise as in DELETE statement.

Database Level DDL Triggers on Tables

September 23, 2019 by Jignesh Raiyani

This article portrays the different utilization of database DDL Triggers for the Tables with a counting mail alert on schema composition using DDL (Data Definition Language) standard. Using that efficient method we can keep close eyes on monitoring schema changes in any database of SQL Server. We’ll also see how we could monitor these movements and send an alert email to persons responsible to inform about this change.