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

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

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

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

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

Analytic Functions in SQL Server

Total: 10 Average: 4.8

Aggregate and Analytic functions in SQL Server operate on a set of rows. However, unlike such aggregate functions as sum, count and average that return scalar values, analytic functions return a group of rows that can be further analyzed. In this article, we will see some of the most commonly used analytic functions in SQL server. We will be discussing the following functions:

  • CUME_DIST
  • FIRST_VALUE
  • LAST_VALUE
  • LEAD
  • LAG

Read More