Development

Esat Erkec

Introduction to the sp_executesql stored procedure with examples

January 9, 2020 by

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.

Read more »
Nisarg Upadhyay
Database install script

Install and configure the AdventureWorks2016 sample database

January 9, 2020 by

This article explains the process of installing the AdventureWorks2016 and AdventureWorksDW2016 sample database on a stand-alone instance of SQL Server and Azure SQL Server. The sample databases were published by Microsoft to demonstrate how to design a database using SQL Server. Microsoft has also published another lightweight database named AdventureworksLT, which can be used as a sample database on Azure SQL Server.

Read more »
Esat Erkec

An overview of the STRING_AGG function in SQL

January 3, 2020 by

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.

Read more »
Nisarg Upadhyay
Restrict Access must be Single_User

Send an alert when the database state changes to SQL Server single-user mode

December 18, 2019 by

This article explains the SQL Server single-user mode and how to configure an automated email alert when the state of the user database changes to the single-user mode. To send an alert, we will create a DDL trigger that executes when the user runs the ALTER DATABASE query. It collects the pieces of information using EVENTDATA() function, saves it in a temporary table, and sends an HTML formatted email.

Read more »
Esat Erkec

SQL Variables: Basics and usage

November 18, 2019 by

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 more »
Esat Erkec

SQL multiple joins for beginners with examples

October 16, 2019 by

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:

Read more »