Ben Richardson

Author
Recent Posts

Ben Richardson runs Acuity Training a leading provider of SQL training the UK. It offers a full range of SQL training from introductory courses through to advanced administration and data warehouse training – see here for more details. Acuity has offices in London and Guildford, Surrey. He also blogs occasionally on Acuity’s blog

View all posts by Ben Richardson

Latest posts by Ben Richardson (see all)

Creating a visualization in Power BI Report View - May 14, 2020
Differences between the M Language and DAX in Power BI - May 14, 2020
Implementing Star Schemas in Power BI Desktop - March 4, 2020

Creating a visualization in Power BI Report View

May 14, 2020 by Ben Richardson

The Report View in Power BI can be used to create beautiful visualizations in Power BI.

Differences between the M Language and DAX in Power BI

May 14, 2020 by Ben Richardson

Microsoft Power BI supports two different languages, M language and DAX (Data Analysis Expression) that can be used to filter, manage, and visualize data.

Implementing Star Schemas in Power BI Desktop

March 4, 2020 by Ben Richardson

In this article, you will see how to implement a star schema in Power BI. Microsoft Power BI is a business analytics tool used to manipulate and analyze data from a variety of sources.

The Power BI Data Model: Working with Relationships

March 4, 2020 by Ben Richardson

In this article, we look at how to create different types of relationships between two or more tables in the Power BI data model.

Getting started with Query Editor in Power BI Desktop

March 3, 2020 by Ben Richardson

Power BI is a data analytics tool developed by Microsoft used to visualize data and find useful insights. In this article, you will see how to work with the Query Editor in Power BI desktop. Power BI comes in various versions, i.e., Power BI Desktop, Power BI Service, Power BI Mobile, and Power BI Developer. Power BI desktop is the free version, and the query editor is available in all three versions.

Using SQL CREATE INDEX to create clustered and non-clustered indexes

January 10, 2020 by Ben Richardson

The SQL CREATE INDEX statement is used to create clustered as well as non-clustered indexes in SQL Server. An index in a database is very similar to an index in a book. A book index may have a list of topics discussed in a book in alphabetical order. Therefore, if you want to search for any specific topic, you simply go to the index, find the page number of the topic, and go to that specific page number. Database indexes are similar and come handy. Particularly, if you have a huge number of records in your database, indexes can speed up the query execution process. There are two major types of indexes in SQL Server: clustered indexes and non-clustered indexes.

Rollback SQL: Rolling back transactions via the ROLLBACK SQL query

December 26, 2019 by Ben Richardson

The rollback SQL statement is used to manually rollback transactions in MS SQL Server.

SQL Injection: Introduction and prevention methods in SQL Server

December 25, 2019 by Ben Richardson

A SQL injection attack is one of the most commonly used hacking techniques. It allows hacks to access information from a database that is otherwise not publically accessible.

Using sp_executesql stored procedure for executing dynamic SQL queries

December 24, 2019 by Ben Richardson

The sp_executesql stored procedure is used to execute dynamic SQL queries in SQL Server. A dynamic SQL query is a query in string format. There are several scenarios where you have an SQL query in the form of a string.

Understanding the SQL EXCEPT statement with examples

December 23, 2019 by Ben Richardson

The SQL EXCEPT statement is one of the most commonly used statements to filter records when two SELECT statements are being used to select records.

The SQL EXCEPT statement returns those records from the left SELECT query, that are not present in the results returned by the SELECT query on the right side of the EXCEPT statement.

Performing CRUD operations with a Python SQL Library for SQL Server

November 13, 2019 by Ben Richardson

This article covers how to connect a Python application to Microsoft SQL Server using a 3^rd party Python SQL library. The library that we are going to use is called “pyodbc”, which is freely available. We will use “pyodbc” to perform CRUD (Create Read Update and Delete) operations on a Microsoft SQL Server database.

SQL DDL: Getting started with SQL DDL commands in SQL Server

November 8, 2019 by Ben Richardson

This article explains SQL DDL commands in Microsoft SQL Server using a few simple examples.

SQL While loop: Understanding While loops in SQL Server

November 6, 2019 by Ben Richardson

The SQL While loop is used to repeatedly execute a certain piece of SQL script.

SQL JOIN TABLES: Working with Queries in SQL Server

November 6, 2019 by Ben Richardson

In this article, you will see how to use different types of SQL JOIN tables queries to select data from two or more related tables.

Dynamic Data Masking In SQL Server

October 15, 2019 by Ben Richardson

Security has been one of the prime concerns of database developers since the inception of database management systems. Various data protection schemes have been introduced to provide secure access to sensitive data.

Working with XML Data in SQL Server

October 11, 2019 by Ben Richardson

XML (eXtensible Markup Language) is one of the most common formats used to share information between different platforms. Owing to its simplicity and readability, it has become the de-facto standard for data sharing. In addition, XML is easily extendable.

Performance Monitoring via SQL Server Query Store

August 19, 2019 by Ben Richardson

SQL Server Query Store is a performance monitoring tool that helps us evaluate the performance of a SQL query in terms of several different performance metrics such as CPU and Memory Consumption, execution time and the I/O cycles consumed by the query. Query store is similar to the windows “Task Manager”. A task manager provides information about the CPU, Memory, Network and Disc consumption of a process. Similarly, the Query Store provides insight to similar information.

Using Logistic Regression in Azure ML for Predicting Customer’s Loan Eligibility

August 19, 2019 by Ben Richardson

This article aims to provide the basics of creating Logistic Regression in Azure ML by designing a simple model step-by-step.

Understanding Graph Databases in SQL Server

August 16, 2019 by Ben Richardson

A graph database is a type of NoSQL database that is based on graph theory. Graph databases are ideal for storing data that has complex many to many relationships. In this article, we will study the very basics of graph databases with the help of a simple example.

Understanding the SQL Server CASE statement

June 28, 2019 by Ben Richardson

SQL Server CASE statement is equivalent to the IF-THEN statement in Excel.

Nested Triggers in SQL Server

March 27, 2019 by Ben Richardson

Nested Triggers in SQL Server are actions that automatically execute when a certain database operation is performed, for example, INSERT, DROP, UPDATE etc.

Python in SQL Server: The Basics

February 13, 2019 by Ben Richardson

With the introduction of SQL Machine Learning Services, it is now possible to run Python Scripts from any SQL Server client such as SQL Server Management Studio. In addition to directly running the Python Scripts on SQL Server Clients, you can write Python Code on native Python editors and run it remotely on SQL Server using Python clients for SQL Server.

In this article, we will see how to execute some of the basic Python functionalities within SQL Server Management Studio. The article provides an introduction to running basic Python scripts in SQL Server Management Studio.

Importing and Working with CSV Files in SQL Server

January 28, 2019 by Ben Richardson

Introduction

CSV (comma separated values) is one of the most popular formats for datasets used in machine learning and data science. MS Excel can be used for basic manipulation of data in CSV format. We often need to execute complex SQL queries on CSV files, which is not possible with MS Excel. See this article for what is possible with Power BI.

However, before we can execute complex SQL queries on CSV files, we need to convert CSV files to data tables.

Machine Learning Services – Configuring R Services in SQL Server

October 25, 2018 by Ben Richardson

The R language is one of the most popular languages for data science, machine learning services and computational statistics. There are several IDEs that allow seamless R development. Owing to the growing popularity of the R language, R services have been included by Microsoft in SQL Server 2016 onwards. In this article, we will briefly review how we can integrate R with SQL Server 2017. We will see the installation process and will also execute the basic R commands in SQL Server 2017.

Difference between Identity & Sequence in SQL Server

August 15, 2018 by Ben Richardson

In SQL Server, both the SEQUENCE object and IDENTITY property are used to generate a sequence of numeric values in an ascending order. However, there are several differences between the IDENTITY property and SEQUENCE object. In this article, we will look at these differences.