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

Synthetic Data Generation. Part 2: Data Changing

Total: 1 Average: 5

Character Data Change

Here, we take an example for the English and Russian alphabets, but you can do it for any other alphabet. The only condition is that its characters must be present in the NCHAR types.

We need to create a function that accepts the line, replaces every character with a pseudorandom character, and then puts the result together and returns it.

Read More

Create a Database Diagram Using dbForge Database ER Diagram Tool for SQL Server

Total: 3 Average: 5

SQL Server database diagram is a graphical representation of database tables. It visualizes the database structure and allows you to understand the overall database schema and relations. It lets you also manage the database schema from the diagram page by creating and modifying database tables, columns, dependencies, indexes, constraints, and keys.

Read More

Synthetic Data Generation. Part 1: Data Copying

Total: 2 Average: 3.5

Introduction

Sooner or later, any information system gets a database, often – more than one. With time, that database gathers very much data, from several GBs to dozens of TBs. To understand how the functionals will perform with the data volumes increasing, we need to generate the data to fill that database.

All scripts presented and implemented will execute on the JobEmplDB database of a recruiting service. The database realization is available here

Read More

3 Easy and Secure Ways to Make Users Run SSIS Packages

Total: 4 Average: 5

Have you ever come across a situation where you needed to make users run SSIS packages? This happens when the user is not comfortable with a fixed schedule for whatever reason. You can’t define if it’s every weekend, at 9 pm, or the 5th of the month. It happens when it happens.

This sounds problematic, depending on the volume of data. If you know that a million or so records won’t be advisable to process any time of the day, say no. But if the data volume is not a problem, what are your options?

Read More

SQL Server Indexes Management Using Index Manager for SQL Server

Total: 4 Average: 5

SQL Server Index Overview

When talking about SQL Server performance tuning and queries enhancement, the first thing to consider is the SQL Server Index. It serves to accelerate reading data from underlying tables by providing quick access to the requested rows. Thus, it won’t need to scan all the table’s records.

The SQL Server index provides those fast search capabilities due to the B-Tree structure of the index. This structure makes it possible to move through the table rows based on the index key quickly and retrieve the requested records at once. It won’t need to read the whole table.

Read More

Configure SQL Server Log Shipping

Total: 1 Average: 5

This article explains the step by step deployment process of the SQL Server Log shipping. It is the database-level disaster recovery solution that is easy to set up and maintain.

The log shipping involves three steps:

  1. Generate the log backup on the primary database.
  2. Copy the backup to the network location or the specific directory on the secondary server.
  3. Restore the log backup on the secondary server.

The log shipping technology performs the steps described above by using SQL Server agent jobs. During the configuration process, the log shipping wizard creates those jobs on primary and secondary servers.

Read More

Configure SQL Jobs in SQL Server using T-SQL

Total: 1 Average: 5

SQL Server Agent is a component used for the database tasks automation. For instance, we need to perform Index maintenance on Production servers during the non-business hours only. So, we create a SQL Server job of running index maintenance and schedule it for “off” hours.

When we install SQL Server, the SQL Server Agent service is disabled. First, we enable it and start it manually. Then, we configure the SQL Server job, using SQL Server Management Studio and the system stored procedures of the MSDB database.

This article explains how to create a SQL Server Job using the system stored procedures of the MSDB database.

Read More