Moving SQL Server Table to Different Filegroup

Total: 2 Average: 5

Introduction

A table is a logical structure. When you create a table, you typically would not care which drives it sits on at the storage layer. However, if you are a database administrator, this knowledge may become essential if you need to move certain database portions to alternative storage or volume. Then, you might want definite tables to be on a particular volume or set of disks.

Filegroups in SQL Server offer that abstraction layer allowing us to control the physical location of our logical structures – tables, indexes, etc.

Read More

Fundamentals of Managing Datafiles in SQL Server

Total: 1 Average: 5

Introduction

Datafiles are physical objects that constitute the most important part of the database system since they contain actual data. You can think of a database as a collection of data files. An instance gives you the means of mounting and accessing such files.

Here, managing datafiles is understanding how to monitor and resize existing datafiles and how to add or remove the data files from a database.

Read More

Use Cases for SQL Server MERGE Statement: Syncing Online and History Tables

Total: 1 Average: 5

INTRODUCTION

The SQL Server MERGE statement is an incredibly useful tool for carrying out DML operations based on comparing two tables or two data sets. Usage of this statement is actually like performing multiple operations in a single statement.

This article will explore three use cases that border on ensuring data between an online table and a history table in sync.

Read More

Add Columns to an Existing Table in SQL Server Database

Total: 1 Average: 5

Introduction

A table is a two-dimensional logical structure and the fundamental means of storing data in relational database management systems. Its ‘row and column’ format is very much like the organization of the spreadsheet. 

Each new record introduced to a table is a row (also called a record or tuple), while rows are grouped into a finite set of columns (also called fields or attributes). Each column has a name and data type, and it serves as a reference for displaying result sets when querying data from relational database systems.

In this article, we explore the current options letting us add new columns to an existing table in SQL Server database. The use case may arise on many occasions. Particularly, it happens when there are updates for an application, and they demand adding new columns.

Read More

Views in SQL Server

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

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