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

Seven Secrets in SSMS for Developers

Total: 1 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

SQL Server Inner Join Basics with Examples

Total: 1 Average: 5

Introduction

T-SQL allows us to combine records from more than one table and return them as a single result set. This is achieved through the concept of joins in SQL Server.

This opportunity is often necessary because data in relational databases are typically normalized. For example, we have employee data spread across two or more tables. The first table would be the basic customer data and called employee. The second table would be the department.

The data consistency requires the correct relationship between the customer and the department. Returning the complete data for a set of employees and their departments requires to join both tables.

Read More

Deploying a Database on Azure SQL

Total: 3 Average: 5

Introduction

Azure SQL Database is a Platform as a Service (PaaS) offered by Microsoft Azure. Hence, you need the Azure subscription to use this SQL Database. Azure offers new subscribers a free subscription and $200 worth of resources for 30 days. Recently I revived my relationship with MS Azure and explored the platform a little bit, using the Warner Chaves’ PluralSight Azure SQL Database for SQL Server DBAs training for the start.

In this article, I will explore the steps required to create an SQL Server in Azure and deploy a small database. Here I am using a free tier subscription, and the resources are on the basic level.  Further, I’ll explain these terms.

Read More

Deploying a Certificate for Encrypted Connection SQL Server

Total: 3 Average: 5

Introduction

Last year we got a requirement to ensure encrypted connections to our SQL Server instances. Before, we did not think it was necessary – all our instances were accessed by application services internally. Still, secure connections protect the instance and clients from attacks as man-in-the middles, so we did it.

Connection encryption differs from Transparent Data Encryption, but you need a certificate in both cases. In this article, we describe the procedure of setting up an encrypted connection for SQL Server instances.

Read More

How to Configure Database Mail in SQL Server

Total: 3 Average: 5

INTRODUCTION

Notifications are usual in most modern systems. SQL Server implemented means of sending notifications by email early with the feature called SQL Mail. In SQL Server 2005, Database Mail was released, and we still have Database Mail working in current versions of SQL Server. Given this background, we can say that Database Mail is a very mature feature in SQL Server.  

I faced some use cases related to the Database Mail deployment in my experience.

Read More

Restoring the SQL Server Master Database

Total: 2 Average: 5

INTRODUCTION

The master database contains records of the structure/configuration for both the current instance and all other databases. When you run sp_configure, you are writing data to the master database.  It also contains most of the dynamic management views that are necessary to monitor the instance.

The importance of the master database is crucial. First, it has the information necessary for opening all other databases and has to be opened first. Then, it involves all instance level principals for the current instance.

Read More

SQL Server System Databases – Basic Concepts

Total: 1 Average: 5

Introduction

The SQL Server design implies a one-to-many mapping between the database engine (instance) and the databases hosted on the instance. It means that you can deploy several databases on one instance of the SQL server. According to the Microsoft documentation, you can have up to 32767 databases on a single instance of SQL Server. Of course, there will be limitations, like the resources on the server, managing concurrency on TempDB, network traffic, etc.

Databases deployed on a SQL Server instance can either be System Databases or User Databases. System Databases come installed with the instance. In this article, we will discuss the purpose of each System database. Also, we’ll clarify what you need to care for when managing system databases on SQL Server.

Read More