Your Ultimate Guide to SQL Joins: OUTER JOIN – Part 2

Total: 2 Average: 5

Outer join is at the center stage today. And this is part 2 of your ultimate guide to SQL joins. If you missed part 1, here’s the link.

By the looks of it, outer is the opposite of inner. However, if you consider the outer join this way, you’ll be confused. To top that, you don’t have to include the word outer in your syntax explicitly. It’s optional!

But before we dive in, let’s discuss nulls concerning outer joins.

Read More

Views in SQL Server

Total: 2 Average: 5

Introduction

A view in SQL Server is a virtual table-like structure based on the result-set of an SQL statement. On the surface, a view is similar to a table with the signature structure of rows and columns. However, these rows and columns come from tables referenced in the query, which defines the View.

We use Views to focus on the concrete columns for the purposes they are created for. Views may also serve for security reasons. They filter out columns in the underlying tables which one does not want to make visible to certain users. Views filter columns like a WHERE clause filters rows.

Read More

How CTE Can Aid In Writing Complex, Powerful Queries: A Performance Perspective

Total: 3 Average: 5

We often see poorly written complex SQL queries running against a table or tables in databases. Those queries make the time of execution very long and cause consuming huge CPU and other resources. Still, complex queries provide valuable information to the application/person running them in many cases. Therefore, they are useful assets in all varieties of applications.

Read More

Using CASE Expressions in SQL Server

Total: 1 Average: 5

Introduction

CASE Expressions in SQL Server are used for the column values substitution to present the result sets in a particular fashion or simple queries. Use cases for such commands are various.

For instance, there is a column containing the department code, but you wish to display the department’s name rather than the code. You could achieve it by doing a JOIN with another table containing the department details. However, let’s assume you want to keep the query relatively simple. Another use case would be returning specific values for the computed values set. Computed columns would not fit if the sets of conditions to specify are not the same.

Read More

Mastering the Use of Stoplists With SQL Server Full-Text Search (FTS)

Total: 1 Average: 5

Creating and managing stoplists serves as one of the main components responsible for improving the Full-Text Search performance alongside reducing the size of the index. This article aims to help you master the job in-depth and implement multiple strategies to create stoplists on simple but interesting examples.

We’ll also highlight the importance of different methods to generate stoplists and clarify how to choose the most suitable method.

Read More

Your Ultimate Guide to SQL Join: INNER JOIN – Part 1

Total: 3 Average: 5

Inner join, outer join, cross join? What gives?

It’s a valid question. I once saw a Visual Basic code with T-SQL codes embedded in it. The VB code retrieves table records with multiple SELECT statements, one SELECT * per table. Then, it combines multiple result sets into a record set. Absurd?

To the young developers who did it, it was not. But when they asked me to evaluate why the system was slow, that issue was the first to catch my attention. That’s right. They never heard of SQL joins. In fairness to them, they were honest and open to suggestions.

Read More

Synthetic Data Generation. Part 3: Backup and Restore

Total: 1 Average: 5

Implementation of the general script for data sanitization and the secret data changes

We’ve examined simple examples for each type of altered data in the previous articles (Part 1: Data Copying, Part 2: Data Changing):

  1. Changing the date and time;
  2. Changing the numerical value;
  3. Changing the byte sequence;
  4. Changing the characters’ data.

However, the examples described above don’t meet the criteria 2 and 3 for the data altering scripts:

Read More

Seven Secrets in SSMS for Developers

Total: 2 Average: 5

SQL Server Management Studio is Microsoft’s primary client tool for interacting with SQL Server. It is simple, and it has been around for quite a while (launched with SQL Server 2005). Still, it is easy to miss plenty of great features that can make the T-SQL scripts execution and overall interaction with SQL Server more rewarding.

In this article, we shall pay attention to seven of these capabilities. It might seem strange, but they often remain hidden even from experienced administrators who work with SSMS. Let’s set the ball rolling.

Read More

Top Answers to 5 Burning Questions on SQL COALESCE Function

Total: 4 Average: 5

How cool is SQL COALESCE?

It’s cool enough to be so important to me. And I’ll be more than happy to hire a new guy who doesn’t have a bad habit of ignoring the goal of COALESCE. That includes other expressions and functions for handling similar situations.

Today, you will find the answers to the five most-asked questions about SQL COALESCE expression. One of these is being debated over and over again.

Shall we begin?

Read More