T-SQL

Introduction to the sp_executesql stored procedure with examples

January 9, 2020 by Esat Erkec

The sp_executesql is a built-in stored procedure in SQL Server that enables to execute of the dynamically constructed SQL statements or batches. Executing the dynamically constructed SQL batches is a technique used to overcome different issues in SQL programming sometimes. For example, when we want to determine the displayed columns in our reports, this procedure might be a solution option for us. In the simplest sense, this procedure takes a dynamically constructed SQL batch and other parameters, then execute it in the runtime and, finally, it returns the result.

Learn SQL: Foreign Key

January 7, 2020 by Emil Drkusic

In the previous article, we talked about the primary key (PK). In this one, we’ll check what is the foreign key (FK) and how it’s related to the primary key. This is the final step, after creating tables, inserting data, and defining primary keys, to cover all basics related to the database theory. There is much, much more than this, but if you understand these concepts, everything else is just expanding your knowledge around these basics.

An overview of the STRING_AGG function in SQL

January 3, 2020 by Esat Erkec

In this article, we will explore the STRING_AGG function in SQL and we will also reinforce our learning with various examples. STRING_AGG function can be counted as a new member of the SQL aggregation functions and it was announced in SQL Server 2017. STRING_AGG function gathers all expression from rows and then puts it together in these rows expression in order to obtain a string. Additionally, this function takes a separator parameter that allows separating the expressions to be concatenated.

SQL Injection: Introduction and prevention methods in SQL Server

December 25, 2019 by Ben Richardson

A SQL injection attack is one of the most commonly used hacking techniques. It allows hacks to access information from a database that is otherwise not publically accessible.

An overview of SQL String Functions

December 25, 2019 by Hadi Fadlallah

In this article, we will try to give a brief overview of the SQL string functions used in SQL Server and we will provide some examples.

Learn SQL: Primary Key

December 20, 2019 by Emil Drkusic

If you’ve already worked with databases, then you could hardly miss the term – Primary Key (PK). And if you’re reading this series and use it to learn about databases, well, this article should give you a good overview of what the PK really is. Still, if you’re a database expert, maybe you’ll find something new or just refresh your knowledge. So, sit back, relax, and let’s dive into PKs.

How to create a view in SQL Server

December 16, 2019 by Esat Erkec

In this article, we will learn the basics of the view concept in SQL Server and then explore methods to create a view in SQL using T-SQL and SQL Server Management Studio.

Learn SQL: INSERT INTO TABLE

December 12, 2019 by Emil Drkusic

In the previous article, we’ve created two tables, and now we’re ready to use the SQL INSERT INTO TABLE command and populate these tables with data. In order to do so, we’ll prepare statements in Excel and then paste these statements into SQL Server and execute them. We’ll also check the contents of both tables before and after these commands using the SELECT statement. So, let’s start.

An overview of SQL Server data types

December 9, 2019 by Rajendra Gupta

In this article, we will give an overview of various SQL Server data types.

An overview of the SQL Server Update Join

December 5, 2019 by Rajendra Gupta

This article explores the SQL Server Update Join statement in T-SQL for SQL Server.

Learn SQL: CREATE DATABASE & CREATE TABLE Operations

December 5, 2019 by Emil Drkusic

Welcome to the first article in the Learn SQL series. In this part, we’ll start with two essential commands in SQL: Create Database and Create Table. While both are pretty simple, they should be used first before you start working on anything with data (unless you use some template database).

The Table Variable in SQL Server

December 3, 2019 by Esat Erkec

In this article, we will explore the table variable in SQL Server with various examples and we will also discuss some useful tips about the table variables.

SQL Variables: Basics and usage

November 18, 2019 by Esat Erkec

In this article, we will learn the notions and usage details of the SQL variable. In SQL Server, local variables are used to store data during the batch execution period. The local variables can be created for different data types and can also be assigned values. Additionally, variable assigned values can be changed during the execution period. The life cycle of the variable starts from the point where it is declared and has to end at the end of the batch. On the other hand, If a variable is being used in a stored procedure, the scope of the variable is limited to the current stored procedure. In the next sections, we will reinforce this theoretical information with various examples

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.

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.