T-SQL

Emil Drkusic

Learn SQL: How to Write a Complex SELECT Query

February 4, 2020 by

In my career, I’ve heard many times, things like “How to write a complex SELECT query?”, “Where to start from?” or “This query looks so complex. How you’ve learned to write such complex queries?”. While I would like to think of myself as of a brilliant mind or genius or add something like “query magician” to my social network profiles, well, writing complex SQL wouldn’t be the only thing required to do that. Therefore, in this article, I’ll try to cover the “magic” behind writing complex SELECT statements.

Read more »
Jignesh Raiyani

Converting commas or other delimiters to a Table or List in SQL Server using T-SQL

January 31, 2020 by

Database developers often need to convert a comma-separated value or other delimited items into a tabular format. Delimiters include pipe “|”, hash “#”, dollar “$” and other characters. SQL Server compatibility level 130, and subsequent versions, support a string_split() function to convert delimiter-separated values to rows (table format). For compatibility levels under 130, developers have previously done this with a user-defined function, which incorporates a While loop or Cursor to extract the data.

Read more »
Prashanth Jayaram
The trend which proves the SQL is third top used tool

Introduction to SQL

January 29, 2020 by

Today, data is the basis of any business. The world of enterprise computing is the epitome of data-driven businesses. The importance of structured storage of data is unquestionably in the focus. And at this time, with the computing model quickly shifting towards the cloud, and storage costs falling rapidly, enterprises are leveraging data more and more to tune their businesses.

Read more »
Emil Drkusic
aggregate functions - the data model we'll use

Learn SQL: Aggregate Functions

January 28, 2020 by

SQL has many cool features and aggregate functions are definitely one of these features, actually functions. While they are not specific to SQL, they are used often. They are part of the SELECT statement, and this allows us to have all benefits of SELECT (joining tables, filtering only rows and columns we need), combined with the power of these functions.

Read more »
Jignesh Raiyani
Database Collation

SQL Server collation introduction with collate SQL casting

January 27, 2020 by

SQL Server collation refers to a set of character and character encoding rules, and influences how information is stored according to the order in the data page, how data is matched by comparing two columns, and how information is arranged in the T-SQL query statement. Collate SQL follows rules applied on a table when Select, Insert, Update or Delete operations are performed against contained data. Data always follows collation constraint rules, which are configured when creating an object.

Read more »
Emil Drkusic
join multiple tables - tables needed

Learn SQL: Join multiple tables

January 27, 2020 by

If you want to get something meaningful out of data, you’ll almost always need to join multiple tables. In this article, we’ll show how to do that using different types of joins. To achieve that, we’ll combine INNER JOINs and LEFT JOINs. So, let’s start.

Read more »
Bojan Petrovic
CREATE VIEW SQL script for creating a simple view

CREATE VIEW SQL: Creating views in SQL Server

January 23, 2020 by

Introduction

In this article, we are going to see how to use the CREATE VIEW SQL statement to create a view. This is the first article in a series that will be a programmatical approach of creating, altering and working with views using T-SQL. If you are a beginner and you don’t even know what a view is, don’t worry. We will kick off with a definition, and then move on to some basics like syntax, examples, use cases, etc.

Read more »
Emil Drkusic
SQL - types of relations

Learn SQL: Types of relations

January 22, 2020 by

One of the most important things in databases is to understand the types of relations in the databases. That stands for both – a process of designing a database model as well as when you’re analyzing your data. Understanding these relations is somehow natural and not so complex but is still essential in the database theory (and practice).

Read more »
Nisarg Upadhyay
Database Mail configuration: Email with high priority and confidential

Database Mail configuration in SQL Server Express edition

January 21, 2020 by

In this article, I am going to explain step by step process to perform Database Mail configuration in SQL Server Express edition using the T-SQL script. As we know, SQL Server Express edition does not provide the SQL Server Agent Services; hence we cannot perform Database Mail configuration or SQL Jobs or maintenance plans using SQL Server Management Studio. To configure the Database Mail feature in the SQL Server Express edition, we must use CLR integration or using stored procedures within the MSDB database.

Read more »
Emil Drkusic

Learn SQL: SQL Scripts

January 21, 2020 by

I can hardly imagine making any complex database changes without using SQL scripts. We actually used them in this series so far, but we haven’t pointed out what they actually are. Today we’ll stand up against that injustice. Sit back, relax, and get ready to learn something new and useful.

Read more »
Nisarg Upadhyay
Query SQL03 to verify the data

Configuring SQL Server Express edition as SQL witness server in Database Mirroring

January 20, 2020 by

In this article, I am going to explain how we can use the SQL Server Express edition for the SQL witness server of the database mirroring setup. Database mirroring is a cost-effective and easy to configure high availability solution. Unfortunately, this feature has been deprecated, but still, some organizations use database mirroring as a high availability solution.

Read more »
Emil Drkusic

Learn SQL: SELECT statement

January 13, 2020 by

The SELECT statement is probably the most important SQL command. It’s used to return results from our database(s) and no matter how easy that could sound, it could be really very complex. In this article, we’ll give an intro to the SELECT statement and we’ll cover more complex stuff in the upcoming articles.

Read more »
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 »
Emil Drkusic

Learn SQL: Foreign Key

January 7, 2020 by

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.

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 »