Working with Power BI Drill Through

The Power BI Drill-through feature allows us to dig deeper into the data and get detailed information about a particular data entity.

Getting and Importing the Dataset

This article will explore the Northwind database to create a simple report that includes the Power BI Drill-through functionality. The script and the detailed information on creating the Northwind database on your SQL Server instance can be found in the dedicated branch of GitHub.

CodingSight - Working with Power BI Drill Through
Read More

Reset the Root Password of MySQL on Windows

When we install MySQL on Windows, we usually specify the password of the root user. This helps keep the MySQL Server secured. Suppose you joined a new organization, and the management of that organization plan to migrate some MySQL databases to SQL Server. To perform this, you need the credential of the root user, but the organization did not keep track of the password. In this scenario, we must reset the password of the root user.

In this article, we are going to learn how we can reset the root password in MySQL Server on Windows 10. I have prepared a demo environment on my workstation for demonstration, and the details are explained in the next section.

CodingSight - Reset the Root Password of MySQL on Windows
Read More

Understanding SQL Server Security Function HAS_Permis_BY_Name and Its USE Cases

There are multiple instances when we want to check the permission on a securable for a principal. Before going ahead, let’s see what principal, securables, and permissions are.

According to Microsoft Documentation,

  1. Securables in SQL Server context are specific resources to which the SQL Server Database Engine authorization system controls access. They are divided into three categories: Server, Database and Schema. In general, any SQL Server or database objects can be securables.
  2. Permissions are controls using which we assign grant or deny certain level of access to a securable.
  3. Principal is an entity that receives permission to a securable. The most common principals are logins and database users.
CodingSight - Understanding SQL Server Security Function HAS_Permis_BY_Name and Its USE Cases
Read More

SQL OR Statement: The Basics

Now and then, you need to create a complex SQL query using multiple statements together. While these query types can be tedious to create, they make your data better suitable for reporting purposes. To understand these query types, you must first have some knowledge of logical operators.

A logical operator allows you to combine two or more statements into one. Also, it lets you control the results based on certain conditions. It makes your queries incredibly powerful tools for creating customized reports of your data. This post will focus specifically on one type: the OR Operator.

CodingSigtht - SQL OR Statement: The Basics
Read More

How to Use AI for SQL Tuning for a Real Automated Process

The rapid evolution in development from rigidly structured data to responsive, flexible databases allows database administrators (DBAs) to work closely with developers.

DBAs are often tasked with finding ways to make applications run faster and more efficiently.

Utilizing artificial intelligence (AI) for SQL tuning helps DBAs create a truly automated process that significantly streamlines their daily and long-term tasks. Testing and eliminating bugs can happen automatically so database administrators have time to focus on meeting the needs of clients. Automation also makes releasing and delivering updates much less risky.

CodingSight - How to Use AI for SQL Tuning for a Real Automated Process
Read More

Stored Procedure to Get Database Tables Information

As SQL Server DBAs, we always take care of one of the most important things for the business, the data. In some cases, applications can get quite complex, and you end up with a ton of database tables scattered around your SQL Server instance(s). This might lead to a few inconveniences, such as:

  • Knowing how your data behave every day, in terms of growth trends (space and/or amount of rows).
  • Knowing what database tables require (or will require) a particular/different strategy to store the data because it is growing too fast.
  • Knowing which of your database tables take too much space, possibly leading to storage constraints.
CodingSight - Stored Procedure to Get Database Tables Information
Read More